8 Ways to Clean Up Data in Microsoft Excel

Cleaners on a computer

Mistakes happen, especially on your spreadsheets. Typos, extra spaces, duplicates, formula errors, and blank cells can all be troublesome when it’s time to analyze your data. To clean up your Microsoft Excel spreadsheet, try one or more of these ways to take care of “dirty data.”

FYI: need to also clean up the data on your “C” drive? We show you how.

1. Eliminate Extra Spaces

Whether from importing or mistyping, you can end up with extra spaces in your data. This can include leading spaces at the start of a cell, trailing spaces at the end, or miscellaneous spaces between words or values. While they may seem innocent, you can easily run into issues when filtering, sorting, or formulating data with extra spaces.

There are two simple ways to remove extra spaces in your Excel sheet: the TRIM function and Find and Replace tool.

Use the TRIM Function

The TRIM function in Excel works specifically to remove whitespace from your data. The syntax for the formula is simply TRIM(text). Enter a cell reference or the text for the argument.

Let’s look at an example. In cell A1 we have leading, trailing, and extra spaces between the text. Go to another cell, and enter the following formula to remove the whitespace:

=TRIM(A1)
Trim function using a cell reference

The function strips the spaces and provides us with clean data.

For another example, we’ll enter the text as the argument instead of the cell reference. Be sure to enclose the text within quotation marks using this formula:

=TRIM(“   John    Jacob    Smith   ”)
Trim function using text

You will see that the spaces are removed.

The important thing to note about using the TRIM function is that you enter the formula in a cell other than the one that contains the data – unless you want to replace that data.

Tip: you may find these advanced Excel formulas very useful.

Use the Find and Replace Tool

If you have extra spaces scattered about your sheet, you may prefer to use Excel’s Find and Replace tool instead of the TRIM function. With this method, you can remove all or a specific number of spaces.

  1. Go to the “Home” tab, open the “Find & Select” drop-down menu in the Editing section, and select “Replace.”
Replace in the Find and Select menu
  1. In the Find and Replace box that appears, use the “Find what” box to enter a single space if you want to remove them all or the exact number of spaces you want to remove.
Find What field with spaces
  1. In the “Replace with” box, enter nothing if you want to remove all spaces or the exact number of spaces you want to keep.
Replace With field with one space
  1. Select “Replace All” to remove the extra spaces, and you’ll see your sheet update immediately.
Replace All button and results

2. Remove Non-Printing Characters

Non-printing characters are similar to extra spaces that can pop up. These are the first 32 characters, including zero, in the 7-bit ASCII code for things like a null character, the start of a heading, form feed, carriage return, and unit separator. Like spaces, these characters can cause problems when calculating formulas, sorting, or filtering in your Excel sheet.

Conveniently, you can use one simple function to remove non-printing characters in Excel. The function is called CLEAN, and the syntax for the formula is CLEAN(text), where you can enter the text or a cell reference for the argument.

In this example, we have the non-printing characters CHAR(10) at the start and end of our formula in cell A1.

Non printing characters in a formula

Use this formula to remove them:

=CLEAN(A1)
Clean function using a cell reference

You can also enter the text as part of the CLEAN formula. Use the following formula if you want to replace the existing text with clean text rather than use a separate cell.

=CLEAN(CHAR(10)&"John Jacob Smith"&CHAR(10))
Clean function using text

The non-printing characters are removed, leaving only the text behind.

3. Delete Blank Rows

If you end up with blank rows in your sheet, these can take up much-needed space for actual data. You can remove the blank rows in Excel in just a few steps and regain space in your sheet.

  1. Go to the “Home” tab, open the “Find & Select” drop-down menu in the Editing section, and select “Go To Special.”
Go To Special in the Find and Select menu
  1. In the window that appears, select “Blanks,” and click “OK.”
Go To Special Blanks marked and OK button
  1. You’ll see all blanks highlighted temporarily. Before you delete the rows, review them to confirm that there is no other data. This is especially important if you can’t see the additional columns without scrolling to the right. To remove the blank rows, return to the “Home” tab, open the “Delete” drop-down menu, and select “Delete Sheet Rows.”
Delete Sheet Rows in the Delete menu
  1. You will see the blank rows vanish and the remaining rows in your sheet shift upward.
Blank rows removed in Excel

Good to know: learn how to combine text in Microsoft Excel.

4. Correct the Letter Case

If you’re importing data, you may find mixed letter cases among your data. You can use a couple of simple case functions in Excel to make the case consistent.

LOWER converts all letters to lower case, UPPER converts all letters to upper case, and PROPER capitalizes the first letter of each word in a text string and changes all other letters to lower case. Additionally, PROPER capitalizes the first letter after a character other than a letter, such as 1a to 1A.

The syntax for each is the same, LOWER(text), UPPER(text), and PROPER(text), where you can use the text or a cell reference for the argument. Let’s look at examples.

To change all letters to lower case in cell A1, use this formula:

=LOWER(A1)
Lower function in Excel

To change all letters to upper case in the same cell, use this formula:

=UPPER(A1)
Upper function in Excel

To capitalize the first letter of each word in the text string in cell A1, use this formula:

=PROPER(A1)
Proper function in Excel

You can also use a cell range for the argument in the formulas. We can use the PROPER function to change the letter case in cells A1 through A5 with this formula:

=PROPER(A1:A5)
Proper function for a cell range in Excel

5. Find and Remove Duplicates

Duplicate data is more data you may want to clean up in your Excel sheet. You could have the same customer name, email address, phone number, or something similar, where the duplicate is extra data you don’t need.

