Spreadsheet Data Cleaning

Spreadsheet data cleaning

Imported data is inconsistent by default: numbers stored as text, dates in several formats, trailing spaces, and the same category spelled three ways. Analysis before cleaning produces confident wrong answers.

Method

1. Inspect before transforming. Sort each column and look at the extremes, which surfaces the format problems immediately (see exploratory-data-analysis).
2. Fix types explicitly. Numbers stored as text do not sum and dates as text do not sort, and both look correct on screen.
3. Trim and normalise text. Whitespace and case inconsistency break every lookup and grouping (see lookup-functions).
4. Standardise categories against a reference list. The same value spelled differently splits every aggregation silently.
5. Handle duplicates deliberately. Decide what constitutes a duplicate and which copy survives before removing anything (see deduplication-queries).
6. Keep the raw import untouched. Clean into a new sheet so the transformation is repeatable and auditable (see data-cleaning).
7. Use Power Query for anything recurring. Manual cleaning is fine once and unsustainable monthly (see power-query).

Boundaries

Cleaning fixes format and consistency, not accuracy: correctly formatted wrong data is still wrong. Aggressive automated cleaning can destroy legitimate variation. Recurring cleaning indicates the upstream source should be fixed instead.