How to clean up a messy spreadsheet

Invisible trailing spaces are why your lookup fails and your totals are wrong. Cleaning first is worth the two minutes.

Data collected by people is messy in predictable ways. Cleaning it before analysis prevents a category of bug that is genuinely hard to spot afterwards.

Step-by-step

1. Add your file.
2. Choose what to clean.
3. Review the summary of what changed.
4. Download.

The problems worth fixing

Trailing and leading spaces. The big one. "London " and "London" are different values to every piece of software, and identical to every human. This produces duplicate categories, failed lookups and totals that are quietly short.

Blank rows and columns. Break sorting and range detection, and cause charts to include empty categories.

Duplicate rows. Usually from merging exports or a double submission. They inflate every total.

Inconsistent case. "YES", "Yes" and "yes" grouping as three categories.

Review before you accept

Deduplication is the one to check. Two rows can be genuinely identical and both real — two customers with the same name buying the same item on the same day is unusual, not impossible. Read the summary rather than trusting it blindly.

The space that is not a space

Trailing spaces are the famous cause of a lookup that fails against a value you can see is identical. The worse version is the non-breaking space, U+00A0, which arrives whenever anything is copied out of a web page or a formatted document. It looks exactly like a normal space and it is a different character.

What makes it genuinely nasty is that Excel's TRIM does not remove it. TRIM strips the ordinary space, character 32, and leaves U+00A0 in place — so the standard fix appears to run, appears to succeed, and the lookup still fails. The value has to be substituted out explicitly before trimming. If a cell resists every cleaning step you can think of, this is very often what it is.

Numbers that are secretly text

The other silent one is a column of numbers stored as text. They add up to zero, they sort in dictionary order so 10 lands between 1 and 2, and they refuse to match numeric keys in a lookup.

The tell is alignment: spreadsheets right-align numbers and left-align text by default, so a column of figures hugging the left edge has already told you. The causes are usually a CSV imported without typing, a leading apostrophe, or a stray character — a currency symbol, a thousands separator the locale does not expect, a trailing minus sign on an accounting export. Fixing the characters is what converts them back; reformatting the cell as Number does not, because the cell was never the problem.

Frequently asked questions

Why do my lookups fail on values that look identical?

Almost always a trailing space in one of them. It is invisible on screen and makes the two values different to any software.

Is deduplication safe?

Check the summary. Identical rows are sometimes genuinely distinct records, so review what was removed rather than accepting it automatically.

Is my spreadsheet uploaded?

No. It is processed entirely in your browser.