The 12-Step Data Cleaning Checklist

A repeatable process for turning any messy export into an analysis-ready table. Run it

top to bottom every time and you'll never again wonder "did I forget something?" The steps

map directly onto the tabs in this workbook; worked numbers match the sample data.

Golden rule: never clean in place. Keep the raw export on its own tab (`01-Dirty

Data`) and build everything else from it. If a step goes wrong, you can always start over

from the original.

Before you start: profile the data

Spend two minutes seeing what you're dealing with before changing anything.

• Row & column count: =ROWS(A:A) and eyeball the headers.
• Distinct values per categorical column: =UNIQUE(E2:E23) shows every spelling of

Country at once — the fastest way to spot USA / U.S.A. / Unites States.

• Blanks per column: =COUNTBLANK(C2:C23).
• Obvious junk: sort each column ascending and descending and look at the extremes.

This tells you which of the steps below you actually need.

Step 1 — Trim whitespace

Leading, trailing, and double spaces are the most common (and most invisible) defect. TRIM

fixes all three.

=TRIM(B2)

" john SMITH " → john SMITH; "Aisha Khan" → Aisha Khan. Test it with

=EXACT(B2, TRIM(B2)) — any FALSE had hidden spaces.

Step 2 — Fix capitalization

Pick one convention per column. Names → PROPER; emails and codes → LOWER; country/state

abbreviations → UPPER.

=PROPER(TRIM(B2))     =LOWER(TRIM(C2))     =UPPER(TRIM(StateCode))

Watch the PROPER edge cases (McDonald, iPhone, O'Connor is fine) and fix the few

exceptions by hand.

Step 3 — Standardize formats (phone, date, currency)

Make every value in a column look identical:

• Phone: strip non-digits, take the last 10, rebuild → (555) 207-3322.
• Date: =TEXT(DATEVALUE(F2), "yyyy-mm-dd") collapses four input formats into one.
• Currency: =VALUE(SUBSTITUTE(SUBSTITUTE(G2,"$",""),",","")) turns "$3,450.50" into

a real number 3450.5.

A column you can't SUM or SORT correctly isn't clean yet.

Step 4 — Normalize categorical values

Map every variant to one canonical value using the Normalization Map and XLOOKUP. After

this step, COUNTIF(Country, "United States") returns the true count instead of being split

across eight spellings.

Step 5 — Split or combine fields

One value per cell. Split Full_Name into first/last with SPLIT/TEXTSPLIT if you need to

sort by surname; combine City + State only for display, never for storage.

Step 6 — Handle missing values deliberately

Decide per column — don't leave it to chance:

• Required field blank (e.g. email) → flag the row for follow-up, don't invent data.
• Optional field blank → fill with an explicit "Unknown" / "N/A" so blanks don't

masquerade as zero. =IF(TRIM(H2)="", "Unknown", H2).

• Numeric blank → usually leave truly empty (so it's excluded from AVERAGE), *not* 0.

Step 7 — Fix data types

Numbers stored as text are the classic silent bug (they left-align and break SUM). Force

the type: =VALUE(text) for numbers, =DATEVALUE(text) for dates. Confirm with =ISNUMBER.

Step 8 — Validate against rules

Apply the checks from 04-Validation Rules as columns. A master flag —

=IF(COUNTIF(RuleCols, FALSE)=0, "OK", "Needs review") — turns the sheet into a self-audit.

Fix or quarantine every Needs review row before moving on.

Step 9 — Deduplicate

Only now, with formats normalized, is your match key reliable. Build it, flag duplicates with

the running COUNTIF, and filter to Keep. In the sample this drops records 19–22 and

leaves 18 unique customers.

Order matters: clean before you dedup. "[email protected] " and

"[email protected]" only collapse into one once Steps 1–2 have run.

Step 10 — Spot outliers and errors

Run the descriptive statistics. An IQR fence test (Q1 − 1.5·IQR, Q3 + 1.5·IQR) flags

values that deserve a second look. In the sample it catches ORD-1031 at $20,000. An outlier

isn't automatically wrong — it might be a real whale of a deal — but every one earns a

"is this right?" check before you report an average.

Step 11 — Sanity-check the totals

Reconcile. Does the cleaned row count make sense (22 raw − 4 dupes = 18)? Does the pivot

grand total equal the sum of the source rows ($87,449.00 both ways)? Does spend per

customer look plausible? A number that doesn't tie out is a bug, not a feature.

Step 12 — Document and freeze

• Note what you did (which columns, which rules) in a README cell or tab — future-you will

thank you.

• When a cleaned column is final, Paste Special → Values to freeze it and keep the file

fast.

• Save the empty version as a template so next month is paste-and-drag.

The one-screen version

Pin this list next to your monitor. Cleaning data well is not about cleverness — it's about

running the same boring checklist every single time.

Questions about applying this to your data? Email [email protected].