Excel Data Cleaning: Formulas and Techniques

Why data cleaning matters

Dirty data breaks everything. A VLOOKUP misses a match because of a trailing space. A SUMIF skips rows because "North " and "North" are different. A pivot table shows duplicate categories because of inconsistent capitalisation. Clean data first, analyse second.

Remove extra spaces — TRIM

=TRIM(A2)
-- Removes leading, trailing, and double spaces
-- "  Sarah  Johnson  " becomes "Sarah Johnson"

Remove non-printable characters — CLEAN

=CLEAN(A2)
-- Removes characters that can't be printed (common in imported data)
-- Combine with TRIM: =TRIM(CLEAN(A2))

Standardise text case

=UPPER(A2)    ALL CAPS
=LOWER(A2)    all lowercase
=PROPER(A2)   First Letter Of Each Word Capitalised

Convert text to numbers

=VALUE(A2)
-- Converts text that looks like a number into an actual number
-- Or multiply by 1: =A2*1
-- Or use Text to Columns: Data → Text to Columns → Finish

Replace or remove specific characters — SUBSTITUTE

=SUBSTITUTE(A2, " ", "")       remove all spaces
=SUBSTITUTE(A2, ",", ".")      replace commas with dots (for decimal formats)
=SUBSTITUTE(A2, "£", "")      remove currency symbols

Standardise inconsistent entries

=SWITCH(TRIM(LOWER(A2)),
  "uk", "United Kingdom",
  "gb", "United Kingdom",
  "united kingdom", "United Kingdom",
  A2)
-- Maps multiple versions of the same thing to one standard form

Flag errors and blanks

=ISBLANK(A2)      TRUE if cell is empty
=ISNUMBER(A2)     TRUE if cell contains a number
=ISERROR(A2)      TRUE if cell contains any error
=IF(ISBLANK(A2), "Missing", A2)   replace blanks with a label

Practice what you just learned

ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.

Start practising free →

Excel data cleaningformulas and techniques