Why this chapter matters
Dirty data causes wrong groupings, broken joins, and misleading KPIs.
What you will learn
- Normalize text casing, whitespace, and hidden characters.
- Convert mixed-format dates and numeric text into true date and number values.
- Flag duplicates, blanks, and invalid categories with repeatable checks.
Understand the core ideas
Data cleaning in Excel should be a repeatable pipeline, not a one-time manual edit session. Start by profiling common defects: inconsistent casing, hidden spaces, nonprinting characters, text stored as numbers, and inconsistent date formats. Use helper columns to preserve raw values while creating cleaned versions, then compare before replacing source columns. TRIM removes extra spaces, CLEAN removes nonprinting characters, and SUBSTITUTE handles specific unwanted characters such as nonbreaking spaces from web exports. Apply UPPER, LOWER, or PROPER only when your reporting standard requires it, because case changes can affect downstream systems. For numbers stored as text, VALUE or multiplication by 1 can coerce types, but verify decimal and thousands separators based on locale settings.
Date normalization requires extra care because Excel interprets date strings differently by regional format. A value like 03/04/2026 can mean March 4 or April 3 depending on system locale. When possible, import dates in ISO format YYYY-MM-DD, or split components and rebuild with DATE(year,month,day) for certainty. For duplicate detection, combine keys that represent business uniqueness, such as CustomerID plus InvoiceDate, rather than checking one field alone. Add data validation lists for controlled categories like region or channel to prevent new inconsistencies after cleanup. Document each cleaning rule on a notes row so future analysts know why transformed fields differ from raw source columns.
Key terms
- TRIM
- A function that removes extra spaces from text while keeping single spaces between words.
- CLEAN
- A function that removes nonprinting characters that often appear in imported data.
- Data validation
- An Excel rule that restricts allowed inputs, such as values from a list or a date range.
- Type coercion
- Converting a value from one data type to another, such as text to number, so formulas and joins behave correctly.
Clean a Customer Master Export
You receive a CSV where City has mixed case and trailing spaces, SignupDate mixes formats, and CustomerID sometimes includes hidden characters.
- Create helper columns City_Clean, SignupDate_Clean, and CustomerID_Clean so the original raw columns remain intact for audit.
- Set City_Clean to =PROPER(TRIM(CLEAN([@City]))) and inspect a filtered sample to confirm naming policy.
- Set CustomerID_Clean to =TRIM(CLEAN([@CustomerID])) and check length consistency with LEN to catch malformed IDs.
- For date cleanup, split known day, month, and year components when formats vary, then rebuild with =DATE(year,month,day), and format as date.
- Add a duplicate flag using =COUNTIFS(tbl_customers[CustomerID_Clean],[@CustomerID_Clean],tbl_customers[SignupDate_Clean],[@SignupDate_Clean]) and review rows above 1.
A common misconception
Claim: Remove Duplicates is always safe and should be done immediately.
Correction: Automatic removal can delete legitimate repeat events. First define business uniqueness and flag duplicates for review before deleting records.
Lessons in this chapter
- Text normalizationUse TRIM, CLEAN, and case functions to standardize labels.
- Type correctionTurn text-like numbers and dates into real typed values. Read the full guide →
- Duplicate controlIdentify and handle repeated records safely.
- Validation rulesApply data validation to prevent future input errors.
Study task
Chapter checkpoint
Why can two date values that look the same still fail to match in a lookup?
One may be text while the other is a true date serial number, so Excel treats them as different values.