Excel Data Cleaning Checklist With a Worked Six-Row Audit
This example keeps the original six records. Three become ready; three need a human decision. The goal is an auditable chain, not silent row deletion.
Follow one dataset through the chain
| Stage | Count | What to verify |
|---|---|---|
| Original input | 6 | Source rows 2–7 are kept unchanged on a separate tab |
| Review required | 3 | One repeated ID/date pair; one missing amount; one bad date |
| Ready for the next workflow | 3 | Rows 4, 5 and 9 on Cleaned review |
Cleaned review!B12:B14 counts raw, review, and ready rows. The expected values are 6, 3, 3, so no row vanishes from the audit. Row 6 repeats A001 and its date after cleanup; row 7 has an empty amount; row 8 has bad-date. The “ready” label is a sample rule, not a declaration that external source data is correct.
Checklist in order
- Inspect. Open a copy and identify columns, data types, blank values, and keys. Confirm source row count before edits.
- Preserve. Keep
Original inputuntouched. In a real file, retain the source file or export as well. - Clean identifiers.
Cleaned review!C4:C9converts the sample NBSP and outer spaces; compare lengths and keys. Do not silently merge identifiers if spaces may be significant. - Validate types. Dates must match a real calendar date in exact
YYYY-MM-DDform: the formula reconstructs the date and compares it with the source string, so2026-02-31,2026-99-99and ten-letter text are flagged. The sample amounts are integer text, avoiding decimal-locale ambiguity; the formula tests numeric coercion before treating a row as ready. Confirm the source separators and locale before applying this to other files. A blank or nonnumeric amount such asoopsis flagged; explicit zero is valid. A truly blank source ID or an ID that becomes empty after cleaning is flagged; text or numeric zero remains a valid ID. - Review duplicates. The status compares cleaned ID and date through the current row, marking later occurrences. Decide which record is authoritative before removing or combining any row.
- Reconcile. Check six source rows equal three ready plus three review. Resolve review rows and rerun checks after every change.
Changing a source cell on Original input updates linked columns and statuses on Cleaned review. A production pipeline still needs its own source schema, numeric-locale choice, approved duplicate policy and separate output of accepted rows. This sample does not auto-delete or publish data.
Use the specialist guides
For a quoted or typed import, begin with CSV preservation. For split columns and ambiguous dates, use those dedicated workflows. For a failing ID match, diagnose hidden characters. For duplicate decisions, preview before deletion. This checklist provides their row-count control in a single worked chain.
Sources: Microsoft Support: cleaning data and Microsoft Support: CSV import.