How to clean messy Excel data without losing control

Do not start by deleting what looks wrong

Messy data often contains several problems at once: extra spaces, inconsistent capitalisation, dates interpreted as text, and duplicate-looking records that are not actually identical. Start by making a copy and noting the specific issues you can see.

A short data profile—row count, blank count, unique count, minimum, and maximum—gives you a baseline for checking whether cleaning changed more than intended.

Clean visibly with helper columns

Helper columns let you compare the original and cleaned values side by side. TRIM removes unnecessary spaces, CLEAN removes non-printing characters, and consistent text functions can standardise names or categories.

=TRIM(CLEAN(A2))

• Keep the raw column until checking is complete.
• Filter for blanks and errors after each transformation.
• Confirm dates are real date values, not text that merely looks like a date.
• Review duplicates using the fields that define a genuinely unique record.

Finish with a reconciliation check

Compare record counts and key totals before and after cleaning. If the source contains transaction amounts, the total should remain unchanged unless you intentionally removed invalid or duplicate rows.

Document what changed. A short note about the rules used is often more valuable than an unexplained perfectly tidy table.