Cleaning data you can actually trust in Excel

Every real spreadsheet has mistakes in it. Here are the six that quietly wreck your numbers — and the fast fix for each.

Open any real spreadsheet — an export from your CRM, a download from your point-of-sale system, a file a colleague emailed you — and you will find something wrong with it. A stray space. A duplicate row. A date typed three different ways. This is not a sign that someone was careless. It's just what real data looks like.

The danger isn't that messy data crashes your analysis. It rarely does. The danger is that it runs perfectly and hands you a number that's quietly wrong. Clean data is the difference between an answer you can stand behind and one that misleads you without ever throwing an error. So before you calculate anything, you clean — and most of the time cleaning is a few minutes of work against a short, known list of problems.

The problem you can't see is the one that hurts

Here's why cleaning gets skipped: the spreadsheet looks fine. The numbers are there, the columns line up, nothing is flashing red. So people go straight to the SUM and the pivot table.

Then the total is off by a little, or a customer shows up twice in a report, or a filter misses half the rows it should catch — and now you're debugging a result instead of trusting it. Worse, sometimes nobody notices at all, and a decision gets made on a number that was wrong the whole time. That's the real cost. Not a broken formula you'd catch, but a plausible wrong answer you wouldn't.

The fix is to stop treating cleaning as optional cleanup and start treating it as the first real step of the analysis. And the good news is that the mess is predictable. Most of what's wrong with a real spreadsheet falls into six recurring categories.

The six problems, and the fast fix for each

Scan every sheet for these before you analyze. Each one has a quick, specific fix.

1. Duplicate rows. The same record entered twice, inflating every count and total. Fix: Remove Duplicates on the Data tab. Select your range, choose the columns that define a unique record, and Excel drops the repeats.

2. Blank cells. Missing values that break averages and hide records. Fix: sort or filter the column to pull the blanks together so you can see how many there are and decide what to do — fill them, drop them, or flag them.

3. Inconsistent text. "NY" in one row and "New York" in another, or a leading space that makes " David Chan" refuse to match "David Chan." Fix: Find & Replace to standardize the labels, and TRIM to strip extra spaces. TRIM is the quiet hero here — invisible spaces cause more "why won't these match?" mysteries than anything else.

4. Formatting mismatches. Dates entered as 3/15/2025 in one row and March 15 in another, so they won't sort or group. Fix: Text to Columns to reparse them into a single consistent format.

5. Wrong data types. Numbers stored as text, which look normal but return a wrong or zero SUM. Fix: set the column to Number, or use the VALUE function to convert. If a total looks wrong for no reason, this is the first thing to check.

6. Out-of-range values. An age of 350, a negative price, a date in the future. Fix: use filters or conditional formatting to flag impossible values so a human can review them — don't auto-delete, because some outliers are real.

That's the whole checklist. Six problems, six fixes, and you can run all of them in the time it takes to get coffee.

What it looks like on a real sheet

Say you've got a customer export you want to count by name. You notice one row reads " David Chan" with a leading space. Left alone, that record will never match the "David Chan" in your other file — your join silently drops him, or your count treats one person as two.

You select the column and wrap it in TRIM. The space disappears, and now the two records line up exactly. While you're in there, you run Remove Duplicates and it drops a row that had been counted twice. Two small moves, maybe ninety seconds, and a count that would have been wrong is now right. Nobody downstream will ever know how close it came to being off — which is exactly the point.

The mindset that makes this work: scan the whole sheet for anything that "looks weird" before you analyze, not after your numbers come out strange. A few minutes of cleaning up front prevents calculation errors that are much harder to trace once they're buried inside a result.

Where the tool helps

Cleaning is mostly mechanical, which makes it a good fit for an AI assistant. In the course I teach this with AI-3: Data Cleaning Copilot — you describe your messy data, the columns you have and the problems you see, and it hands back a prioritized cleaning plan with the exact Excel technique for each problem. That turns "this sheet is a mess and I don't know where to start" into an ordered checklist.

But notice the seam. The tool can tell you how to fix a wrong data type or a duplicate. It can't decide whether that age of 350 is a typo to fix or a data-entry pattern worth investigating. It can't tell you whether a blank cell means "zero" or "we never asked." Those are judgment calls about what your data means, and they stay with you.

The caveat: cleaning is not scrubbing

There's a line between cleaning data and quietly changing what it says. Removing a true duplicate is cleaning. Deleting rows because they're inconvenient for the story you want to tell is not — that's how analyses become dishonest without anyone lying outright.

So keep two rules. First, when you flag out-of-range values, review them; don't bulk-delete, because a surprising number of "impossible" values are real and interesting. Second, keep a note of what you changed — removed two duplicates, TRIMmed the name column, converted prices from text to number. That record is what lets someone else trust your cleaned data instead of taking it on faith. Clean so the data tells the truth more clearly. Never so it tells a different one.