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. "John.Smith@EXAMPLE.com " and

"john.smith@example.com" 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 support@datanest.dev.

Pivot Table Recipes

Five copy-paste recipes for summarizing the transactions in 06-Pivot Source, each shown

two ways: as a pivot table (fast) and as a formula (so you can see exactly how the

number is built). Every figure below matches the sample data, which totals $87,449.00

across 31 orders.

Source columns: `A Order_ID · B Order_Date · C Region · D Category · E Segment · F Quantity

· G Unit_Price_USD · H Revenue_USD`.

How to insert a pivot table (once)

1. Click any cell inside the data, or select A1:H32.

2. Google Sheets: Insert → Pivot table → *New sheet*. Excel: Insert → PivotTable → OK.

3. The editor shows four drop zones: Rows, Columns, Values, Filters.

4. Drag fields into the zones as each recipe describes. Money fields go to Values, set to

SUM (Sheets) / Sum of (Excel).

That's the whole skill. The recipes below are just different combinations of those drop

zones.

Recipe 1 — Revenue by Region × Category (the cross-tab)

The classic two-dimensional summary.

Pivot table: Region → Rows, Category → Columns, Revenue_USD → Values (Sum).

Formula version (this is what 07-Pivot Summary does):

=SUMIFS('06-Pivot Source'!$H:$H,
        '06-Pivot Source'!$C:$C, $A2,
        '06-Pivot Source'!$D:$D, B$1)

with regions in A2:A5 and category headers in B1:D1. Drag across and down.

Result:

The story: East dominates (54% of revenue) and Services is the biggest category —

but East's Services number ($31,100) is inflated by one giant order (see Recipe 5 and the

outlier note in the statistics tab).

Recipe 2 — Revenue by Month (the trend)

To group by month you need a month key, because the dates are daily.

Add a helper column on the source (say column I): =TEXT(B2, "yyyy-mm") → 2024-01.

Pivot table: Month helper → Rows, Revenue_USD → Values (Sum). (In Excel you can instead

drag the real Order_Date to Rows and right-click → Group → Months.)

Formula version:

=SUMIFS('06-Pivot Source'!$H:$H, '06-Pivot Source'!$I:$I, "2024-01")

Result:

Revenue climbs steadily, then May spikes — partly real growth, partly the $20,000 outlier

order. Always reconcile a suspicious jump against the order detail before celebrating.

Recipe 3 — Two measures by Segment (revenue *and* count)

Pivots happily show more than one measure at once.

Pivot table: Segment → Rows; drag Revenue_USD to Values twice — set the first to

SUM and the second to COUNT (Sheets: summarize by COUNTA; Excel: Count).

Formula version:

Revenue:  =SUMIF('06-Pivot Source'!$E:$E, $A2, '06-Pivot Source'!$H:$H)
Orders:   =COUNTIF('06-Pivot Source'!$E:$E, $A2)

Result:

Enterprise is 84% of revenue from 52% of the orders — each enterprise deal is worth

roughly 5× an SMB one. That single comparison often reshapes where a team spends its time.

Recipe 4 — Average Order Value by Region (AVERAGEIFS)

Sums can hide a lot; averages reveal deal size.

Pivot table: Region → Rows, Revenue_USD → Values, then set *Summarize by* → Average.

Formula version:

=AVERAGEIFS('06-Pivot Source'!$H:$H, '06-Pivot Source'!$C:$C, $A2)

Result: East 5,869, North 3,315, West 1,093, South 790. East's high

average is again the outlier's fingerprint — compare it with East's *median* (Recipe 5's

caution) before reading too much into it.

Recipe 5 — Each Region's % of Total

Pivot table: Region → Rows, Revenue_USD → Values, then *Show values as* → **% of grand

total** (Excel) / *Show as → % of grand total* (Sheets).

Formula version:

=SUMIF('06-Pivot Source'!$C:$C, $A2, '06-Pivot Source'!$H:$H) / SUM('06-Pivot Source'!$H:$H)

Format as percent.

Result: East 53.7%, North 30.3%, West 8.8%, South 7.2%. Two regions make

up 84% of revenue — a textbook concentration that's worth knowing before you plan headcount

or inventory.

Caution on averages and outliers. Recipes 4 and 5 are both skewed by ORD-1031

($20,000, East/Services). When one order can move a regional average by thousands, report

the median alongside the mean, or exclude flagged outliers and note that you did. The

statistics tab gives you the fences to defend that decision.

Bonus — Running total (cumulative revenue by month)

Once you have monthly revenue in B2:B6, a cumulative column is one anchored SUM:

=SUM($B$2:B2)

Drag down: 9,092 → 23,016 → 37,694 → 53,178 → 87,449. The final running total must equal the

grand total — a built-in reconciliation check.

The one-formula pivot: QUERY (Google Sheets)

When you want a quick cut without building a pivot table, QUERY does grouping, sorting, and

labeling in a single cell:

=QUERY('06-Pivot Source'!A1:H32,
       "select C, sum(H), count(A), avg(H)
        group by C
        order by sum(H) desc
        label sum(H) 'Revenue', count(A) 'Orders', avg(H) 'Avg'", 1)

For a full cross-tab in one cell, add a pivot clause:

=QUERY('06-Pivot Source'!A1:H32, "select C, sum(H) group by C pivot D", 1)

Excel users get the same flexibility from a real PivotTable or from SUMIFS/AVERAGEIFS

grids as shown above.

Pivot-table troubleshooting

Questions about a cut that isn't here? Email support@datanest.dev.