Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

When I started learning data analytics, I honestly thought I'd be jumping straight into Python or SQL. I didn't expect Week 1 to be all about Excel. But it made sense pretty quickly, before you can analyze anything, you need to know how to hold your data properly, and Excel is still the tool most of us touch first, whether it's a sales report at work or a list of expenses at home.

This article walks through what I learned in Week 1: the basic building blocks of Excel, and then the more important skill of actually cleaning up messy data so it's ready to be analyzed. To make this real instead of just theory, I built a small sales dataset myself. The kind of messy spreadsheet you'd actually get from someone on a team who was in a hurry and cleaned it step by step. I'll show that process here with the actual tables from my workbook.

Why Excel First?

A lot of people treat Excel as "the basic tool" you graduate from. But in my first week, I learned that most data problems aren't really about which tool you use. They're about understanding your data. Excel forces you to look at every row and column because you can literally see it all on screen. That habit of looking closely at your data carries over into every other tool you'll use later.

Also, a huge number of companies still run their reporting in Excel. Knowing it well isn't a beginner step you skip past, it's a skill you keep using.

The Basic Building Blocks

Before touching any formulas, Week 1 covered the core vocabulary of Excel. It sounds obvious, but getting these terms straight in your head makes everything after this so much easier.

Workbook vs. Worksheet
A workbook is the whole file (the thing you save, like Sales_Practice_Workbook.xlsx). A worksheet (or "sheet") is one tab inside that file. My practice workbook has three sheets: Raw Data, Cleaned Data, and Summary. Keeping raw and cleaned data on separate tabs is a habit I picked up in Week 1, and I'd recommend it to anyone starting out. You never want to clean data in a way that destroys the original.

Rows, Columns, and Cells
Rows run horizontally and are numbered (1, 2, 3...). Columns run vertically and are lettered (A, B, C...). A cell is where a row and column meet, and it has an address like B4. Every single piece of data in Excel lives in a cell. This seems basic, but understanding cell addresses is the whole foundation for formulas later. A formula is really just "do something with the value that's sitting in this address."

Data Types
Excel treats numbers, text, and dates differently, even if they look similar on screen. This tripped me up early on. For example, if a date is typed as text instead of being recognized as a real date, Excel can't sort it properly or use it in date calculations. I ran into exactly this problem in my own dataset, some dates were entered as 01/03/2024 and others as 2024-01-04, and Excel read them inconsistently until I fixed the format. More on that in the cleaning section.

The Ribbon and Basic Navigation
The Ribbon at the top of Excel groups tools into tabs: Home, Insert, Formulas, Data, and so on. In Week 1, the two I used constantly were the Home tab (formatting, fonts, colors) and the Data tab (sorting, filtering, removing duplicates etc.).

Basic Formulas and Functions

Once you're comfortable navigating cells, formulas are the next natural step. Every formula starts with an equals sign (=), which tells Excel "don't just show me text, calculate something."

The functions I used most in Week 1 were simple but genuinely useful:

• =SUM(range) - adds up a group of numbers
• =AVERAGE(range) - finds the mean
• =COUNT(range) - counts how many cells have numbers in them
• =COUNTA(range) - counts how many cells are not empty (numbers or text)
• =IF(condition, value_if_true, value_if_false) - makes a decision based on a condition

In my Summary sheet, I used a couple of slightly more advanced but still Week1 friendly functions: SUMIFS and COUNTIFS, which let you total or count values based on a condition for example, adding up sales only for the "East" region. Here's what that formula actually looks like in the cell:

=SUMIFS('Cleaned Data'!H:H, 'Cleaned Data'!G:G, A4)

In plain language: "add up column H (Total Sale) on the Cleaned Data sheet, but only where column G (Region) matches whatever region is in cell A4." I like this function because it reads almost like a sentence once you break it down.

My Practice Dataset

To make this practical, I built a small sales order dataset. 15 rows, the kind of size you can actually check by eye, which matters when you're learning. It includes an Order ID, Customer Name, Product, Quantity, Unit Price, Order Date, and Region. I deliberately put in the kind of mistakes that show up in actual spreadsheets: extra spaces, inconsistent capitalization, a duplicate row, a couple of blank cells, and dates typed in different formats.

Here's what the raw data looked like:

Order ID	Customer Name	Product	Quantity	Unit Price	Order Date	Region
1001	John Smith	Notebook	5	2.5	01/03/2024	East
1002	" mary jones"	Pen	10	0.75	01/03/2024	west
1003	John Smith	Notebook	5	2.5	01/03/2024	East
1004	ALEX BROWN	Stapler	2	4	2024-01-04	North
1005	Priya Patel	Notebook	(blank)	2.5	01/05/2024	South
1006	Mary Jones	"Pen "	10	0.75	1/5/2024	West
1007	Tom O'Neil	Folder	20	1.2	01/06/2024	east
1008	(blank)	Notebook	3	2.5	01/06/2024	North
1009	Alex Brown	Stapler	2	4	01/07/2024	North
1010	"Priya Patel"	Marker	8	1.5	01/07/2024	South
1011	John Smith	Folder	15	1.2	01/08/2024	East
1012	mary jones	Pen	10	0.75	01/08/2024	West
1013	Tom O'Neil	Notebook	6	2.5	01/09/2024	East
1014	Alex Brown	Marker	4	1.5	2024/01/09	North
1015	Priya Patel	Folder	12	1.2	01/10/2024	South

Quotation marks above show where extra spaces are hiding. You can't always see a trailing space just by looking, but Excel treats "Pen" and "Pen " as two completely different pieces of text. That's exactly the kind of thing that quietly breaks a report.

