Ian Klosowicz
Messy data has a few reliable signatures: extra spaces, inconsistent text formatting, merged cells, duplicate entries, dates stored as text, and numbers that look like numbers but don't behave like them. None of these are hard to fix once you know the tools. The problem is usually not knowing which tool matches which problem.
This guide covers the cleaning operations that come up most often in Excel, with the specific functions and features that handle each one.
Text data is the most common source of problems because it's easy for small inconsistencies to break matches and filters.
Extra spaces. TRIM() removes leading, trailing, and extra internal spaces from a text string. If cells that look identical aren't matching in a VLOOKUP, extra spaces are the first thing to check. Apply TRIM() to the lookup values, the lookup column, or both.
Inconsistent capitalization. UPPER(), LOWER(), and PROPER() standardize capitalization. PROPER() capitalizes the first letter of each word, which works for most names and titles. Use LOWER() or UPPER() when the destination system expects a specific case.
Extra characters. SUBSTITUTE() replaces specific characters. CLEAN() removes non-printing characters that sometimes come in from copied text or exported files. If a cell looks clean but isn't behaving correctly in formulas, CLEAN() often solves it.
Splitting text into columns. Text to Columns (Data tab) splits a column based on a delimiter (comma, space, pipe) or at fixed positions. For more control over the split logic, TEXTSPLIT() (Excel 365) or LEFT/MID/RIGHT with FIND/SEARCH give you precise control over where to cut.
Excel's built-in duplicate detection is under Data, Remove Duplicates. It compares selected columns and removes rows where all selected columns match a previous row. The order matters: it keeps the first occurrence and removes subsequent ones.
Before removing duplicates, it's worth understanding them. Conditional formatting with "Highlight Duplicate Values" (under Highlight Cell Rules) shows you which cells are duplicated without removing anything, so you can inspect the data before committing to a deletion.
COUNTIF is useful for counting how many times a value appears:
=COUNTIF($A$2:$A$100, A2)
Any row where this returns more than 1 is a duplicate. Filter for values greater than 1 to see only the duplicated rows.
Dates in Excel can be stored as actual date values (serial numbers that display as dates) or as text strings that look like dates. The difference matters because date functions (YEAR, MONTH, EOMONTH, DATEDIF) only work on actual date values, not text strings that happen to look like dates.
How to tell the difference: actual dates are right-aligned by default; text-formatted dates are left-aligned. A more reliable test is to apply a date format to the cell -- if the value changes, it's a real date; if it doesn't change, it's text.
Converting text to dates:
Inconsistent date formats across rows (some cells as MM/DD/YYYY, some as DD-MM-YYYY, some as text) are harder to clean programmatically. Power Query handles this more reliably than Excel formulas because it can detect and normalize date formats during the transformation step.
Numbers stored as text are a common problem from exports and copy-pasting. They look like numbers but SUM() returns 0 and formulas treat them as text.
Indicators: left-aligned by default (numbers are right-aligned), green triangle warning in the top-left corner of the cell, SUM of the range returning 0 or an unexpected value.
Quick fixes:
Blank cells interrupt formulas, cause unexpected results in sorts and filters, and make COUNTIF and aggregation functions work on incomplete data.
Finding blanks: Go To Special (Ctrl+G, then Special) lets you select all blank cells in a range at once. From there you can fill them, delete the rows, or apply formatting.
Filling blanks with the value above: Select the range, Go To Special, Blanks. Without clicking away, type =A1 (or whatever the first data cell is), then press Ctrl+Enter to fill all selected cells with that formula. Then copy and paste as values to lock the results.
ISBLANK() returns TRUE for empty cells, which is useful in IF() statements that need to handle missing data differently: =IF(ISBLANK(A2), "Missing", A2).
Category fields -- status, region, product type -- often accumulate variations over time. "East" and "east" and " East" and "East Region" are four ways the same thing can appear, and they'll all behave as different values in pivot tables and filters.
Approaches:
Should I clean data in the original file or in a separate sheet?
Clean in a separate sheet or column. Keep the original data intact. If something goes wrong with the cleaning formulas, you want to be able to go back to the source. A common structure: one sheet with raw data (no edits), one sheet with cleaned data (formulas referencing the raw sheet), one sheet for analysis.
What's the fastest way to check if a column is clean?
Build a pivot table on that column. Inconsistent values will show up as separate items. If "East" and "east" both appear, the pivot table will list them separately and the count of unique values will be higher than expected.
When should I use Power Query instead of Excel functions for cleaning?
When the cleaning needs to be repeatable. If data comes in monthly with the same problems, Power Query automates the fix so you're not re-doing it each time. For a one-time cleanup, Excel formulas are usually faster to set up.
How do I handle a column with mixed date formats?
There's no clean formula solution for mixed formats. Power Query can detect and normalize date formats more reliably than Excel formulas because it inspects the entire column's format pattern during the transformation. For a one-time fix on a small dataset, manual correction with Find and Replace on the specific format variants is often faster than building a complex formula.
