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 closer look
Clean the format without rewriting the meaning
Fictional example: a table contains a leading-zero identifier, a label surrounded by spaces and a missing measurement. Write a rule for each column.
| Value | Proposed transformation | Justified decision |
|---|---|---|
| 00123 | Convert to a number | Reject for a five-character identifier |
| “ Library ” | Trim surrounding spaces | Possible if those spaces have no meaning; retain the original |
| Empty cell | Replace with 0 | Reject without an explicit definition of missing values |
Make cleaning reproducible: original value, applied rule, resulting value and reason. Then check totals and groupings relevant to the analysis.
Should accents be removed to match names?
Keep the original name. A normalised comparison key can suggest candidates, but similar names do not prove that two records describe the same person or place.
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 |
Read the checklist directly in the table.
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.