Data Cleaningbeginner
Check for empty cells with ISBLANK
=IF(ISBLANK(A2),"Missing","OK")
Data Cleaning
Data Cleaning formulas
Remove whitespace, fix casing, strip unwanted characters, and standardize messy data.
Data Cleaningbeginner
Convert text to numbers with VALUE
=VALUE(TRIM(A2))
Data Cleaningbeginner
Flag duplicate values with COUNTIF
=COUNTIF($A$2:$A$100,A2)>1
Data Cleaningbeginner
Remove extra spaces from text with TRIM
=TRIM(A2)
Data Cleaningbeginner
Remove non-printable characters with CLEAN
=CLEAN(A2)
Data Cleaningbeginner
Suppress formula errors with IFERROR
=IFERROR(VLOOKUP(A2,$F$2:$G$20,2,FALSE),"-")
Data Cleaningbeginner
Validate numeric entries with ISNUMBER
=ISNUMBER(A2)
Frequently asked questions
How do I count blank cells in a range?
Use COUNTBLANK(A2:A100) to count all empty cells in the range. It counts both truly blank cells and cells containing an empty string.
Is there a faster way to convert a whole column?
Select the column, go to Data > Text to Columns > Finish — this converts the selection in place without a helper column. VALUE is better for formula-driven pipelines.
How do I mark only the second and subsequent occurrences, not the first?
Use =COUNTIF($A$2:A2,A2)>1 with a mixed reference — the range expands as you fill down, so it only flags rows after the first occurrence.
How do I clean an entire column at once?
Add a helper column with =TRIM(A2), copy it down, copy the results, and paste-as-values over the original column. Then delete the helper column.