Guides
Practical guides for messy spreadsheets
How to fix the most common spreadsheet problems by hand — and where an automated pass saves the time.
CSV
How to clean a CSV file
Nine checks in the order that works — including a file that opens in one column, and leading zeros Excel has already dropped.
Duplicates
How to remove duplicate rows in Excel or a CSV
Why a trailing space defeats an exact-match check in every tool, and why you normalize a column before deduplicating it.
Phone numbers
How to standardize phone numbers in a spreadsheet
Why one formula cannot cover mobiles, landlines and 1300 numbers, and why the leading-zero rule changes country to country.
Customer data
How to clean customer data before importing into a CRM
A pre-import checklist in the order that matters, from choosing a match key to test-importing twenty rows first.
Leading zeros
Why Excel removes leading zeros from CSV files
What the spreadsheet is actually doing to a column of identifiers, and why formatting it as Text afterwards does not bring the zeros back.
File formats
CSV vs XLSX for data processing
What each format is good at, and the silent losses in between — dropped leading zeros, date serials, guessed encodings.
Australia
How to validate Australian suburb, state and postcode combinations
Why the three fields only mean something together — 880 suburb names are used in more than one state, and 15 postcodes cross a state border.
Dates
How to find inconsistent date formats in a spreadsheet
Spotting text-stored dates and two-digit years, and proving a column's day/month order from the data rather than guessing.