Clean a spreadsheet without losing meaning
Preserve the original, define rules and distinguish missing values from errors before changing a table.
Follow the method ↓
The answer in 30 seconds
Trimming spaces or changing dates can make a table easier to work with. It can also damage identifiers, erase meaningful distinctions and hide how a result was produced if the original is overwritten.
Keep an untouched source, document the intended meaning of each column, clean a copy with explicit rules and compare row counts, identifiers and sample values before using the result.
Examples to adapt
Keep them as text so leading zeroes remain visible.
Distinguish not recorded from not applicable before filling any cell.
Standardise spacing on a copy, but do not merge distinct people because names look similar.
Follow the method
- 1Preserve the source
Save the original file and record its source and download date.
- 2Define columns
Write down expected type, unit, allowed blanks and any identifier format.
- 3Apply one rule at a time
Clean a duplicate file and record transformations, including any excluded rows.
- 4Validate
Compare counts, unique identifiers and a sample of changed cells with the original.
A checklist to keep
Use these checks to record what you found. The grid supports a decision; it does not make one for you.
| Check | What to examine | Action |
|---|---|---|
| Original | Is an untouched copy available? | Keep |
| Column | What does each field mean? | Define |
| Rule | Why change a value? | Record |
| Missing | What do blanks represent? | Distinguish |
| Validation | Did counts or identifiers change? | Compare |
Free, no sign-up. UTF-8 text with semicolon-separated columns.
What to check
An empty cell is not automatically zero.
An identifier may look numeric but must remain text.
A convenient automatic conversion can change dates or precision.
Common questions
Should I delete all duplicates?
No. First define the unit of observation and decide whether repeated rows are legitimate.
When is the cleaned file ready?
When its rules are documented and checks against the original support the intended use.
Sources and documentation
Documentation consulted on . Examples are illustrative; interfaces and results may change.