Many Excel problems that look like formula errors are actually data-quality problems. A lookup may fail because one value has an extra space. A total may be wrong because numbers were imported as text. A PivotTable may split what looks like one category into several variations.
A good cleanup process starts by understanding what each column is supposed to contain. Then fix the data before building formulas or reports on top of it.
A practical Excel data-cleaning workflow
- Make a copy: Preserve the original dataset before changing values.
- Identify the expected format: Decide what a valid value looks like in each column.
- Check blanks and duplicates: Determine whether missing or repeated records are legitimate.
- Fix text issues: Remove unwanted spaces and nonprintable characters.
- Fix data types: Check numbers, dates and IDs that may have been imported as text.
- Standardize values: Decide how variations such as north, North and NORTH should be handled.
- Validate the result: Test formulas and sample records after cleanup.
Remove unwanted spaces with TRIM
TRIM removes leading and trailing spaces and reduces repeated spaces between words. For example:
Microsoft notes that TRIM is designed around the ordinary ASCII space character. Data copied from web pages can contain nonbreaking spaces that TRIM alone does not remove.
Remove nonprintable characters with CLEAN
CLEAN removes many nonprintable characters from imported text:
For imported data, combining cleanup steps can be useful:
Do not assume this fixes every Unicode or formatting issue. Inspect difficult records when the source came from a web page, PDF or external system.
Numbers stored as text
A cell can display something that looks like 100 while Excel treats it as text. That can break arithmetic, sorting and criteria formulas. Check a suspicious column by testing a calculation or examining the cell's behavior. Depending on the source, converting the value to a number or using a controlled import/transform step may be safer than blindly changing the whole column.
Dates stored as text
Dates are especially important because sorting and date criteria depend on real date values. If a date-looking value does not behave like a date, verify its data type before building a report.
Standardize categories
Suppose one department appears as Sales, sales and Sales . Those variations can produce confusing counts or PivotTable categories. Decide on one representation and normalize the source data.
Check blanks before deleting them
A blank can mean different things: missing information, not applicable, or an intentionally empty field. Do not fill every blank with zero or a placeholder until you know what the blank means.
Check duplicates carefully
Repeated values are not automatically duplicate records. A customer can legitimately appear on many order rows. Define the columns that make a complete record unique before removing anything.
Use cleanup columns when safety matters
For important data, create a helper column with the cleaned value rather than overwriting the source immediately. Compare the results, spot-check unusual records, and only then replace the original if appropriate.
Common cleanup mistakes
- Cleaning the only copy of the source data.
- Deleting repeated values without defining what a duplicate means.
- Replacing blanks without understanding their meaning.
- Assuming TRIM removes every kind of invisible character.
- Converting IDs to numbers when leading zeros are meaningful.
- Building a PivotTable before resolving inconsistent categories.
Quick answer
Start by preserving the source, define what clean data should look like, then fix spaces, hidden characters, data types, inconsistent categories, blanks and duplicates in a controlled sequence. Validate a sample before using the cleaned dataset for reports.
Related Excel guides
For duplicate records, see How to Remove Duplicates in Excel. To separate packed fields, see How to Split Text into Columns in Excel. For summaries after cleanup, see How to Create a PivotTable in Excel.
Related Excel guide: How to Use UNIQUE in Excel.