The data cleaning checklist every freelance analyst needs

Your dashboard shipped Tuesday. Wednesday morning the client writes: "Nice work, but the customer count says 1,204 and our CRM says 1,190 — which is right?" You already know the answer, and it isn't the CRM. Somewhere in the file, the same fourteen customers are being counted twice because of trailing spaces and casing you never checked. That fourteen-customer gap is what turns a confident delivery into an afternoon of apologising. A checklist exists so that conversation never happens.

Why one skipped check costs you a distinct count

Here is the kind of thing that slips past when you clean by eye. A Customer column, five rows, one actual company:

Customer
Acme Corp
Acme Corp        <- two trailing spaces
acme corp
Acme  Corp       <- double space in the middle
ACME CORP

A distinct count over that column returns 5. The real answer is 1. Every "customers" KPI, every per-customer average, every "top 10 accounts" chart is now wrong by a factor you can't see. You can catch it before it ships with one formula that counts distinct values as the file actually stores them:

=SUMPRODUCT(1/COUNTIF(B2:B1191,B2:B1191))

If that number is higher than the client's own count, you have invisible duplicates. Normalize the column with =UPPER(TRIM(SUBSTITUTE(B2," "," "))) before you group on it, and the five rows collapse back to one. This single check is item seven below — but the point of a checklist is that you never rely on remembering item seven at 11pm.

The 12-point checklist

Run these in order on every client dataset. The order is deliberate: structure before values, values before de-duplication, de-duplication before validation.

1. Profile the raw file — row count, column data types, and where blanks concentrate.
2. Confirm the header row — real names in row 1, no title banners above the data.
3. Remove empty rows and columns that exist only for spacing.
4. Unmerge and fill down so every row is self-contained.
5. Trim whitespace and fix casing on every text field.
6. Standardize dates to one format and convert text-dates to real dates.
7. Normalize before you group — apply the UPPER(TRIM()) pass to key columns.
8. Clean numeric columns — strip currency symbols, separators, and stray text.
9. De-duplicate on the key, checking near-matches, not just exact ones.
10. Validate ranges — no impossible negatives, dates inside the period.
11. Reconcile one total against a number the client already trusts.
12. Log every change in plain words for a defensible audit trail.

The three items people skip under deadline

Date standardization (item 6). A column that mixes 03/04/2026 and 2026-04-03 imports as two different months the moment the locale is guessed wrong — March in one row, April in the next. Force one format, yyyy-mm-dd, before anything downstream reads it, and the ambiguity disappears.

Reconciliation (item 11). Pick one number the client already knows by heart — last quarter's revenue, the headcount, the total order value — and match it to the penny before you send. If it doesn't tie out, you have found a cleaning error, not a rounding difference. This is the single check that catches the most damage for the least effort, and almost nobody does it.

The change log (item 12). "Removed 14 duplicate customers, standardized 2,300 dates, dropped 6 rows with no order ID." Three sentences, written as you go, not reconstructed from memory afterward. It is also the paragraph that lets you bill cleaning as its own line item, because it turns invisible work into something the client can read.

Make the checklist repeatable, not heroic

A checklist you run from memory is a checklist you will skip under deadline. The analysts who never ship a wrong customer count are not more disciplined — they made the list mechanical. Turn it into a saved sequence you apply to each new file, so item eleven, the reconciliation, happens whether or not you remember it exists. Consistency is the product you are actually selling; the charts are just what the client sees, and the checklist is what stops you re-earning their trust from scratch on every engagement.