DEV Community

Mariam Turnesh
Mariam Turnesh

Posted on

# Introduction to MS Excel for Data Analytics 📊

#analytics #beginners #datascience #microsoft

Excel for Absolute Beginners: Your Interactive Journey 🚀

"I've never touched Excel. Where do I even start?"

Right here. Right now. This guide has zero assumptions about what you know. We're building from the ground up, one tiny step at a time.

🎯 How This Guide Works

Each section has:

  • 📖 A quick explanation
  • 🎮 A hands-on exercise you can do RIGHT NOW
  • ✨ Why it matters (so you're not just blindly following steps)

Ground Rule: Open Excel (or Google Sheets) and follow along. Reading won't teach you. Doing will.

Level 1: Your First 60 Seconds in Excel

📖 What You Need to Know

When you open Excel, you see a grid. That's it. Each box is called a cell. You click it, you type in it. That's Excel.

  • Columns = vertical (A, B, C, D...)
  • Rows = horizontal (1, 2, 3, 4...)
  • Cell names = Column + Row (A1 means column A, row 1)

🎮 Exercise 1: Create Your First Table

Do this:

  1. Open Excel
  2. Click cell A1
  3. Type: Item
  4. Press Tab (moves you to B1)
  5. Type: Price
  6. Press Enter (moves you down)
  7. Type: Coffee
  8. Press Tab
  9. Type: 5
  10. Keep going - add 2 more items and prices

Your result should look like:

Item       Price
Coffee     5
Sandwich   8
Water      2
Enter fullscreen mode Exit fullscreen mode

🤔 Why this matters

You just created a database. Seriously. Every Excel file is just organized boxes of information. Master this, and you've mastered the foundation.

Level 2: Make Excel Do Math For You

📖 What You Need to Know

Excel can calculate anything. The secret? Start with an equals sign =

When you type =5+3, Excel doesn't show =5+3. It shows 8. That's the magic.

🎮 Exercise 2: Your First Formula

Use the table you just made. Now:

  1. Click cell C1
  2. Type: Quantity
  3. Press Enter
  4. In C2, type: 2
  5. In C3, type: 1
  6. In C4, type: 3

Now for the magic part:

  1. Click cell D1, type: Total
  2. Click cell D2
  3. Type: =B2*C2 (don't type the words, type the actual formula)
  4. Press Enter

What happened? Excel multiplied Price × Quantity for you. You should see 10 (because 5 × 2 = 10).

🤔 Why this matters

You just told Excel: "Take what's in B2, multiply it by what's in C2, put the answer here." Now if you change the price or quantity, the total updates automatically. That's why Excel exists.

🎮 Exercise 3: The Copy-Down Trick That Changes Everything

Here's where it gets wild:

  1. Click cell D2 (where your formula is)
  2. Look at the bottom-right corner of the cell - see that tiny square?
  3. Click that square and drag down to D4
  4. Let go

BOOM. Excel copied your formula AND adjusted it automatically. D3 now calculates B3×C3, and D4 calculates B4×C4.

🤔 Why this matters

You just saved yourself from typing the same formula 100 times. This one trick is why people love Excel. Imagine you had 1,000 rows - same process, 2 seconds.

🎉 Achievement Unlocked: You can now do math faster than a calculator.

Level 3: Functions = Excel's Built-In Superpowers

📖 What You Need to Know

Functions are pre-made formulas. Instead of typing =D2+D3+D4, you can type =SUM(D2:D4). Easier, right?

🎮 Exercise 4: Add Up Everything Instantly

Using your table:

  1. Click cell D5
  2. Type: =SUM(D2:D4)
  3. Press Enter

You just added all your totals. In one formula.

Now try these in the cells below:

=AVERAGE(D2:D4)  ← Your average total
=MAX(D2:D4)      ← Your highest total
=MIN(D2:D4)      ← Your lowest total
=COUNT(D2:D4)    ← How many numbers you have
Enter fullscreen mode Exit fullscreen mode

🤔 Why this matters

These 5 functions (SUM, AVERAGE, MAX, MIN, COUNT) solve 80% of real-world Excel problems. Seriously. Learn these, and you're already dangerous.

💡 Quick Reference Card

Copy this somewhere:

What You Want Function to Use Example
Add everything =SUM(A1:A10) Total sales
Find the average =AVERAGE(A1:A10) Average score
Find the biggest =MAX(A1:A10) Highest price
Find the smallest =MIN(A1:A10) Lowest score
Count how many =COUNT(A1:A10) Number of sales

Level 4: Sort Your Data Like a Pro

📖 What You Need to Know

Real data is messy. You need to organize it. Excel makes this stupid easy.

🎮 Exercise 5: Sort from Biggest to Smallest

Using your table:

  1. Click anywhere in your data (any cell with stuff in it)
  2. Go to the Data tab at the top
  3. Click Sort
  4. A window appears - under "Sort by", choose Total
  5. Choose Largest to Smallest
  6. Click OK

Your items are now sorted by total - highest at the top.

🤔 Why this matters

This is how you find what matters. "What's our best-selling product?" Sort by sales. "Who spent the most?" Sort by total. This is how managers "analyze data."

Level 5: Filter to See Only What You Want

📖 What You Need to Know

Sometimes you don't want to see ALL the data. Just the laptops. Just sales over $100. Just entries from January. Filters let you do this in one click.

🎮 Exercise 6: Turn On Filters

  1. Click any cell in your data
  2. Go to Data tab
  3. Click Filter

See those little dropdown arrows that appeared in your headers? That's the magic button.

Now click the arrow in the "Item" column:

  • You'll see checkboxes for each item
  • Uncheck "Select All"
  • Check only "Coffee"
  • Click OK

Everything else disappears. (Don't panic - it's not deleted. Click the arrow again and check "Select All" to bring it back.)

🤔 Why this matters

This is how you explore large datasets. Got 10,000 rows? Filter to see just what you need. This is literally what "drilling down into the data" means.

Level 6: Make a Chart in 30 Seconds

📖 What You Need to Know

Nobody reads tables. Everyone looks at pictures. Charts turn numbers into visuals instantly.

🎮 Exercise 7: Your First Chart

  1. Select your Item and Total columns (click A1, drag to highlight A1 through D4)
  2. Go to the Insert tab
  3. Click Column Chart (the one that looks like bars)
  4. Pick the first option

BAM. You just made a chart.

Want to make it better?

  • Click on it
  • Click Chart Title and rename it to something like "My Spending"
  • Done

🤔 Why this matters

Charts are how you present data. Your boss doesn't want to read 50 rows. They want to see a picture that tells them what's happening. You now know how to give them that.

📊 Chart Cheat Sheet

Use This Chart When You Want To Show
Bar/Column Chart Compare different things (which is bigger?)
Line Chart Show change over time (going up or down?)
Pie Chart Show parts of a whole (percentages)

Pro tip: These three charts cover 95% of situations. Don't overthink it.

Level 7: Clean Up Messy Data

📖 What You Need to Know

Real data has problems:

  • Extra spaces: " Coffee " instead of "Coffee"
  • Random capitalization: "COFFEE" vs "coffee"
  • Duplicates

Excel can fix all of this.

🎮 Exercise 8: Remove Extra Spaces

Try this:

  1. In a new cell, type: " Coffee " (with spaces before and after)
  2. In the cell next to it, type: =TRIM(A1) (replace A1 with your cell)
  3. Press Enter

The spaces are gone. Clean data.

🎮 Exercise 9: Remove Duplicates

If you have duplicate rows:

  1. Select all your data
  2. Data tab → Remove Duplicates
  3. Click OK

Excel removes all duplicate rows automatically.

🤔 Why this matters

Messy data = wrong results. Before you analyze anything, clean it first. These two tools save hours of manual cleanup.

Level 8: Your First Dashboard (Impress Everyone)

📖 What You Need to Know

A dashboard is just:

  1. Your most important numbers at the top (big and bold)
  2. A chart or two below
  3. Everything fits on one screen

That's it. You don't need fancy.

🎮 Exercise 10: Build a Simple Dashboard

Using everything you've learned:

  1. Create a new area in your sheet (maybe start at row 10)
  2. Type these labels in column A:
    • Total Spent:
    • Average Purchase:
    • Highest Purchase:
  3. In column B, add the formulas:
    • =SUM(D2:D4)
    • =AVERAGE(D2:D4)
    • =MAX(D2:D4)
  4. Make these numbers BIG (select them, increase font size to 16-20)
  5. Make them BOLD
  6. Copy your chart from earlier and paste it below these numbers

You just built a dashboard.

🤔 Why this matters

This is what people mean by "business intelligence." You took raw data, calculated insights, and presented it clearly. This is literally the job description for "Data Analyst."

🆘 When Excel Yells at You (Error Guide)

You'll see these. Here's what they mean:

Error Translation Fix
#DIV/0! "You divided by zero" Check for empty cells
#VALUE! "That's not a number" Make sure numbers are actually numbers
#REF! "You deleted something I needed" Press Ctrl+Z (undo)
#NAME? "I don't know that formula" Check your spelling

Pro tip: When in doubt, press Ctrl+Z (undo) and try again.

🎮 Your Challenge: Make It Personal

Forget boring practice data. Track something YOU care about:

Ideas:

  • Your daily coffee spending for a week
  • Hours you slept each night
  • Minutes on social media per day
  • Money spent on food delivery
  • Books/articles you read
  • Workouts completed

Then:

  1. ✅ Put it in Excel (3 columns max)
  2. ✅ Calculate total and average
  3. ✅ Make ONE chart
  4. ✅ See what you learn about yourself

Post your results in the comments! What did you track? What did you discover?

💡 Keyboard Shortcuts You'll Actually Use

Start learning these NOW:

Shortcut What It Does
Ctrl + S SAVE (use this every 2 minutes)
Ctrl + Z Undo (your best friend)
Ctrl + C Copy
Ctrl + V Paste
Ctrl + X Cut
Alt + = Quick SUM (try it!)
Ctrl + T Turn your data into a fancy table

🎓 What You Just Learned

In 15 minutes, you went from zero to:

✅ Understanding cells, rows, and columns

✅ Writing formulas that calculate automatically

✅ Using functions (SUM, AVERAGE, MAX, MIN, COUNT)

✅ Sorting and filtering data

✅ Cleaning messy data

✅ Creating charts that look professional

✅ Building an actual dashboard

That's more than most people know after years of "using Excel."

🚀 What's Next?

Level Up: Start using Excel for real things:

  • Track your personal expenses
  • Plan a project timeline
  • Analyze your work data
  • Make a budget
  • Track your side hustle revenue

The best way to learn Excel? Use it for something that matters to you.

💬 Join the Conversation

Drop a comment and tell me:

  1. What's the first thing you're going to analyze in Excel?
  2. Which exercise was your "aha!" moment?
  3. What Excel topic should I cover next?

If this helped you: Bookmark it, share it with someone who's intimidated by Excel, or drop a ❤️ to let me know this was useful.

Now stop reading and go make a spreadsheet. Break stuff. Press Ctrl+Z. That's how everyone learned.

P.S. - Still haven't opened Excel? What are you waiting for? The best time to start was 10 minutes ago. The second best time is right now. Go! 🚀

Tags: #excel #beginners #tutorial #dataanalysis #productivity #learntocode #career

Top comments (0)