The one idea
Real data arrives broken. Not occasionally — normally. It was typed by tired people into systems that did not stop them, exported by software that mangled the dates, and merged from sources that each spelled things differently.
Cleaning is not the boring bit before the analysis. On most real jobs it is the majority of the work, and everything downstream inherits whatever you failed to fix. A perfect PivotTable on dirty data gives a confident wrong answer, which is worse than no answer, because people act on it.
Before counting anything, check that things which should be the same really are the same.
Profile the data first — unique values, row counts, date ranges, blanks — then fix problems on a copy, and keep a written log of every change.
Validate types, normalise categorical values to a controlled vocabulary, resolve duplicate entities against a key, and distinguish structurally missing values from zero.
The seven things that are wrong with your data
Check for them in this order. Each one hides the next.
1. Trailing and leading spaces
"North " is not "North". This causes more failed lookups than any other
single problem, and it is invisible on screen.
=TRIM(B2)
TRIM removes leading and trailing spaces and collapses runs of internal
spaces to one. It does not remove non-breaking spaces, which is what you get
when data was copied from a web page — for those, =TRIM(SUBSTITUTE(B2, CHAR(160), " ")).
To see whether a cell has a hidden character at all, compare =LEN(B2) with
what you can count on screen.
2. Inconsistent spellings of the same thing
"Mumbai", "mumbai", "MUMBAI", "Bombay", "Mumbai (West)". A human reads one city. Every tool reads five.
Find them by listing what is actually in the column. Select it, insert a
PivotTable, and drag the column into Rows — you now have every distinct value.
Or use =UNIQUE(B2:B900) if your version has it. Reading that list is often the
most informative ten minutes of the whole job.
Fix by mapping, not by hand-editing. Build a two-column lookup of wrong value to correct value on another sheet, then:
=IFERROR(XLOOKUP(TRIM(B2), Map!$A:$A, Map!$B:$B), TRIM(B2))
Anything in the map gets corrected; anything not in it passes through unchanged. The map is your record of what you decided, and it can be reused next month.
3. Case differences
SUMIFS and COUNTIFS ignore case, so "north" and "North" sum together. But
XLOOKUP, exact VLOOKUP and most databases do not. Normalise with =PROPER(B2)
or =UPPER(B2) so both behave the same way.
4. Dates stored as text
A left-aligned date is a warning. So is one that will not sort chronologically, or that a PivotTable refuses to group by month.
The most common cause is a CSV saying 03/04/2026 where your machine's regional
setting read it the other way round — 3 April or 4 March, indistinguishable for
the first twelve days of any month. That is not a formatting problem. It is
wrong data, silently, across the whole quarter.
Fix by selecting the column, then Data, then Text to Columns, then Next, Next,
and choosing Date with the order (DMY or MDY) that matches the source file. Or
=DATEVALUE(B2) when the text is unambiguous. Then check that the minimum and
maximum dates are dates you expect to see.
5. Duplicates
Two kinds, and they need different treatment.
Exact duplicate rows — usually an export that ran twice. Select the data, then Data, then Remove Duplicates. Note the row count before and after, because that difference is a fact you may need to explain.
Duplicate entities that are not identical rows — the same customer entered twice with different spellings, or the same invoice with two different dates. Removing duplicates will not find these. Count occurrences of the key first:
=COUNTIF($A:$A, A2)
Anything above 1 needs a human decision. Do not delete either row until you know which is right.
6. Mixed units and formats
A weight column holding "2 kg", "2000g" and "2". An amount column mixing "₹1,200", "1200" and "1.2K". A phone column with and without country codes.
No formula fixes this. Look at the distribution: if some values are a thousand times larger than others in the same column, units are mixed. Convert deliberately, and put the unit in the column header, never in the cell.
7. Blanks that mean two different things
This is the one people get wrong, and it changes conclusions.
A blank in a "returns" column might mean zero returns — nothing came back. Or it might mean unknown — the shop never reported. They are not the same, and treating unknown as zero pulls your average down and invents a fact.
| If blank means | Then |
|---|---|
| Zero | Fill with 0 and say you did |
| Unknown | Leave blank, and report the count of blanks alongside every figure |
| Not applicable | Exclude those rows from that metric, and say so |
You often cannot tell from the file. Ask whoever produced it. "Does a blank here mean zero or missing?" is a question that makes you look careful, not slow.
An HR file lists 340 employees, and the Department column holds 22 distinct values for what everyone knows are 9 departments. Headcount by department comes out wrong in a way that looks entirely plausible.
The fix that lasts is upstream: a dropdown on the form that captures the data, so the 22 values cannot recur. Cleaning the same file every month without fixing the source is a job you have given yourself for life.
You export a year of bank transactions to categorise your spending. The merchant names are "AMAZON PAY INDIA", "Amazon Pay India Pvt", "AMZN MKTPLACE" and six others. Your food total looks reasonable and your shopping total looks absurdly low.
Nothing is broken. You are counting nine merchants where there is one.
Cleaning an export you have just received
1 of 7Save an untouched copy first, and never open it again. Name it
sales-export-raw-2026-03-14.xlsx. Everything you do next happens on a copy. Without this you cannot prove anything, and you cannot undo a decision you made three hours ago.
Try this
A column of 500 order dates. 480 sort correctly. 20 sit at the top when you sort ascending, regardless of their apparent date.
What is happening, and what do you check first?
Your challenge
Level 3 · IndependentTake a real messy export — a bank statement, an app data download, a class list. Do not use a tidy sample dataset; the mess is the exercise.
Produce a cleaned version plus a cleaning log listing: original row count, final row count, every transformation applied, how many cells each affected, and every judgement call you made where a reasonable person might have chosen differently.
Success test: someone else could take your raw file and your log and reproduce your cleaned file exactly.
What people usually get wrong
- Cleaning in place on the only copy. The first irreversible step. Duplicate the file before anything else.
- Fixing values by hand-editing cells. It is unrepeatable, unauditable, and next month's export will have the same problems.
- Deleting duplicate rows without checking whether they are genuinely duplicates. Two orders on the same day for the same amount can both be real.
- Filling blanks with zero because zero is tidier. You have invented data, and it will move your average.
- Trusting a date column because it looks like dates. Look at the alignment and the minimum and maximum before you trust it.
- Not reporting what you removed. If 40 rows failed to match, that is part of the finding, not an embarrassment to be hidden.
How someone experienced does it
Experienced analysts do not clean data. They build a cleaning pipeline: steps on a separate sheet, or in Power Query, that run again when next month's file arrives. The test is simple — when the export is refreshed, how long until you have the answer? If it is "another day of the same work", you did the job manually and it will need doing forever.
They also treat data quality as a finding, not an obstacle. "22 spellings for 9 departments" is real information about a process that is not working, and often worth more than the analysis it was blocking.
And they know when to stop. The aim is not perfect data; it is data clean enough that further cleaning would not change the decision. If 3 unmatched rows out of 900 cannot flip the conclusion, note them and move on.
When not to use this
Do not clean a dataset you have not yet framed a question for. You will normalise columns nobody needs and leave the one that matters untouched.
And do not clean when the right answer is to fix the source. If the same file arrives broken every month because a form has a free-text field where it should have a dropdown, one conversation ends the problem permanently, and every month you spend cleaning instead delays that conversation.
Prove it
Produce a cleaning log for one real dataset: raw row count, final row count, each transformation with the number of cells affected, each ambiguous blank and how you treated it, and the reconciliation showing your totals tie back to source.
One page. It is the document that turns your number from an assertion into something checkable.
Keep learning this
Paste this into any AI assistant. It turns the assistant into a tutor that tests you instead of just answering you.
Act as an experienced practitioner who is good at teaching. I have just learned cleaning messy spreadsheet data and deciding what to do with blanks. Assume I am intelligent but relatively new to this — treat me as intermediate level. Work through this in order, and wait for my reply at each step: 1. Ask me 5 questions that test whether I actually understood cleaning messy spreadsheet data and deciding what to do with blanks. Do not reveal the answers yet. 2. After I answer, tell me which parts I got right, which I got wrong, and which I only half-understand. Explain only what I misunderstood — do not re-teach what I already know. 3. Give me one practical challenge based on something I could genuinely encounter at work or in daily life. Do not solve it for me. 4. Evaluate my solution the way an experienced person would judge it, including what a professional would have done differently. 5. Tell me what to learn next, and why that comes next. 6. Give me trustworthy sources for deeper study — prefer official documentation, primary research or standards bodies over blogs and videos. Rules for you: no buzzwords. No motivational filler. Say "I'm not certain" when you are not certain, and tell me which parts of your answer I should verify myself. Clearly separate facts from your recommendations and your opinions.
Become independent at this
Use this when you want a path from where you are to actually good, with checkpoints you can test yourself against.
I want to become independently capable at assessing and cleaning a messy dataset before analysis — not permanently dependent on AI, tutorials or step-by-step guides. Design a progression for me with five stages: Beginner, Guided practice, Independent practice, Real-world application, Professional level. For each stage tell me: - what I must know - what I must be able to do without help - the mistakes people make at this stage - one practical challenge - one real project that would prove I reached this stage - one way I can test myself honestly Then tell me the signals that I am ready to move to the next stage, and the signals that I have skipped ahead too early. Keep the theory to the minimum I actually need. Focus on ability I can transfer to situations you and I have not discussed.