How to Clean Data in Excel – 8 Easy Ways

Any work you conduct in Excel is built around the data in your worksheets. There are many ways that data might go wrong – including the structure, placement, formatting, unnecessary characters, etc. – especially when you’re working with large datasets. Learning how to clean data in Excel will make your work more organized and accurate.

1. How to Clean Data in Excel by Removing Duplicates

Duplicate values can pop up when importing data. You can quickly highlight the duplicate values or delete them.

Highlight duplicates: See our post about highlighting duplicates in Excel with conditional formatting to learn how to highlight consecutive values, check multiple columns, and more.

Delete duplicates: To delete duplicate rows, select the data range and click Data > Remove Duplicates. The resulting dialog box will ask you to confirm which columns must match for the row to be considered a duplicate. Click OK and Excel will remove the duplicate rows.

2. Make Sure Numbers Are Correctly Formatted

We can’t always control the formatting of the data we import into our worksheets, and numbers may occasionally be stored as text values. Keep in mind that numbers stored as text will still appear as numbers in the cell … they are just stored in Excel as a text string. 

Note: one quick indication that a cell value is stored as text is if the value is left-aligned within the cell. By default, Excel left-aligns text values and right-aligns numeric values.

If you use these text cells in calculations, they can cause issues in some formulas. Here’s how to convert numbers stored as text values into numbers stored as numeric values.

Depending on your version of Excel, you may get an in-cell Number Stored as Text indicator. If so, you can click it and select Convert to Number.

If you’d like to use a formula to convert the text value into a number, you can use the VALUE function. If your text value was in A1, you could write the following formula into another cell like B1:

=VALUE(A1)

Or, you can quickly convert a large range of text values like this:

• Select any empty cell and do a standard Copy (Ctrl + C)
• Select the cell or range that you want to convert to numbers
• On the Home ribbon item, select the arrow under Paste and then Paste Special
• In the resulting Paste Special dialog, select Add and click OK. Excel tries to add 0 to each cell. This forces Excel to convert text that represents a number to a stored number value.

3. Change the Case of Your Text

Names and titles are frequently inconsistent when you import data from text files or inherit a worksheet. Sometimes, the text may be written entirely in lowercase, uppercase, or a combination of the two. Luckily, there are three functions that make converting text easy:

• LOWER –  Changes all text to lowercase letters
• UPPER – Converts all text into uppercase letters
• PROPER – Converts all text into proper case

For example, if cell A1 contained an email address in this format: [email protected], you could write this formula in a cell like B1 to convert it to lowercase:

=LOWER(A1)

When you hit Enter … bam: [email protected].

Note: we can also use Power Query to perform these tasks … see the Power Query bonus below.

4. Eliminate Extra Spaces

Extra spaces can be challenging to identify and remove. They may be more obvious if there are several spaces in a text string, but trailing spaces can be tricky. When a statement or word ends with a blank space, this is known as a trailing space. 

Here’s how to get rid of them using the TRIM function. Start by selecting any empty cell … typically a cell to the right and at the top of the data range.

Then, write the following formula, where range represents the range to trim:

=TRIM(range)

The formula will return the original values, but without the extra spaces.

Note: depending on your version of Excel, you may need to fill the formula down.

If you’d like to replace the original values with the trimmed formula results, copy the formula cells and do a standard Copy. Then, select the original value range and do a Paste Special as Values. Then, delete the formulas.

5. How to Clean Data in Excel by Highlighting Errors

You may run into a few formula errors when copying data or building complex reports. If the worksheet is small, these errors are simple to identify. But tracking down formula errors can be time-consulting with large worksheets. Two options that may help are to have Excel (1) Select all error cells or (2) Highlight all error cells.

To Select Error Cells: You can have Excel select all error cells on a worksheet at once, even if there are multiple error cells. To do so:

• Select any single cell in the worksheet
• Home > Find & Select > Go To Special
• Select Formulas, and then check the Errors checkbox only
• Click OK

Now, all formulas that return an error are selected. To advance through them, just hit the Tab key.

To Highlight Error Cells: You can also have Excel highlight all error cells with Conditional Formatting:

• Select all cells in the worksheet by clicking the top-left corner of the sheet, or Ctrl+A.
• Home > Conditional Formatting > Highlight Cell Rules > More Rules.
• Format only cells with: select Errors from the drop-down.
• Pick the desired format and click OK.
• When you update the formula and remove the error, the formatting will go back to normal.

Now, all cells with errors are highlighted with the desired format. The good news is that once you correct the error, Excel will automatically remove the selected formatting. And, if any new errors pop-up, Excel will automatically highlight them by applying the selected formatting.

6. Delete All Formatting

Coloring your cell backgrounds, aligning the text within them, adding borders, etc. are all considered formatting. 

Ensuring that the formatting is uniform is a good way to keep your data consistent and easy to read. In some situations, eliminating the existing formatting enables you to start fresh.

You can remove the formatting from all worksheet cells:

• Select all worksheet cells by clicking the upper-left corner of the sheet or Ctrl+A
• Home > Editing group > Clear > Clear Formats

To quickly remove any conditional formatting rules, Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

7. Use Find and Replace

When it comes to cleaning up data in Excel, Find and Replace is essential. You can easily identify and adjust formatting, find and eliminate all zeros, change formula references, and more. And, it’s pretty easy to use!

You’ll see the Find and Select option in the Home ribbon tab. When you select the Replace drop-down item, the Find and Replace dialog will open. There, click the Options button to reveal numerous options, including format, search within, search by, match case, and more.

8. Remember to Use Spell Check

Before sharing or presenting your work, it’s a good idea to spell check it. The built-in spell checker in Excel makes it easy! 

You can use the following steps to check your spelling in the worksheet.

• Go to the Review tab and click the abc Spelling button.
• Excel will automatically identify misspelled words and display the suggested spelling in the dialogue box.
• The suggestions can be accepted or ignored as needed.
• There are several options to explore as well.

Bonus: Power Query

In addition to the manual techniques above, you may want to check out Power Query if you end up applying the same cleaning steps to the same type of imported data on a recurring basis. It will take a little more effort to learn and to create the process. But the benefit is that, once you create the query and define the steps, you can click Refresh in subsequent periods. So, depending on your data and how often you have to clean it, Power Query may be a great option to explore. We have tons of Power Query posts to get you started!

Cleaning data in Excel can be a multi-step process, but it makes all the difference when it comes to worksheet readability. 

Do you have any more easy tips for cleaning your data in Excel? Let us know in the comments!