5 Quick Ways To Clean Data In Excel Spreadsheets

Summary:

Want to remove unwanted or unnecessary data from your Excel workbook but by using simple and easy tricks.

This post will help you to know some best tricks to clean data in Excel spreadsheet. So let's check this out.

#1 Get Rid Of Extra Spaces

Extra spaces are very difficult to fetch as it is a bit easy to find the extra spaces between numbers and words. But when it comes to trailing spaces you can’t even see it.

To handle such situations easily and effortlessly, use the TRIM function of Excel.

Here is the Syntax: TRIM(text)

Excel TRIM function, as an input actually considers the cell reference or text. It deletes the all the trailing and leading spaces along with the spaces present between the words.

2# Select and Treat All Blank Cells

Blank cells can be big disaster if it’s not removed time to time. Mostly this kind of issue arises in the data set which is used for creating dashboards/reports.

If you want to fill the entire blanks with ‘Not Available’ or ‘0’ or in case you need wants to apply a different color to it.

If you have a huge data set, then doing this one by one will consume very much time.

Here is the simple solution to figure out these blank cells at once.

1. Choose the entire data set.
2. Hit the F5 from your keyboard, this will opens the Go To dialogue box.
3. Hit the Special… button which is present at the bottom left. After this, you will see the opened dialogue box of “Go To Special”.
4. Choose Blank and then hit the OK button.

This process will select entire blank cells from the data set. If you need to put Not Available or 0 in all the blank cells. Type this text and hit the Control + Enter.

Note:  if you only press the enter button then the assigned value is only kept in the active cell.

You may also like: how to recover corrupted Excel file

3# Convert Numbers Stored as Text into Numbers

When any data is been imported from an external database or text file, the number will get saved in the text format. Most of us have the habit of giving apostrophe (‘) just before the number to make it as a text.

For the calculation purpose if you are using this cell then this will create serious issues. Here is a guaranteed solution to convert the numbers which are stored as text back to number format.

1. Select one blank cell, and type 1 in it.
2. Keep choosing this cell you have to press the Ctrl + C button from your keyboard.
3. Choose the cell/range that you are willing to convert it not numbers.
4. Follow the path: Paste –> Paste Special (KeyBoard Shortcut – Alt + E + S)
5. From the dialogue box of Paste Special, choose Multiply
6. Hit the ok After this you will see all the text format numbers back to number format.

4#  Remove Duplicates

You can perform two things with duplicate data 1^st is Highlight It and 2^nd is Delete It.

• Highlight the Duplicate Data:

• Choose your data set in which you want to highlight duplicate data. Follow this path: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
• You will see all the duplicate values is been highlighted.

Delete Duplicates in Data: 

• Choose the data set and follow this path Data –> Remove Duplicates.
• In your data, if headers are present then make sure to select the checkbox which is present at the top right corner.
• Choose the Columns from which you have to delete the duplicate ones.

This will remove all the duplicate values from the list. If you want the original list intact, copy-paste the data at some other location and then do this.

5# Highlight Errors

Mainly there are two methods to highlight errors in Excel data.

Using Conditional Formatting

1. Choose entire Excel data set
2. Follow this path: Home –> Conditional Formatting –> New Rule
3. From the opened dialog box of “New Formatting Rule” choose the option “Format Only Cells that Contain”
4. In the section of edit the Rule Description, select the Errors option
5. Hit the format and then the ok

This will highlight all the error values present within the specified dataset.

Using Go To Special

1. Choose the complete data set
2. From your keyboard hit the F5 button. This will open the Go To Dialogue box.
3. Hit the Special button from the bottom left section.
4. Choose Formulas but unselect the errors.

This will choose entire cells having the error. Now it’s up to you whether you want to highlight these cells or delete them. 

For more tips & tricks like this, you can check my post on clean data in Excel.