How to clean a spreadsheet and compare two exports

Two jobs come up again and again with spreadsheets: tidying a messy export so it can be used, and working out what changed between last month's file and this month's. Both are done badly by eye and badly by hastily written formulas. Here is how to do each properly — and, just as importantly, which shortcuts quietly corrupt your data.

The five ways a spreadsheet quietly loses your data

Before any cleaning, it is worth knowing what goes wrong, because most of it happens silently — the file opens, the numbers look fine, and something is already wrong.

• Leading zeros disappear. An account number written 00123 is read as the number 123. Once that has happened and the file has been saved, the original digits are gone.
• Long numbers are rounded. Any whole number past about sixteen digits cannot be stored exactly as a number. A long invoice or card reference silently becomes a slightly different number, usually ending in 0.
• Dates are guessed. 03/04/2026 is the third of April in most of the world and the fourth of March in the United States. Nothing in the file says which. A tool that picks one for you is guessing with your records.
• Numbers appear where there were none. Text that begins with digits — 12 Main Street, 3 boxes — can be coerced into a number by a careless converter.
• Pennies appear and disappear. Comparing money with ordinary computer arithmetic produces differences of a hundredth of a penny, which then round into real ones. Money has to be compared in decimal.

Each of these is why the Spreadsheet Cleanup & Reconciliation tool treats every value as text until you explicitly ask for it to be converted, and asks which way round your dates are written rather than choosing.

Part one: cleaning a file

Step 1: check how the file was read before you change anything

Most cleaning mistakes happen at the import, not during the work. Look at the preview and check three things:

• Is the header on the right row? Exports often have a title line or two above the real headings.
• Are the columns split correctly? If a file was exported in a country that uses the comma as a decimal mark, it is probably separated by semicolons.
• Is the text right? If names show black diamonds or odd symbols, the file is not UTF-8 — try Windows-1252, which is what older exports usually are.

Two headings with the same name, and headings that are blank, are normal in exports. They should be kept apart and labelled, never merged: if two columns are both called Amount, collapsing them loses one of them completely.

Step 2: tidy the text

Trailing spaces are the single most common reason two apparently identical values refuse to match. Trim first, and look at what the trim reports: if it changed hundreds of values, that tells you something about how the file was produced.

Collapsing repeated spaces and converting non-breaking spaces are separate decisions. A non-breaking space is invisible and extremely common in data copied from web pages or PDFs, and it will not match an ordinary space.

Step 3: deal with duplicates deliberately

Decide what makes two rows the same before you remove anything. Duplicates by whole row are safe to remove. Duplicates by a key column — two rows with the same invoice number — are usually a sign of a real problem, and deleting one silently hides it. Marking them and looking at them first is nearly always the right move.

Whatever you remove, keep it. A cleaning step that discards rows with no way to see them again is a step you cannot check.

Step 4: convert dates and numbers last, and only where you mean it

Conversion is the step that cannot be reversed by looking at the result, so leave it until the shape of the data is right. Convert a column only when you know what is in it: a column of dates in one format, a column of amounts in one convention. Anything that fails to convert should be left exactly as it was and listed, not blanked — a value the tool could not read is information, and replacing it with nothing destroys that.

Part two: comparing two exports

Step 1: choose a key, and check it

A comparison is only as good as the column that identifies a row. An invoice number, an SKU, a member ID, an email address. Before comparing, check two things about it:

• Is it unique? If the same key appears twice on one side, there is no single correct pairing. A tool that matches them anyway is inventing an answer — and worse, if both sides have it twice, a naive join produces four rows out of four, silently doubling your data.
• Is it always filled in? Rows with a blank key cannot be matched to anything. They should be set aside for you to look at, not matched to each other because they are both blank.

Step 2: decide what counts as the same

By default, compare keys exactly: ABC  and abc are different. Ignoring case or trailing spaces is often the right thing, but it must be a decision you made and one that appears in the report, because it changes which rows are considered to be the same record.

Step 3: set a tolerance where a tolerance is honest

Amounts that came through different systems can differ by a penny from rounding. Allowing a difference of 0.01 is reasonable; allowing 1.00 is hiding real differences. Whatever you allow, the actual values should still be shown, so a "within tolerance" row can be checked rather than trusted.

Step 4: read the six groups

Every row on both sides should land in exactly one of these, with nothing left over:

• Unchanged — matched, and the compared fields are the same.
• Changed — matched, and at least one compared field differs.
• Only on the left — in the earlier file and not the later one: removed, or renumbered.
• Only on the right — new since the earlier file.
• Duplicate key — the key appears more than once, so the pairing is ambiguous.
• No key — the key is blank, so the row cannot be matched at all.

The check that matters is arithmetic: the groups should add up to the number of rows you started with on each side. If they do not, rows have been duplicated or dropped somewhere in the comparison.

Step 5: mind the difference between blank, zero and absent

These three are not the same thing and a comparison should never treat them as one. A field that was empty and now holds 0 has changed — someone entered a figure. A row that is absent from the later file is not a row whose values became blank. Reports that flatten all three into "empty" are the reason reconciliations get signed off with real changes hidden inside them.

Before you send the results to anyone

• Reopen the file you exported. Check the row count, check an ID that starts with a zero, and check one amount. It takes a minute and catches almost everything.
• Watch for the apostrophe. A CSV value that begins with =, + or @ will be run as a formula when the file is opened in a spreadsheet, which is both a wrong value and a genuine security problem. Writing it with a leading apostrophe stops that; real negative numbers such as -5.00 must not be given one, or they stop being numbers.
• Say what you did. A change log — which rows, which fields, old value and new — is what turns "I compared them" into something a colleague or an auditor can check.
• Keep the original. Every result should be a new file. If your process overwrites the source, you cannot go back and check the thing you are about to rely on.

Doing it without uploading anything

Spreadsheets of invoices, members or staff are exactly the kind of file that should not be posted to a website to be processed. The Spreadsheet Cleanup & Reconciliation tool does all of the above in your browser: the file is read by code running on the page, nothing is sent anywhere, nothing is stored, and every result is a new file you download yourself.

Related: Developer & Data tools for converting between JSON and CSV, and Text Compare when what you are comparing is prose rather than a table.