Cleaning a Messy Spreadsheet Without Breaking It

Someone sends you an export and asks for a summary. The file has merged cells in the header, three spellings of the same country, dates that sort in the wrong order and a total row halfway down. Cleaning it is straightforward, but only in a particular order, and only if you keep the ability to prove that nothing was lost. Here is a sequence that works in Excel, Google Sheets or anything similar.

Keep the original untouched

Before anything else, save a copy of the file as received and do not open it again except to compare. Then, inside your working file, keep the raw export on its own sheet and do the cleaning on a second one. Everything you fix should be a step you can repeat, because the sender will email a new export next month and you do not want to do this from memory.

Write down the number of rows in the raw data now. It is the first thing you will check at the end.

Make it one table

Tools can only work with data shaped as a proper table: a single header row of unique names, one record per row, one field per column, and nothing else on the sheet.

• Unmerge every merged cell. Merged cells break sorting, filtering and almost every formula. Where a merged header spanned two columns, give each column its own name.
• Remove totals, blank spacer rows and notes. A total row inside the data will be counted as a record by every summary you build afterwards. Move notes to a separate sheet rather than deleting them.
• Unstack repeated blocks. Exports sometimes repeat a small table per department down the page. Each block needs to become rows in one table with a department column.
• Split combined fields. A column holding "Name, City" is two fields. Most tools have a split-by-delimiter function; keep the original column until you have checked the result.

Fix the types, starting with text

Values that look identical on screen are often not. Leading and trailing spaces, non-breaking spaces from a web page, and line breaks inside a cell all defeat matching and lookups. Trim and clean every text column first, because the later steps depend on it.

Then numbers. A number stored as text sorts alphabetically and is skipped by sums. The usual causes are a leading apostrophe, a thousands separator, a currency symbol in the cell, or a decimal comma read by a tool that expects a point. The quickest diagnosis: a column that suddenly aligns left is text.

Dates are the field that goes wrong most often, because a date is a number the software formats for display, and a date typed as text is neither. Two things matter. First, confirm the column is real dates by changing the format and watching whether the display changes. Second, be careful with day and month order: an export with "03/04" is ambiguous, and half of your rows can be silently swapped. Where you can, ask the sender for an ISO format, with year first.

Make the categories agree

"NL", "Netherlands", "netherlands " and "The Netherlands" are four groups in any summary. Build a small lookup sheet with two columns, the messy value and the clean one, and translate through it rather than editing values by hand. When the next export arrives with a fifth spelling, you add one row to the lookup instead of repeating the whole job.

To find the variants, make a summary of unique values with their counts. The long tail at the bottom, the values appearing once or twice, is where the typos live.

Duplicates: decide what identical means

Exact duplicate rows are easy to remove, but they are rarely the real problem. The awkward case is the same customer entered twice with a different spelling or a different address. Decide which columns define a record, an email address for instance, and look for repeats on those columns only.

Before you delete anything, copy the suspected duplicates to a separate sheet so you can look at them. Some of them will turn out to be genuine repeat records, such as two orders from the same person on the same day.

Check that nothing went missing

This is the step people skip, and it is the one that catches the expensive mistakes. Compare the cleaned sheet against the raw one on at least three measures: the row count after subtracting the rows you deliberately removed, the sum of the main numeric column, and the count of distinct values in a key column. If a total changed and you cannot explain why, something in the cleaning did it.

Add a few standing checks to the working sheet as well: a count of blank cells per column, a count of rows falling outside a plausible date range, and a count of negative values where negatives should not occur. They take five minutes to set up and they will keep catching problems in the next export.

Make it repeatable

If the same file arrives regularly, do the cleaning with a query tool rather than by hand. Excel's Power Query and the equivalents in other tools record each step and replay it against a new file, which turns an afternoon into a click. Even without one, formulas on a second sheet that reference the raw sheet will rerun themselves when you paste in new data, as long as you never edit values in place.

The principle behind all of this is the same: keep the source, describe each change as a step, and prove at the end that the numbers still add up. A clean spreadsheet is worth less than a cleaning process you can run again.