8 Ways to Clean Up Data in Microsoft Excel

by
Spread the love

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.

Content

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)

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   ”)

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.”

  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.

  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.

  1. Select “Replace All” to remove the extra spaces, and you’ll see your sheet update immediately.

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.

Use this formula to remove them:

=CLEAN(A1)

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))

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.”

  1. In the window that appears, select “Blanks,” and click “OK.”

  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.”

  1. You will see the blank rows vanish and the remaining rows in your sheet shift upward.

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)

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

=UPPER(A1)

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

=PROPER(A1)

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)

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.

  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.

  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.

  1. Your sheet will be cleaned up with those duplicates removed.

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.

  1. Go to the “Home” tab, open the “Clear” drop-down menu, and select “Clear Formats.”

  1. You’ll see all formatting in those cells vanish and will have a clean slate.

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.

  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.”

  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.”

  1. Choose the data format for the column, like text or date, and enter the destination for the converted data. Click “Finish.”

  1. You’ll see your text converted to columns, ready for you to work with.

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.

  1. Go to the “Home” tab, open the “Conditional Formatting” drop-down menu in the Styles group, and choose “New Rule.”

  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.

  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.

  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.

  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.

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.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Sandy Writtenhouse

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.

Comments are closed