Written and maintained by CASRAI Editorial Board
Last updated
Excel cleaning data comes down to a short sequence: keep an untouched copy, fix text problems with helper columns, convert numbers stored as text, highlight duplicates before deleting them, and only then remove them. This guide follows Microsoft’s own support documentation, and adds the failure modes that quietly corrupt research spreadsheets: characters that TRIM and CLEAN do not remove, and a duplicate removal that cannot be undone once the file is saved.
The same steps answer the common phrasings: data cleaning Excel, cleaning Excel data, Excel data cleaning, cleaning data Excel and cleaning data in Excel. They are the in-sheet route for a one-off fix; for a repeatable pipeline, see how to use Power Query, and for the wider workflow see data cleaning steps for research data.
What it is used for in research
Cleaning is the step between a raw export (a survey download, an instrument file, a hand-keyed sheet) and analysis. Microsoft’s guidance describes good source data as having similar data in each column, all columns and rows visible, and no blank rows within the range, and recommends an Excel table for best results. Research teams use these steps to prepare a dataset before it goes into a statistics package or a repository, as part of the broader research data quality framework and data curation.
Data cleaning in Excel versus a repeatable pipeline
The functions below change cells directly, so they suit one-off fixes. If the same export will arrive again next month, record the steps in Power Query instead so they can be re-run (see the Free alternatives section).
Install or access (official source only)
Everything here is a built-in Excel feature: the TRIM, CLEAN, SUBSTITUTE and VALUE functions, Conditional Formatting, Remove Duplicates and the Convert Text to Columns Wizard. The reference pages are on Microsoft Support (listed under Sources). Menu names below follow those pages; if your Excel version labels a command differently, search Microsoft Support for the command name.
Step-by-step: cleaning data in Excel
Before you start
- Back up the original. Microsoft’s first instruction is to create a backup copy of the original data in a separate workbook. Do this before any step that overwrites cells.
- Do tasks that do not need column manipulation first, such as spell check and Find and Replace, as Microsoft recommends.
Cleaning a column with a helper column
- Insert a new column (B) next to the column you are cleaning (A).
- In the top cell of column B, add the formula, for example
=TRIM(CLEAN(A2)). Microsoft’s TRIM page gives this combination as the way to remove extra spaces and nonprintable characters together. - Fill the formula down the column.
- Copy column B and paste it back as values.
- Remove the original column A once you have checked the result.
Working in a helper column means the raw values are never overwritten until you have looked at the cleaned ones.
Convert numbers stored as text
- Numbers imported as text can cause problems with calculations or produce confusing sort orders, according to Microsoft. Use the VALUE function, which converts a text string that represents a number to a number, in a helper column and paste back as values, as above.
Split combined fields
- For a column holding two values (for example surname and given name in one cell), use the Convert Text to Columns Wizard. Microsoft documents it in the article “Split text into different columns with the Convert Text to Columns Wizard”.
Find and remove duplicates
- Highlight first. Select the cells, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and choose a format. Highlighting cannot be applied in the Values area of a PivotTable.
- Copy the sheet. Microsoft states that when you remove duplicates the duplicate data is permanently deleted, so copy the original data to another sheet first.
- Remove any outlines or subtotals from the data, as Microsoft advises, then select the range.
- Choose Data > Remove Duplicates, then under Columns check or clear the columns you want to compare, and select OK.
Output and how to read it
After Remove Duplicates, Excel reports how many duplicate and unique values it found. Microsoft notes that these counts might include empty cells, spaces and similar, so a count that looks too high can be a sign of blank or space-only cells rather than real repeated records. Compare the row count before and after against your backup copy, and re-run the highlight step to confirm nothing you meant to keep was flagged.
Troubleshooting
TRIM did not remove a space
TRIM removes the standard space character (ASCII value 32) and leaves single spaces between words. It does not remove the nonbreaking space (Unicode value 160), which web pages often use as the HTML entity nbsp. Text copied from a web page can therefore still look padded after TRIM. Microsoft’s guidance is that characters above the first 127 need SUBSTITUTE, and it names values 32 and 160 as the two space characters to handle.
CLEAN left odd characters behind
CLEAN removes the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. Other Unicode characters are outside its range and need SUBSTITUTE.
Remove Duplicates misses rows
Microsoft advises removing any outlines or subtotals from the data before trying to remove duplicates. Also check which columns are ticked in the Columns box: a row counts as a duplicate only on the columns you selected.
You removed rows you needed
Because the deletion is permanent once saved, go back to the backup workbook or the copied sheet. This is the reason the backup is step one.
Free alternatives
| Tool | What its own documentation says | When it fits |
|---|---|---|
| Power Query (inside Excel) | Import and clean steps are recorded and can be repeated; see our Power Query guide. | The same export arrives repeatedly. |
| OpenRefine | “A powerful free, open source tool for working with messy data: cleaning it; transforming it from one format into another; and extending it with web services and external data” (openrefine.org, checked 2026-10-29). | Large messy text columns outside Excel. See also data curation tools. |
FAQ
Does TRIM remove every space in Excel?
No. It removes extra standard spaces (ASCII 32) and keeps single spaces between words. It does not remove nonbreaking spaces (value 160).
Can I undo Remove Duplicates after saving?
Microsoft says the duplicate data is permanently deleted, so keep a copy of the original on another sheet or in another workbook.
What should I do before cleaning data in Excel?
Create a backup copy of the original data in a separate workbook, and do tasks that do not require column manipulation, such as spell check and Find and Replace, first.
Why do numbers imported as text cause trouble in Excel?
Microsoft says numbers stored as text can cause problems with calculations or produce confusing sort orders; the VALUE function converts a text string that represents a number to a number.
