MS Excel for Data Analytics - A Beginner's Guide

Introduction

If you're stepping into data analytics, Excel is your best friend. It's the most accessible, powerful tool for working with data - no coding required. Whether you're tracking sales, analyzing trends, or building reports, Excel has you covered.

This guide uses real examples (yes, with Pokemon data 🎮) to make learning fun and relatable. By the end, you'll know how to clean data, write formulas, create pivot tables, and extract insights like a pro.

Let's dive in!

---

Part 1: Understanding the Excel Interface

Before we jump into formulas and functions, let's get comfortable with Excel's layout.

Key components you need to know:

• Ribbon: The toolbar at the top with tabs (Home, Insert, Formulas, Data, etc.) - this is where all Excel's features live
• Formula Bar: Shows the contents of the active cell - especially useful when viewing formulas
• Column Headers: The letters (A, B, C...) that identify columns
• Row Headers: The numbers (1, 2, 3...) that identify rows
• Active Cell: The currently selected cell with a green border (like A1 in the image)
• Worksheet Tabs: Switch between different sheets in your workbook (Sheet1, Sheet2, etc.)

Pro tip: When someone says "cell D5", they mean the intersection of column D and row 5. This naming system is how we reference data in formulas.

---

PART 2: Data Cleaning & Sorting/Filtering

How to Find Duplicates (Quick Check)

You might want to see duplicates before deleting them to be sure what you’re removing.

1. Highlight Duplicates Using Conditional Formatting

Select the cells you want to check.

Click Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

Pick a color style and click OK.

Now all duplicates will be colored so you can see them easily.
Duplicate data messes up analysis. Here's how to clean it:

✅ This doesn’t delete anything — it just shows duplicates so you can inspect them.

📸 Screenshot to include:

Find & Replace

Basic Find:

• Ctrl+F: Search for text
• Type what you're looking for
• Click Find Next or Find All

Find and Replace:

• Ctrl+H: Opens Find & Replace
• Find what: Old text
• Replace with: New text
• Click Replace All

Example: Replace all "Water" with "Aqua" in one click

Pro Tip: Use Match case option for case-sensitive searches

Text to Columns

When data is cramped in one column, split it!

Example: "Pikachu,Electric,35" should be 3 columns

1. Select the column
2. Go to Data → Text to Columns
3. Choose Delimited (split by character) or Fixed Width
4. Choose delimiter (comma, space, tab)
5. Click Finish

Real-world: Split "Last, First" names, separate addresses

Error Checking

Excel shows error codes when formulas break:

• #DIV/0!: Dividing by zero
• #VALUE!: Wrong data type (text in a math formula)
• #REF!: Reference to deleted cells
• #NAME?: Excel doesn't recognize the function name (typo?)
• #N/A: Value not found (common with lookups)

ISERROR() and IFERROR()

=IFERROR(A1/B1, "Error!")

• If the formula works, show the result
• If it errors, show "Error!" instead
• Prevents ugly error codes in your reports

Sorting Data

Quick Sort (One Column):

1. Click any cell in the column
2. Data tab → Sort A to Z (ascending) or Sort Z to A (descending)

Custom Sort (Multiple Columns):

1. Select your data (including headers!)
2. Data → Sort
3. Choose primary sort column (e.g., "Type")
4. Click Add Level for secondary sort (e.g., "Total Stats")
5. Click OK

Warning: Always select the entire data range before sorting! Sorting one column alone separates related data.

Filtering Data

Enable Filters:

1. Click any cell in your data
2. Data → Filter (or Ctrl+Shift+L)
3. Dropdown arrows appear in header row

Using Filters:

• Click dropdown arrow
• Uncheck items to hide them
• Text Filters: Contains, Equals, Begins With
• Number Filters: Greater Than, Less Than, Between, Top 10

Example: Show only Water-type Pokemon with stats > 400

1. Filter "Type" = "Water"
2. Filter "Total Stats" > 400
3. Result: Only strong Water types visible

Clear Filters: Data → Clear (removes all filters)

Freeze Panes

Keep headers visible while scrolling:

1. Click the cell below and to the right of what you want frozen
2. View → Freeze Panes → Freeze Panes

Freeze top row only: View → Freeze Panes → Freeze Top Row

Unfreeze: View → Freeze Panes → Unfreeze Panes

Hide/Unhide Rows and Columns

Hide:

• Select rows/columns
• Right-click → Hide

Unhide:

