Why Cleaning Is Half the Assignment
Dirty data — duplicate rows, trailing spaces, mixed date formats — silently corrupts VLOOKUPs, pivots, and charts. A documented 15-minute clean routinely separates A-grades from resubmits. Follow this checklist in order.
The 8-Step Cleaning Checklist
1. Profile first (2 min)
Add filters (Ctrl+Shift+L), scan dropdowns for oddballs ("west", "West ", "WEST"), and run =COUNTA vs =COUNT to spot text-numbers.
2. Remove exact duplicates
Select data → Data → Remove Duplicates → tick only the true key columns (e.g. Order ID), not every column. Excel reports how many rows vanished — screenshot it for your appendix.
3. Kill invisible spaces
=TRIM(CLEAN(A2))
TRIM strips edge/double spaces; CLEAN removes non-printing characters from imports. Paste as Values over the original.
4. Split combined columns
Data → Text to Columns (delimited) or TEXTSPLIT in Excel 365: =TEXTSPLIT(A2, ", ") for "City, Country".
5. Fix dates and numbers
Text-dates: select → Data → Text to Columns → Finish (applies locale parsing). Text-numbers: green triangle → Convert to Number, or =VALUE(A2).
6. Standardize categories
Find & Replace (Ctrl+H) or a mapping table + XLOOKUP: "U.S.A / USA / US" → one canonical value. Add Data Validation dropdowns so it never regresses.
7. Flag fuzzy near-duplicates
=IF(COUNTIF($A$2:$A$1000, "*"&MID(A2,2,5)&"*")>1, "review", "")
Conditional Formatting → Highlight Duplicates catches exact matches; the formula above surfaces near-matches for manual review.
8. Make it refreshable with Power Query
Data → From Table → apply Trim, type changes, dedupe in Applied Steps → Close & Load. Next data drop = one Refresh click. Document the steps with screenshots.
What to Write in Your Report
One short "Data preparation" paragraph: rows received vs rows analyzed, duplicates removed,标准化 decisions, and validation checks (totals before/after). That paragraph signals professional workflow louder than any chart.
Messy 10,000-row file and no time? We clean, dedupe, and document it — with a refreshable query you can defend.
