What does cleaning data in Excel mean?
Cleaning data means fixing the problems that stop a spreadsheet from being useful: extra spaces, numbers stored as text, mixed date formats, different spellings of the same name, duplicate rows and blank cells. The goal is a file you can sort, filter, count and analyze without surprises.
The steps below work in Excel. Most also work in Google Sheets, with small differences in menu names.
If you would rather hand the job over, see my data cleaning service, Excel data entry and pricing.
How do you clean messy data in Excel?
Follow the steps in order. Each one is short, and most take a few minutes.
Step 1: Work on a copy
Save a copy of the file before you change anything, or keep the original data on a separate sheet. Some steps, such as removing duplicates, delete data, and you cannot always undo them after you save.
Step 2: Remove extra spaces and hidden characters
Extra spaces make identical values look different. In a helper column, use =TRIM(A2) to remove extra spaces and =CLEAN(A2) to remove non-printing characters. Text copied from websites often contains non-breaking spaces, which TRIM does not remove. For those, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Then copy the helper column and paste it back as values.
Step 3: Fix numbers and dates stored as text
A number stored as text does not add up, and the cell often shows a small green triangle. Select the column, choose Data, then Text to Columns, and click Finish to convert it. You can also use =VALUE(A2).
For dates, select the column, open Text to Columns and go to the last step. Choose Date and pick the order that matches your data, such as DMY or MDY. Then apply one date format to the whole column.
Step 4: Standardize spelling and capitalization
Use =PROPER(A2) for names, and =UPPER(A2) or =LOWER(A2) where needed. For different spellings of the same value, such as USA, U.S. and United States, use Find and Replace (Ctrl+H) to change every variant to one standard value.
Step 5: Split or combine columns
To split full names or addresses, choose Data, then Text to Columns, and pick a delimiter such as a space or a comma. Flash Fill (Ctrl+E) can copy a pattern that you type in the first row. To combine columns, use =A2&" "&B2, or TEXTJOIN in Excel 2019 and later.
Step 6: Remove duplicates
First highlight the duplicates so you can review them. Select the column, then choose Home, Conditional Formatting, Highlight Cells Rules and Duplicate Values. When you are sure, choose Data, then Remove Duplicates, and tick only the columns that define a duplicate, such as an email address or an invoice number.
Step 7: Deal with blanks and errors
Filter each column for blanks and decide whether to fill them, mark them or delete the row. For errors such as #N/A or #DIV/0!, find the cause first. =IFERROR(formula,"") hides an error, so use it only when you know why the error appears.
Step 8: Check the result
Compare the row count with your original, sort and filter each column to spot odd values, and check several rows against the source. Then use Data, Data Validation to allow only valid entries, such as a list, a date range or a number range, so the problems do not come back.
A faster way for repeated clean-ups
If you clean the same type of file every week, use Power Query (Data, Get Data). It records your cleaning steps and applies them again when you refresh the data.
When is it better to have someone clean the data for you?
Manual cleaning works for small files. It becomes slow and error-prone when a file has thousands of rows, many columns, data from several sources or more than one language.
In that case I can clean the file for you and send back organized data in Excel or Google Sheets. I review the finished file against your source data before delivery, checking for missing rows, typos, wrong formats and duplicates. See my data cleaning service and the pricing page. Pricing starts at $5, and I confirm the price before I start.
Frequently asked questions
Quick answers about cleaning data in Excel.
What is the fastest way to clean data in Excel?
For a one-time clean-up, use TRIM, Text to Columns, Find and Replace and Remove Duplicates, in that order. For files you clean regularly, Power Query saves your steps so you can repeat them with one refresh.
How do I remove duplicates in Excel without losing data?
Work on a copy of the file. Highlight duplicates with Conditional Formatting first so you can review them, then use Data, Remove Duplicates and select only the columns that define a duplicate.
Why are my numbers stored as text in Excel?
This often happens when data is imported or copied from a website, a PDF or another system, or when a cell has a leading apostrophe or extra spaces. Text to Columns or the VALUE function converts them back to numbers.
Can I use the same steps in Google Sheets?
Most of them. TRIM, PROPER, UPPER, LOWER, Find and replace and Remove duplicates work in Google Sheets, and Split text to columns replaces Text to Columns. Flash Fill and Power Query are Excel features.
How do I stop messy data from coming back?
Use Data Validation to allow only valid values, such as a drop-down list, a date range or a whole number, and keep one standard format for each column.
Can you clean my Excel data for me?
Yes. I clean messy files and send back organized data in Excel or Google Sheets. Orders, payment and delivery go through Fiverr. See my data cleaning page for details.
Want me to clean your file?
Tell me what your data looks like and what you need. I’ll reply with a clear plan. Back to the Home page.