How to clean messy Excel data for clients

The file lands at 6:11pm on a Friday: Master_List_FINAL_v3(2).xlsx, 8,400 rows, subject line "here's the data — dashboard by Monday?" You scroll to the Amount column and the weekend is already gone. Power BI will read that column as text, every measure that sums it will return blank or zero, and on Tuesday the client will ask why revenue "looks broken." This is the part of the job nobody sees on the invoice and everybody underpays — so the goal is to make it fast, boring, and identical every time.

Read the column before you fix anything

The instinct is to start deleting. Don't. Spend ninety seconds profiling first, because the fix depends entirely on how the column is broken. Sort the column ascending, then descending. Text values sort separately from numbers, so genuine numbers pool at one end and the junk lands at the other — that split alone tells you how much of the column is text-typed. Then run =COUNTBLANK(D2:D8401) and a quick =SUMPRODUCT(--ISTEXT(D2:D8401)) to count how many cells are text instead of numbers. Now you know the size of the problem instead of guessing.

The example: one Amount column, six kinds of wrong

Here is what that real Amount column actually contains, pulled straight from a client export:

Amount
$1,240.00
1240
1,240
USD 1,240
(320)
1240      <- trailing space
—

Six representations of a number, and only one of them (1240) is a number Power BI will trust. The dollar signs and the USD prefix force the cell to text. The comma is fine in the US locale but breaks the second you hand the file to a colleague on a European machine. The (320) is accountant notation for negative 320 and will import as the literal text "(320)". The em dash is a human saying "no value," which is not the same as zero. The trailing space is invisible and the cruelest of all.

You can neutralize five of these in one pass with a real formula. In an adjacent column:

=IFERROR(
   VALUE(
     SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
       TRIM(D2),"$",""),"USD",""),",",""),"(","-")
   ), "")

That strips the currency symbols and thousands separators, trims the whitespace, turns the opening parenthesis into a minus sign, and returns a real number — or an empty string if the cell was the em dash, so a blank stays a blank instead of becoming a fake zero. Paste-special the result back as values, and the column is finally summable.

A repeatable cleaning sequence

Run these seven steps in this order on every client file. Order matters — fixing values before structure means redoing the values.

1. Copy the raw sheet to a tab named _original and never touch it. You will need to prove what changed.
2. Promote the real header row and delete title banners, logos, or blank spacer rows pasted above the data.
3. Unmerge every merged cell and fill the value down so each row stands on its own.
4. Standardize one column type at a time — currency, then dates, then IDs — using the formula pattern above.
5. Trim whitespace everywhere with TRIM, so " Acme " and "Acme" stop counting as two customers.
6. De-duplicate on the key column, checking near-matches ("Acme Corp" vs "Acme Corp.") not just exact ones.
7. Validate ranges — no negative quantities, dates inside the reporting period, required fields populated — and flag anomalies for the client instead of silently deleting them.

Ship it with a record of what you changed

The difference between a $200 cleanup and a $900 one is the paper trail. When you hand back the file, attach a short change log in plain words: "converted 412 text-formatted amounts to numbers, removed 3 duplicate customers, flagged 11 rows with dates outside Q3." That one paragraph is what lets you charge for cleaning as its own line item, because now the client can see the work they were previously getting for free.