There are several ways to find and remove duplicates in Microsoft Excel, and you can check out our tutorial on this topic for the various methods. Here, we’re looking at just the simplest option, which is using Excel’s built-in Remove Duplicates feature.

Note that Excel keeps the first instance and removes the next instance.

  1. Start by selecting the cell range containing the duplicates, then head to the “Data” tab, and click “Remove Duplicates” in the Data Tools group.
Remove Duplicates on the Data tab
  1. In the pop-up window, you can narrow the data to review and remove. First, check the boxes or use “Select All” for those columns you want to review for duplicates. Next, check the box if your data contains headers. Click “OK” to continue.
Remove Duplicates setup and OK button
  1. If Excel successfully removes duplicate data, you’ll see a pop-up message letting you know the numbers of what was deleted and kept.
Remove Duplicates message
  1. Your sheet will be cleaned up with those duplicates removed.
Duplicates removed in Excel

FYI: learn how to take your data in a PDF and turn it into an Excel spreadsheet.

6. Clear the Formatting

If you have several people working on a sheet or who have copied and pasted data from another location, you may have extra formatting that you don’t need. This may include bold font, fill colors, or borders. Luckily, you don’t have to change each cell, row, or column one by one. You can clear the formatting in your sheet with a click.

Note that if you set up conditional formatting in your sheet, clearing the formatting as explained here will remove that formatting as well.

  1. Select the cell range containing the formatting you want to remove. We have bold, colored, and italics applied to text along with a fill color and borders.
Formatted cells with fonts and borders in Excel
  1. Go to the “Home” tab, open the “Clear” drop-down menu, and select “Clear Formats.”
Clear Formats in the Clear menu
  1. You’ll see all formatting in those cells vanish and will have a clean slate.
Cleared formats in Excel

7. Convert Text to Columns

When you pull in data from another source, it’s not always laid out the way you want it. You may have rows with data that needs to be placed into separate cells in columns. You can convert this text to columns in just a few steps so that it’s easier to manipulate.

  1. Select the cells you want to convert, head to the “Data” tab, and choose “Text to Columns” in the Data Tools group.
Text To Columns on the Data tab
  1. When the Convert Text to Column Wizard opens, walk through a few steps to convert your data based on its current state and how you want to display it. Start by choosing “Delimited” or “Fixed width.” Excel provides you with a recommendation based on your data, but you can choose whichever option is most applicable. Click “Next.”
Text To Columns Wizard step 1
  1. Depending on which option you choose in the first step, you’ll see the corresponding second step. For example, if you choose Delimited data, you can choose the delimiter, and if you have Fixed data, you can adjust the column widths. Click “Next.”
Text To Columns Wizard step 2
  1. Choose the data format for the column, like text or date, and enter the destination for the converted data. Click “Finish.”
Text To Columns Wizard step 3
  1. You’ll see your text converted to columns, ready for you to work with.
Converted text to columns in Excel

Good to know: learn which one to use: Grammarly or Microsoft Editor.

8. Highlight Errors

While Excel does a good job of pointing out errors in cells, like those from formulas, you may not notice these errors if you have a lengthy spreadsheet. With conditional formatting, you can highlight the errors so that you are more apt to see them and correct them.

  1. Select the entire sheet by clicking the “Select All” (triangle) button on the top left of the sheet. If you prefer to only check certain cells, select those instead.
Select All button in Excel
  1. Go to the “Home” tab, open the “Conditional Formatting” drop-down menu in the Styles group, and choose “New Rule.”
New Rule in the Conditional Formatting menu
  1. When the New Formatting Rule window options, select “Format only cells that contain” at the top and “Errors” in the drop-down list at the bottom.
New Rule setup for cells containing errors
  1. Click the “Format” button to choose the formatting you want to apply. You can do things like fill the cells with a color, make the text bold, or add a dark border. Select “OK” after you set up the formatting.
Formatting options for a new rule in Excel
  1. You’ll see a preview of your selection at the bottom of the New Formatting Rule window. Click “OK” to save and apply the rule.
New Rule preview in Excel
  1. When you view your sheet or selected cells, you’ll see those errors pop up. Once you correct an error, the formatting vanishes, and you can move on to the next one.
Highlighted errors in Excel

Frequently Asked Questions

How do I check for spelling errors in Excel?

Like the other Microsoft Office applications, you can use a spell check in Excel, too. This is handy for finding common typos or spelling errors.

Go to the “Review” tab and select “Spelling” in the Proofing section of the ribbon. When the spellcheck box appears, you’ll see the words in your spreadsheet that are not in the dictionary. You can ignore the spelling, add the word to the dictionary, change it manually, or use one of the suggestions.

How do I know how to fix an Excel error?

If you see a particular error in your sheet, select the cell and go to the “Formulas” tab. Click “Error Checking” in the Formula Auditing group. You’ll see the Error Checking window appear with brief details on the error. You can choose to get help with the error from Microsoft on the Web, show the calculation steps, ignore the error, or edit the formula.

Additionally, you can visit the How to Avoid Broken Formulas page on the Microsoft Support site for a list of common formula errors with descriptions.

How do I convert rows to columns or vice versa in Excel?

Similar to the Text to Columns feature discussed here, you can convert data from a row to a column or column to row in Excel.

The simplest way uses copy and paste with the Transpose feature. Alternatively, you can use the TRANSPOSE function. Take a look at our full tutorial on transposing data in Excel, which explains both methods.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Is this post useful?
Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sandy Writtenhouse
Sandy Writtenhouse - Staff Writer

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.