The idea
Excel compares and adds what is stored in a cell, not what you read. Two emails that look identical to you are different strings if one starts with a space. A refund that looks like 19.99 is not a number if the export wrote it as text. A city in capitals is not equal to the same city in title case.
Cleaning is the work of making the stored value match the meaning, and doing it in a way that someone else can check. The rest is technique.
Figure 1
Two accounts for one person: the raw email hides it, the clean key shows it
| customer_id | first_name | clean_key | raw_count | key_count | |
|---|---|---|---|---|---|
| 1 | Owen | owen.hudson@icloud.com | owen.hudson@icloud.com | 1 | 2 |
| 2 | Douglas | dmendoza69@gmail.com | dmendoza69@gmail.com | 1 | 1 |
| 2003 | Owen | owen.hudson@icloud.com | owen.hudson@icloud.com | 1 | 2 |
- Customers 1 and 2003 are both Owen Hudson. Count the raw emails with
COUNTIFand each appears once, so neither row is flagged. - Look closely at row 2003: its email starts with a space. To Excel,
" owen.hudson@icloud.com"and"owen.hudson@icloud.com"are different strings, so nothing matches. - Build a clean key in a new column:
=LOWER(TRIM(D2)).TRIMremoves the leading and trailing spaces,LOWERremoves case differences. Both rows now hold the same text. COUNTIFover the clean key returns 2 for both rows. Douglas is 1. Count the rows where the result is above 1 and you have the duplicate accounts.
Why it exists
Every system that produces an export makes its own choices. A signup form lets people type spaces. A payments portal writes amounts as text so that currency symbols survive. Three systems write the same date three ways. None of this raises an alarm. It just makes the numbers wrong, in quiet ways: a customer counted twice, revenue short by one refund, a month with no orders because its dates are text.
Cleaning is cheaper than debugging the report afterwards. It is also the part of the job where trust is built: when a manager asks "why is this customer count lower than last month's?", the answer is a list of what you changed and why.
How it works under the hood
Match on a clean key, not on the raw value
To find duplicates, you need equality that ignores noise. TRIM removes leading and trailing spaces and collapses double spaces inside; LOWER, UPPER and PROPER make the case consistent. Put the cleaned value in a helper column and run COUNTIF(key_range, key_cell): a count above 1 means the key appears more than once. Lock the range with $ so it does not slide as you fill down.
EXACT(a, b) compares two cells and respects case and spaces. It is how you prove a cleaning step changed something: =NOT(EXACT(raw, clean)) is TRUE for every row you altered, and counting those gives the "problems found" figure for your note.
Numbers and dates stored as text
A text number looks right and behaves wrong: it sorts alphabetically, SUM skips it, and comparisons such as >=250 fail. Convert with VALUE(text). Text dates need DATEVALUE, or, when the pieces are in an awkward order, rebuild them with DATE(year, month, day) using LEFT, MID and RIGHT to pull the parts out. A quick test is ISNUMBER(cell): a FALSE on a cell you expected to be a number is the tell.
Figure 2
Three refunds, one stored as text: SUM skips it
| return_id | refund_amount | stored_as | counted_by_SUM |
|---|---|---|---|
| 1 | 83.69 | number | 83.69 |
| 2 | 19.99 | text | 0 |
| 3 | 10.99 | number | 10.99 |
| formula | returns |
|---|---|
| =SUM(B2:B4) | 94.68 |
| =SUM(VALUE(B2),VALUE(B3),VALUE(B4)) | 114.67 |
- Harbor & Pine returns 1 to 3. An export wrote the refund of return 2 as text, so it sits in the cell looking like 19.99.
SUMadds numbers and skips text. The total reads 94.68, short by 19.99, and Excel shows no error. A text number is usually aligned to the left of its cell, and Excel often adds a small green triangle.VALUEturns text that looks like a number into a number. After converting, every refund counts and the total is 114.67.
Missing values and outliers
A blank is information, and filling it is a decision. COUNTBLANK tells you how many there are. If you fill a missing product cost with the MEDIAN of the other products, write that rule down next to the column, because anyone reading the margin figures needs to know that some costs are estimates. Never fill a gap silently.
Outliers are values far from the rest, such as an order line with a quantity of 100000 when every other line is between 1 and 6. Excel has no outlier button. The usual rule fences off values beyond 1.5 times the interquartile range from the quartiles: compute QUARTILE.INC(range,1) and QUARTILE.INC(range,3), take the difference, and flag anything outside the fence. The flag is a question for a human, not an instruction to delete: a huge quantity may be a typing slip, or a real bulk order.
Messy casing and padding in labels
Cities and states are the usual place where the same thing is written five ways: dallas, Dallas, DALLAS . Grouping or counting by them then splits one city into several rows, which is how a "top cities" table ends up wrong without looking wrong. Normalise labels with PROPER(TRIM(x)) for names and UPPER(TRIM(x)) for codes such as state abbreviations. Then count the distinct values again: the number should drop to what you expect, and if it does not, a value is hiding another kind of noise such as a typo.
Prove each fix on a sample
After you build a cleaning column, pick five rows from different parts of the sheet, including the one with the worst mess, and check them against the raw values by eye. Then count how many rows changed. A cleaning step that changes every row, or none, is rarely right.
The rule: never overwrite the raw data
Clean into new columns beside the originals, and give each a name that says what it is (clean_email, refund_number). Keep the raw column, add a column for what changed and why, and hand back the cleaned copy with the raw one still in the workbook. Overwriting in place destroys the evidence and makes any mistake unrecoverable.
Common misconceptions
"If it looks the same, it is the same"
Two cells can show the same text and differ by an invisible space, a different case or a non-breaking space copied from a web page. LEN is the quickest detector: if two cells that look equal have different lengths, something is hiding in one of them.
"Duplicate means a completely identical row"
The duplicates that cost money are near-duplicates: the same person with a different case, a stray space, or a typo in one field. Decide which field defines identity (the email, here), clean that field, and compare on the clean version.
"Cleaning means deleting the bad rows"
Deleting changes totals and cannot be undone. Flag first, fix or exclude with a stated reason, and keep the original. The reader of your report will trust it more when it shows what you did, not just the final numbers.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. Deleting loses the original and the proof. The two accounts may have different orders; decide after flagging, with a reason recorded.Try another answer.
Right. A clean key column makes the match exact and visible, and the raw email stays for anyone who wants to check your work.
Not quite. A wildcard widens the match but also finds rows you did not mean, and it does not fix the comparison for the rows that follow.Try another answer.
Not quite. Retyping fixes one cell by hand and leaves no trace. The next export will carry the same problem.Try another answer.
Lesson done: it is ticked on the map.