The Ultimate Guide to Excel Data Cleaning

"My data is a mess." I hear this at least three times a week. And you know what? It usually is. Merged cells, weird formatting, numbers stored as text, dates in every format imaginable—I've seen it all.

But here's the thing: messy data is normal. The difference between someone who struggles with Excel and someone who looks like a wizard is simply knowing where to start.

First Things First: The Coffee Rule

Before you touch anything, make a backup. I call this the Coffee Rule—make a backup, then go get coffee. When you come back, you'll have a fresh perspective and a safety net.

I learned this in 2020 when I overwrote an entire quarter's worth of sales data. My heart still races thinking about it.

The Dirty Dozen: Most Common Excel Problems

1. Numbers That Won't Sum (Text-Formatted Numbers)

You know the drill—you try to sum a column and Excel gives you zero. Those little green triangles in the corners are crying for help.

Quick fix: Multiply by 1. Seriously. Put 1 in a cell, copy it, select your numbers, and paste special → multiply. Works every time.

2. Dates That Excel Hates

I once got a spreadsheet with dates in "Jan 15, 2024", "01/15/24", "15-Jan", and "2024.01.15" all in the same column. Excel was confused. I was confused. Nobody was happy.

The DATEVALUE function is your friend here. It's not perfect, but it handles most formats. For stubborn cases, Text to Columns with the MDY option usually does the trick.

3. The Dreaded Merged Cells

Whoever invented merged cells probably regrets it now. They look nice but break everything—sorting, filtering, pivot tables.

My strategy: Unmerge and fill. Select the merged area, unmerge (Home → Merge & Center → Unmerge Cells), then press Ctrl+G → Special → Blanks, type =↑ (that's equals and the up arrow), and hit Ctrl+Enter. Five seconds, problem solved.

4. Blank Cells That Aren't Really Blank

Sometimes cells look empty but contain spaces, line breaks, or that mysterious Excel character that shall not be named.

The TRIM function is magic. It removes extra spaces, line breaks, and invisible characters. Wrap it around your data and watch the ghosts disappear.

5. Inconsistent Capitalization

"JOHN SMITH", "john smith", "John Smith" in the same column drives me crazy. Use PROPER() for names, UPPER() for codes, LOWER() for emails. Pick one and stick to it.

The 10-Minute Data Cleaning Routine

Here's what I do with every new spreadsheet. It takes 10 minutes and saves hours later:

1. Quick scan (1 min): Scroll through, look for obvious issues. Red flags? Merged cells? Blank columns?
2. Remove formatting (2 min): Select all, clear formats (Home → Clear → Clear Formats). Start fresh.
3. Fix numbers (1 min): Use the multiply trick on any column that should be numbers.
4. Standardize dates (2 min): Get every date in the same format.
5. Trim everything (1 min): Use TRIM on text columns.
6. Check for blanks (1 min): Fill or remove empty cells.
7. Remove duplicates (2 min): Quick pass with Remove Duplicates.

Tools I Actually Use (And You Should Too)

Flash Fill (Ctrl+E)

This is Excel's hidden superpower. Show it what you want once, and it figures out the pattern. I use it constantly—splitting names, formatting phone numbers, extracting email domains.

Go To Special (Ctrl+G)

Want to select all blanks? All formulas? All numbers? This is your tool. I probably use it 20 times a day.

Text to Columns

Besides splitting data, it's my go-to for fixing stubborn formats. The "Finish" button feels so satisfying.

When to Automate

If you're doing the same cleaning more than three times, it's time to automate. Power Query is perfect for this.

Example: I have a client who sends me a weekly report that's always formatted badly. I set up a Power Query once—now I just refresh and it's clean in 5 seconds.

What I Wish Someone Told Me 10 Years Ago

• Clean data first, analyze second. Don't try to do both at once.
• There's almost always a built-in tool for what you're doing manually.
• Google is your friend. If you're doing something that feels tedious, someone has already solved it.
• Your future self will thank you for documenting your cleaning steps.