Spreadsheet Cleaning Prompts: Rules and Checks
Cleaning data starts with deciding what each column means. A tidy-looking table can still contain changed identifiers, ambiguous dates or lost rows. Use a small anonymised sample to agree the rules, then check a cleaned copy against the original before using it. These prompts concern data preparation rather than formula explanation.
A small workflow
- Keep the original file and describe its columns using a small permitted sample.
- Approve cleanup rules before applying them to the full dataset.
- Check row counts, identifiers and exceptions on the cleaned copy.
Worked example
Two rows contain account IDs 0012 and 12. Removing leading zeros would make them appear identical, but nothing establishes that they refer to the same account. Keep the original identifiers and ask the source owner whether the values represent one account or two.
Agree the cleanup rules
You get: Column-by-column rules and unresolved decisions.
Bring: sample, meanings, use
Here is an anonymised sample [sample] with column meanings [meanings]. The intended use is [use]. Propose a cleanup specification covering whitespace, case, dates, missing values and duplicate handling where relevant. Preserve identifiers exactly. For each rule show the reason and a before/after example using supplied values. Put ambiguous decisions in an approval list; do not clean the whole file yet.
Follow-up
Which proposed rule could change the meaning of a value rather than its formatting?
Inspect ambiguous values
You get: An exception list instead of guessed replacements.
Bring: values, rules
Review these values [values] against the approved column rules [rules]. Separate valid values, clear formatting issues and ambiguous entries. Explain why each ambiguous entry needs confirmation. Do not infer missing dates, currencies, names or identifiers. Return the original value alongside any proposed correction so the change is traceable.
Follow-up
Write the questions I should send to the data owner to resolve the exceptions.
Test a cleanup on a small sample
You get: A cleaned sample and a change log.
Bring: rules, sample
Apply only these approved rules [rules] to this small sample [sample]. Keep an untouched copy of every original row in the output, show the transformed row beside it and list the exact rule used for each change. Preserve row order and identifiers. Leave unresolved entries unchanged and flag them. Do not claim to have processed a file unless a file-processing tool actually ran.
Follow-up
Create two edge-case rows that would reveal an unsafe cleanup rule.
Verify the cleaned result
You get: Reconciliation checks and differences to investigate.
Bring: original, cleaned, rules
Compare the original sample [original] with the cleaned sample [cleaned] using the approved rules [rules]. Check row counts, unchanged identifiers, unintended duplicates and every field not covered by a rule. List differences that cannot be explained by the rules. Do not approve the dataset solely because its formatting is consistent. Suggest a manual check on a few source rows.
Follow-up
Which discrepancy should stop us using this cleaned copy until it is resolved?
Check the result
- Treat identifiers as identifiers, not automatically as numbers.
- Do not guess what an ambiguous date or missing value means.
- Compare record counts and exception rows after every transformation.
