How to Clean Data in Excel – 8 Easy Ways

Man sitting at a desk with an open computer learning How to Clean Data in Excel

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!

Avatar photo

Excel University

We love sharing the things we've learned about Excel, and we built Excel University to help us do that. Our motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

3 Comments

  1. Valerie on November 9, 2022 at 8:17 am

    I’m sure I’m not the only one to use a current workbook to create a new workbook and removing what is not needed and building on to what is needed. I have many workbooks that started years ago and have evolved many times and what happens is the Styles build up to multiple inconsistent styles being added. I found a macro years ago that I have as an add-in to delete all custom styles which helps me keeps my workbooks “clean”. I usually enact it on a blank tab or cell so it doesn’t change any of the current formatting. Just google for Excel delete custom styles.

  2. Mark Regehr on November 9, 2022 at 9:41 am

    I have also created macros to standardize headers, delete columns & rows that are not needed and find specific headers that are not always present in new data.

  3. Elizabeth Frost on November 9, 2022 at 6:47 pm

    This blog was so timely in that I needed to create a lookup table for a power query. I kept getting the error message about not being able to create a relationship in a Data Model because there were duplicates in the primary table.

    The challenge was actually finding the duplicates because the COUNTIF formula (in worksheet mode) showed the number “1” on each row (a.k.a. no duplicates). I looked at several of the options that were identified in the blog including the “remove duplicates” option in the Data Model as well. It turns out that PQ considers a space or two after a word or phrase the same as the word or phrase without trailing spaces. So, where I needed to list two separate phrases: “TAX RETURN” and “TAX RETURN ” (with an added space at the end of the second phrase), those two phrases are considered the same/duplicates in PQ yet considered two individual phrases in an Excel worksheet.

    So, I finally figured out why the error message kept happening and was able to manually delete the necessary rows while in worksheet mode in order to allow a relationship to be created in a PQ Data Model. As a result of searching for how to get my data to work, I looked at many of the “cleaning data” options noted in this blog. It was just so timely that the blog came out at the same time that I encountered my “duplication” issue.

    I take away more knowledge about how to clean my data for use in PQ, but also on how to design my lookup tables.

Leave a Comment