Data Cleaning & Analysis Templates
Data validation formulas, deduplication tools, pivot table templates, and statistical analysis frameworks in Sheets.
📄 Product Preview
Try the interactive reader and demo tools below, or get the full product with all content unlocked.
📖 Interactive Reader (Free Preview) ⚙ Try Demo Tools 📦 Download Free Sample📁 File Structure 22 files
📖 Documentation Preview README excerpt
Data Cleaning Templates
A spreadsheet toolkit for turning a messy data export into a clean, deduplicated,
analysis-ready table — and then actually analyzing it — entirely in **Google Sheets or
Excel**, with no add-ons, macros, or API keys.
The workbook is built around two worked datasets you can trace end to end:
1. A messy customer export (22 rows, deliberately dirty) that you clean field by
field, validate, normalize, and deduplicate down to 18 unique records.
2. A clean transactions table (31 orders) that you summarize with pivot tables and
profile with descriptive statistics.
Every transformation is shown twice — once as the before and once as the after — so
you can see exactly what each formula does and copy the pattern onto your own data. Every
number reconciles: the deduplication drops exactly the four rows you can see are
duplicates, the pivot totals add up to the transaction total, and the statistics match the
raw values to the cent.
Who this is for
- Analysts who get a CSV export and need it clean today, not after a week of scripting
- Ops, sales, and marketing teams cleaning CRM, lead, or signup lists by hand
- Anyone who has ever fought leading spaces,
USA/U.S.A./United States, or duplicate
contacts in a spreadsheet
- People who know pivot tables exist but have never been shown a reliable recipe for one
No programming required. If you can copy a formula and drag it down, you can use every
tab in this workbook.
What's included
| Tab | File | What it does |
|---|---|---|
| 1. Dirty Data | sheets/01-dirty-data.csv | The raw messy export: bad casing, stray spaces, mixed date/phone/country formats, duplicates |
| 2. Cleaned Data | sheets/02-cleaned-data.csv | The same 22 rows after field-level cleaning (trim, case, phone, date, number, normalize) |
| 3. Deduplication | sheets/03-deduplication.csv | A match key plus keep/drop decision that removes the 4 duplicate rows |
| 4. Validation Rules | sheets/04-validation-rules.csv | 10 reusable validation checks, each with a real formula and pass/fail examples |
| 5. Normalization Map | sheets/05-normalization-map.csv | Lookup table mapping messy variants (USA, can) to canonical values |
| 6. Pivot Source | sheets/06-pivot-source.csv | A clean 31-row transactions fact table (region, category, segment, revenue) |
| 7. Pivot Summary | sheets/07-pivot-summary.csv | Revenue by region × category with row, column, and grand totals |
| 8. Statistical Summary | sheets/08-statistical-summary.csv | 16 descriptive statistics on the revenue column, including outlier fences |
Supporting material:
formulas/FORMULAS.md— every formula, with real cell references, worked numbers, and both Google Sheets and Excel syntaxguides/data-cleaning-checklist.md— a repeatable 12-step cleaning checklist you can run on any datasetguides/pivot-table-recipes.md— five copy-paste pivot recipes plus aQUERY/SUMIFSformula alternativedocs/— getting-started, CSV import, and customization guides
The worked example at a glance
Cleaning half. The dirty export has 22 rows. Field-level cleaning standardizes every
column — for example raw " john SMITH " becomes John Smith, "John.Smith@EXAMPLE.com "
becomes john.smith@example.com, "+1 (555) 207-3322" becomes (555) 207-3322, and
"12-Feb-2024", "01/22/2024", and "2024/2/19" all become ISO 2024-02-12 style dates.
Deduplication then keys on the cleaned email and drops the 4 duplicate rows (records 19,
20, 21, 22 — repeats of records 1, 5, 10, 16), leaving 18 unique customers whose annual
spend totals $36,906.90 (average $2,050.38).
... continues with setup instructions, usage examples, and more.
📄 Content Sample guides/data-cleaning-checklist.md
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
... and much more in the full download.