Guides
Fixing spreadsheet data, properly
Short, practical notes on the problems that show up in almost every export, and how to fix each one without losing data.
- 01
Fixing dates that Excel mangled
Why 03/04/2024 is two different days, how Excel turns dates into numbers like 45296, and how to get one clean format back.
- 02
Removing duplicates without losing the rows you need
Exact duplicates, duplicates by key, keeping the newest record, and the near-duplicates that whitespace and capital letters hide.
- 03
Turning “$1,234.50” and “(45.00)” into numbers
Currency symbols, thousands separators, accounting negatives, percentages and the decimal comma.
- 04
Merging monthly exports into one table
Stacking files whose columns do not quite match, and keeping track of which row came from where.
- 05
Why Excel shows “é” instead of “é” in your CSV
What UTF-8, Windows-1252 and the byte order mark are, why Excel guesses wrong, and how to export a CSV that opens correctly everywhere.
- 06
Cleaning a folder of exports the same way, every month
Apply one set of cleaning steps to many files at once, download them as a ZIP, and save the steps as a workflow for next time.