• Select rows/columns on either side
• Right-click → Unhide

Pro Tip: Hidden data still exists and is included in calculations!

Part 3: Essential Excel Functions

Functions are pre-built formulas that perform calculations for you. Let's look at the most important ones you'll use every day.

The SUM Function

Need to add up numbers quickly? SUM is your go-to function.

What's happening here:

• Formula: =SUM(D2:D10)
• This adds all values from cell D2 through D10
• Result: 2489 (total stats for all Pokemon)

How to use it:

1. Click the cell where you want the result
2. Type =SUM(
3. Select your range of cells (or type it like D2:D10)
4. Close with )
5. Press Enter

Real-world use: Totaling sales, expenses, inventory quantities, hours worked, etc.

---

The AVERAGE Function

Want to find the mean of a group of numbers? AVERAGE does the math for you.

What's happening here:

• Formula: =AVERAGE(B2:E2)
• This calculates the average of values across columns B through E in row 2
• Result: 4 (average number of pokeballs for trainer Iva)

Why it matters: Averages help you understand typical values in your data - average sales per month, average customer rating, average response time, etc.

---

The MAX and MIN Functions

These functions find the highest and lowest values in a range.

What's happening:

• Formula: =MAX(D2:D21)
• Finds the highest value in the Total stats column
• Result: 525 (Cloyster has the highest stats)

What's happening:

• Formula: =MIN(D2:D21)
• Finds the lowest value in the Total stats column
• Result: 200 (Magikarp has the lowest stats)

Real-world applications:

• MAX: Find your best-selling product, highest revenue month, top performer
• MIN: Identify lowest inventory levels, minimum order quantities, slowest sales day

---

The MEDIAN Function

While AVERAGE can be skewed by extreme values, MEDIAN finds the true middle value.

What's happening:

• Formula: =MEDIAN(A2:G2)
• Finds the middle value when all numbers are sorted
• Result: 2

When to use MEDIAN vs AVERAGE: If you have outliers (extremely high or low values), MEDIAN gives you a better sense of the "typical" value. For example, median income is often more useful than average income because a few billionaires don't skew it.

---

Part 4: Pivot Tables - Your Data Analysis Superpower

Pivot tables let you summarize thousands of rows of data in seconds. They're intimidating at first, but incredibly powerful once you get the hang of them.

Creating a Pivot Table

Steps to create a pivot table:

1. Click anywhere in your data t

2. Go to Insert > PivotTable

3. Excel automatically selects your data range (Table1 in this example)

4. Choose where to place it (New Worksheet or Existing Worksheet)

5. Click OK

Working with Slicers

What are slicers?

Slicers are visual filters that make it easy to filter pivot table data with a click. In this example:

• Filter by Region (Aberdeen, Aberdeenshire, Anglesey, etc.)
• Filter by Sub-Category (Accessories, Appliances, Art, Binders, etc.)

The pivot table shows Sum of Sales by region, totaling over 16,000 for Aberdeen alone.

Real-world scenario:

Imagine you're analyzing sales data across 50 stores and 100 products. Instead of manually filtering, you can:

• Click "North Region" slicer → instantly see only North region sales
• Click "Electronics" category → see only electronics sales
• Combine filters → see North region electronics sales

How to add slicers:

1. Click your pivot table
2. Go to PivotTable Analyze > Insert Slicer
3. Select the fields you want to filter by
4. Click OK

---

Keyboard Shortcuts That Save Time

• Ctrl + C / Ctrl + V: Copy and paste
• Ctrl + Z: Undo your last action
• Ctrl + Arrow Key: Jump to the edge of your data
• Ctrl + Shift + L: Add/remove filters
• Alt + =: Auto-sum selected cells
• F2: Edit the active cell

Common Beginner Mistakes (and How to Avoid Them)

❌ Mistake: Typing numbers directly into formulas

✅ Better: Use cell references so formulas update when data changes

❌ Mistake: Not freezing header rows when scrolling

✅ Better: Use View > Freeze Panes > Freeze Top Row

❌ Mistake: Deleting rows/columns with hidden formulas

✅ Better: Use Ctrl + ` to show all formulas before deleting

---

Final Thoughts

The best way to master Excel is to practice with real data. Start small, experiment, and don't fear mistakes - that's how you learn!
Remember: Every data analyst started exactly where you are now. The difference? They kept practicing.
What will you analyze first?
Drop a comment below and let me know! 👇