Keep an untouched raw sheet, profile the problems, clean into new columns or Power Query, and verify counts and totals after each step. Normalize spaces, types, labels, and missing values before removing duplicates, because apparent duplicates may differ only by hidden characters or formatting.
- Never make the only copy of source data your cleanup workspace.
- Define what makes a record unique before using Remove Duplicates.
- Validate row counts, missing values, and key totals after cleaning.
Profile the mess before changing it
Filter each important column and scan its distinct values. Look for trailing spaces, mixed date formats, numbers stored as text, inconsistent capitalization, and placeholder values such as N/A, unknown, or a dash. Decide which differences are errors and which are real categories.
TRIM removes ordinary extra spaces, CLEAN removes many nonprinting characters, and VALUE can convert numeric text when the text is valid. Find and Replace is fast but risky when a short label also appears inside longer text.
Remove duplicates by a real business key
A duplicate is not simply a repeated row. Two orders from the same customer may both be valid. An order ID, transaction ID, or a documented combination of fields should define uniqueness. If duplicate keys disagree on other values, investigate before deleting one.
- Count rows before and after deduplication.
- Keep a column that records how the row was cleaned.
- Use Power Query when the same cleanup must run again on next month's file.
Standardize inconsistent region labels
The Region column contains West, west, West with a trailing space, and W.
- Create a mapping table with each source label and one approved label.
- Trim the source value, then use XLOOKUP to return the approved region.
- Filter unmatched values and add them to the mapping only after review.
Common mistakes
- Deleting duplicates before deciding which columns define a unique record.
- Replacing blanks with zero when blank means unknown rather than none.
- Changing source values in place with no record of the original.
- Assuming formatting a text date as a date converts its underlying value.
Try one
Why should you clean spaces and types before removing duplicates?
Rows that mean the same thing may not compare as equal until hidden spaces, labels, and types are normalized.
Sources
- Microsoft Excel help and learningOfficial reference for Excel features, formulas, tables, charts, and data tools.