Cleaning before you analyze (Sheets edition)

Cleaning is the work everyone postpones because analysis feels more senior. Then the chart is wrong because of a trailing space, a date that is really text, or three spellings of the same status. Cleaning is not glamorous. It is how you stop lying by accident.

This is Part 4 of From spreadsheets to real data. We stay in Sheets/Excel on purpose: practical steps before Python or a warehouse.

What you’ll learn

Example:

Clean categories

A repeatable cleaning pass order
Trim, case, dates, splits, dedupes, nulls, types
How grain decides what “duplicate” means
A before/after checklist you can reuse weekly

Clean in a copy; keep a raw landing zone

Example:

Trim and standardize categories

Never clean on top of the only copy of an export. Keep raw_immutable (or a dated raw file) and build clean_ steps on another tab or file. If you cannot undo, you cannot audit.

Order matters: structure first, then values, then dedupe.

A practical cleaning order

Structure: tidy headers, unmerge, remove spacer rows (Part 2).
Trim and normalize text: TRIM, consistent casing for categories.
Dates and numbers: coerce types; fix text-dates.
Split overloaded cells: one fact per cell.
Null policy: blanks vs zeros vs sentinels like N/A.
Deduplicate: only after grain is clear.

Trim and case

“Open” and “Open ” and “open” are three categories to a machine. Trim spaces and pick a case rule for categories (Title Case or lowercase) and stick to it. Free-text comments can stay messy; categorical fields should not.

Dates are where silent bugs breed

If a date column is text, sorts become lexicographic and filters become prayers. Convert to real dates. Prefer ISO display (2026-07-22) for handoffs. Beware locale swaps (03/04/2026 meaning different days in different countries).

Deduping without destroying history

Duplicate rows are not always errors. Two payments can share a customer. Define grain first: one row per invoice line? per invoice? per customer-day? Then dedupe on the key that matches that grain. Keep discarded rows in a quarantine tab if the stakes are high.

Grain	Duplicate means	Keep
One row per order_id	Same order_id twice	One row; investigate
One row per order line	Same order_id many times	Expected
One row per customer	Customer appears twice	Collapse with rule

Nulls and fake values

Zero can mean “measured zero” or “missing.” N/A can mean “not applicable” or “I did not check.” Pick conventions. For analysis exports, empty cells or explicit nulls beat magic numbers like -999 unless everyone agrees.

Worked mini-pass

Sheets-ish sequence (conceptually):
1) Data > freeze header
2) TRIM on category columns (helper column, then paste values)
3) Format > Number > Date on date columns
4) Split "City, ST" into city / state
5) Remove rows where id is blank
6) Data > Remove duplicates on id if grain = one row per id

Practice this week

Take one recurring export. Create raw_ and clean_ tabs.
Fix one category column casing/trim issue.
Convert one text-date column to dates; prove a sort works.
Write the grain in _meta before you dedupe anything.

Common mistakes

Deduping before defining grain
Cleaning the only copy of raw data
Using color or comments instead of fields
Fixing values by hand without a repeatable step list

Cleaning as a product, not a mood

Ad hoc cleaning produces ad hoc trust. When the same export arrives every Monday, the cleaning steps should be a short runbook a tired person can follow. If a step cannot be written down, it is not a step; it is folklore. Folklore does not scale and does not audit.

Prefer helper columns over in-place edits when learning. In-place edits are faster and easier to regret. Helper columns show the derivation: raw_status next to status_clean. Once stable, paste values and archive the helpers, or keep them if the sheet is small.

Category fields deserve a allowed-values list. If status must be open, pending, or closed, enforce it with validation. Free text categories are where “done”, “Done”, and “complete” become three lifestyles.

For dates, beware of copy-paste from PDFs and emails. Text that looks like a date may not sort as a date. After conversion, spot-check min and max. If your max date is in 1905 or 2099, you have a problem disguised as a chart.

Duplicates, keys, and ethics

Aggressive dedupe can erase real events. Two payments on the same day are not a glitch. Two identical rows from a double export might be. The grain statement decides. When stakes are high, quarantine instead of delete. Deletion feels tidy and destroys evidence.

Also separate operational fixes from analytical cleaning. If you correct a customer name in an analysis extract, you did not fix the CRM. Push durable fixes upstream when you can; otherwise label analysis-only corrections clearly so nobody mistakes them for system truth.

Time-box cleaning without skipping it

Good enough data still needs a floor. Spend a fixed budget: thirty minutes of cleaning for a two-hour analysis, for example. Log what you did not finish. Shipping a chart with known dirt is better than shipping a chart with unknown dirt, as long as the caveats travel with the number.

Putting it into daily practice

Think about the last time two dashboards disagreed. The argument was rarely about chart colors. It was about definitions, grain, filters, and who changed a file without telling anyone. Spreadsheet discipline exists to make those arguments shorter and rarer. When you invest in keys, cleaning, and contracts, you are investing in fewer emergency meetings and more decisions that stay decided.

A useful test for any process is vacation resilience. If the owner disappears for two weeks, can someone else reproduce the number from written rules and a canonical file? If the answer depends on a private brain, you have a single point of failure wearing a friendly grid. Document the boring parts while they are still small enough to remember.

Tools will keep changing. Sheets today, a warehouse tomorrow, a notebook the week after. Habits transfer. Grain statements transfer. Ownership transfers. Hopeful name matching does not transfer; it just fails in a new syntax. Build habits that survive the next platform pitch from a vendor or a well-meaning executive.

Stakeholders often reward speed visibly and quality invisibly. Your job includes making quality visible: row counts, as-of times, exclusion lists, and links to definitions. When quality is visible, it can be valued. When it is invisible, it is treated as optional polish and then blamed when something breaks in public.

None of this requires perfectionism. It requires floors. A floor for structure. A floor for identity. A floor for cleanliness. A floor for handoff. A floor for memory. Below those floors, analysis becomes theater. Above them, even simple tools can support serious work until you truly need heavier infrastructure.

If you teach only one sentence from this series to a new hire, teach this: say what one row means before you sum anything. That single habit prevents a shocking share of wrong totals, broken joins, and confident nonsense in slides that look expensive.

One more pass on judgment calls

Quick recap

Cleaning is structured compassion for your future self. Keep raw copies, normalize text and types, split overloaded cells, set a null policy, and dedupe only with grain in mind. Sheets can do a lot if you are disciplined; later tools get easier when inputs are boring.

Next: Exporting cleanly into SQL or a warehouse. Series: From spreadsheets to real data.

Sources

Hadley Wickham tidy data principles (clean structure enables clean analysis): https://doi.org/10.18637/jss.v059.i10
Analytics Made Simple foundations on good enough data and reading numbers

Make cleaning visible

Add a clean_log tab: date, step, rows in, rows out, notes. When someone asks why a number moved, you have a trail. Invisible cleaning is indistinguishable from silent tampering, even when intentions are pure.

If cleaning takes longer than analysis every week, that is a signal to automate or to push validation upstream, not a signal to skip cleaning.