DEV Community

Mariam Turnesh
Mariam Turnesh

Posted on

MS Excel for Data Analytics - A Beginner's Guide

#datascience #beginners #microsoft #analytics

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: 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! 👇

Top comments (0)