"The report is wrong" almost always means "the data was dirty." Before any analysis, your rows need to be consistent, complete, and correctly typed. Here are the seven problems that break spreadsheets — and a workflow to fix them without silently dropping good data.
1. Duplicate rows
The same record appears twice because of a bad merge or re-import. Fix: sort by a unique key and remove exact duplicates — but only on a key you trust, never on the whole row blindly (two legitimately identical rows can be real).
2. Stray whitespace
"Apple" vs " Apple" never match. Fix: TRIM() every text column, and watch for non-breaking spaces (char 160) that TRIM misses.
3. Mixed formats in one column
A "price" column holding "$1.20", 1.2, and "1,20". Fix: strip currency symbols and thousands separators, then cast to number. If a value won't cast, flag it — don't zero it.
4. Broken dates
03/04/2026 is March 4 or April 3? Depends on locale. Fix: parse dates with an explicit format, never let Excel guess. Store as ISO YYYY-MM-DD.
5. Inconsistent categories
"US", "U.S.", "Usa", "United States". Fix: build a mapping table and normalize to one canonical value.
6. Encoding garbage
"München" becomes "München" after a wrong import. Fix: re-open the file with UTF-8; the "ü" is mojibake from a Latin-1 misread.
7. Embedded commas splitting rows
A quoted field containing a comma breaks CSV parsing. Fix: use a proper CSV parser (not naive split on comma), or switch delimiters.
A repeatable cleanup workflow
- Snapshot the raw file so you can always go back.
- Profile each column: type, null rate, distinct values, min/max.
- Standardize types and casing; flag bad values instead of deleting.
- Dedupe on a trusted key.
- Re-profile to confirm the fixes landed.
👋 Drowning in a 50,000-row export?
If cleanup is eating your week, I turn messy exports into clean, analysis-ready datasets with a repeatable script — so the next export takes minutes, not days. See the Data Cleaning & Excel Automation page, or just email me.