Just by looking at this small table, I could already spot four different problems. In a spreadsheet of 10,000 rows, none of these would be visible without cleaning it properly first, which is exactly why data cleaning is treated as its own skill, not just a "quick fix before the real work."

Data Cleaning: The Actual Work

This was the part of Week 1 that felt the most like real analytics work, rather than just learning software. Here's what I did, step by step, and why.

1. Trimming extra spaces

The TRIM() function removes extra spaces from text whether as a prefix, suffix, and repeated spaces in the middle. So " Priya Patel" becomes "Priya Patel". This matters more than it sounds like it should, because Excel (and every tool downstream of it) treats text as an exact match. Two entries that look identical to a human but have a hidden space will not be grouped together, counted together, or matched together.

2. Fixing inconsistent capitalization

I had customer names typed in all sorts of ways: ALEX BROWN, Alex Brown, alex brown. The PROPER() function fixes this by capitalizing the first letter of each word. For region names, the same idea applied. west needed to become West so it would group properly with the rest.

3. Finding and removing duplicates

Row 1003 was an exact duplicate of row 1001. Same customer, same product, same date, same everything. Excel's Data tab > Remove Duplicates tool finds and removes these automatically once you tell it which columns to check. I could also have used Conditional Formatting > Highlight Duplicate Values first, just to see the duplicates highlighted before deciding whether to delete them. I'd recommend doing that first, never delete something you haven't actually looked at.

4. Deciding what to do with blank cells

This is the step people rush past, and I think it's the most important one. I had two blanks: a missing Quantity (order 1005) and a missing Customer Name (order 1008). It's tempting to just guess a number or copy a name from somewhere else, but that's not cleaning, that's inventing data. Since I had no reliable way to recover the real values, I documented both rows and excluded them from the cleaned dataset rather than making something up. In a real job, the correct move here is almost always to go back to whoever entered the data and ask, not to fill the gap yourself.

5. Standardizing dates

Excel had stored my dates in at least three different text patterns: 01/03/2024, 2024-01-04, and 2024/01/09. Until these are all recognized as actual dates (not text that merely looks like a date), sorting and filtering by date will give wrong or incomplete results. I fixed this using Data > Text to Columns, which can force Excel to re-read a column and recognize it as a proper date, and then applied one consistent date format (yyyy-mm-dd) across the whole column.

6. Sorting and filtering to double check the work

Once cleaned, I used Data > Filter to turn on the dropdown arrows at the top of each column. This let me quickly check things like "show me only the East region" to eyeball whether the totals looked reasonable. Sorting by Customer Name also made it very easy to see whether any near duplicate spellings had slipped through.

Here's the cleaned version of the dataset, with a Total Sale column added using a real formula (=Quantity * Unit Price, not a typed in number, so it recalculates if the price changes):

Order ID	Customer Name	Product	Quantity	Unit Price	Order Date	Region	Total Sale
1001	John Smith	Notebook	5	$2.50	2024-01-03	East	$12.50
1002	Mary Jones	Pen	10	$0.75	2024-01-03	West	$7.50
1004	Alex Brown	Stapler	2	$4.00	2024-01-04	North	$8.00
1006	Mary Jones	Pen	10	$0.75	2024-01-05	West	$7.50
1007	Tom O'Neil	Folder	20	$1.20	2024-01-06	East	$24.00
1009	Alex Brown	Stapler	2	$4.00	2024-01-07	North	$8.00
1010	Priya Patel	Marker	8	$1.50	2024-01-07	South	$12.00
1011	John Smith	Folder	15	$1.20	2024-01-08	East	$18.00
1012	Mary Jones	Pen	10	$0.75	2024-01-08	West	$7.50
1013	Tom O'Neil	Notebook	6	$2.50	2024-01-09	East	$15.00
1014	Alex Brown	Marker	4	$1.50	2024-01-09	North	$6.00
1015	Priya Patel	Folder	12	$1.20	2024-01-10	South	$14.40

Notice this table has 12 rows instead of the original 15. One duplicate removed, and two rows with unrecoverable blanks excluded. That drop from 15 to 12 rows is worth stating clearly rather than hiding, because in analytics, being upfront about what you removed (and why) is part of doing the work honestly.

Turning Clean Data into a Quick Summary

Once the data was clean, pulling a summary took only a few formulas. Using SUMIFS and COUNTIFS grouped by region, I got:

Region	Total Sales	Orders	Avg Order Value
East	$69.50	4	$17.38
West	$22.50	3	$7.50
North	$22.00	3	$7.33
South	$26.40	2	$13.20
Grand Total	$140.40

What I liked about this step is that it made the earlier cleaning feel worth it. Before cleaning, the "West" and "west" region values would have been split into two separate groups by SUMIFS, quietly under reporting West's totals. Clean data isn't just neater to look at, it directly changes whether your numbers are actually correct.

What I'd Tell Someone Just Starting Out

A few things stuck with me after this first week:

1. Learn the vocabulary first. Knowing exactly what a cell, row, column, and worksheet are makes every instruction you read afterward make sense instantly.
2. Never edit your raw data directly. Keep a separate tab (or a copy of the file) so you can always go back to what you started with.
3. Don't guess to fill gaps. A blank cell is information too. It tells you something is missing. Filling it with a guess hides that.
4. Small mistakes compound. A trailing space or a mismatched date format looks tiny, but it can silently throw off every total built on top of it.
5. Formulas over typed numbers. Writing =D2*E2 instead of typing 12.50 means your sheet updates itself instead of going stale.

Excel isn't flashy, and Week 1 definitely felt slower than I expected. But the habits it built, like checking your data before trusting it, documenting what you changed, and never inventing values you don't actually have, are the same habits that matter no matter which tool you move on to next.

What was your first experience with Excel?