10 Effective Way To Clean Data On Excel

In this day and age, our dependence on data is overwhelming. Thanks to our cellphones and laptop, a halo of data surrounds our life. Data is nothing but a piece of classified information. Microsoft Excel is one of the most used data handling/analysis software. At the same time, one tiny mistake in analyzing data can cause headaches. Data is the backbone of any analysis that you do. It is an eternal problem and not only in Excel! Here’s a list of top 10 Super Neat Ways to Clean Data in Excel as follows.

1. Get Rid of Extra Spaces

When its come to clean data on excel extra spaces are painfully difficult to spot. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible. Here is a neat way to get rid of these extra spaces.

– Use TRIM Function.

Here a practical examples of using the TRIM function.

Example 1 – Remove Leading, Trailing, and Double Spaces

TRIM function is made to do this.

Below is an example where there are leading, trailing, and double spaces in the cells.

Excel TRIM Function - Data set Example 1

You can easily remove all these extra spaces by using the below TRIM function:

=TRIM(A1)

Copy-paste this into all the cells and you are all set.

2. Select & Treat all blank cells

Blank cells are troublesome because they often create errors while creating reports. And, people usually want to replace such cells with 0, Not Available or something like that. But replacing each cell manually on a large data table would take hours. Luckily, there’s an easy way to tackle this problem.


Steps:

  • Select the entire Data (you want to treat)
  • Press F5 (on keyboard)
  • A dialogue box will appear > Select “Special
  • Select “Blanks” & click “OK
  • Now, all blank cells will be highlighted in pale grey color, out of which one cell would be white with a different border. That’s the active cell, type the statement you want to replace in blank cells.
  • Hit “Ctrl+Enter

3. Convert Numbers Stored as Text into Numbers

Sometimes when you import data from text files or external databases, numbers get stored as text. Also, some people are in the habit of using an apostrophe (‘) before a number to make it text. This could create serious issues if you are using these cells in calculations. Here is a foolproof way to converts these numbers stored as text back into numbers.

Steps:

  • In any blank cell, type 1
  • Select the cell where you typed 1, and press Control + C
  • Select the cell/range which you want to convert to numbers
  • Select Paste –> Paste Special (KeyBoard Shortcut – Alt + E + S)
  • In the Paste Special Dialogue box, select Multiply (in operations category)
  • Click OK. This converts all the numbers in text format back to numbers.
Clean Data in Excel - Paste Special Multiply

4. Remove Duplicates

Elimination of duplicate data is necessary for the creation of unique data & less usage of storage. In duplication, you can either highlight it or delete it.

A) Highlight Duplicates:

  • Select the data & go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values
  • A dialogue box will appear (Duplicate Values), Select Duplicate & formatting color
  • Press OK
  • All duplicate values will be highlighted!
Clean Data in Excel - Highlight Duplicates

B) Delete Duplicates:

  • Select the data & go to DATA > Remove Duplicates
  • A dialogue box will appear (Remove Duplicates), tick columns whose duplicates need to be found.
  • Remember to have a click on “My data has headers” (if your Data has headers) or else column heads will be considered as data & a duplication search will be applied on it too.
  • Click OK!
Clean Data in Excel - Remove Duplicates select column

Duplicate values will be removed! Suppose you select 4 of 4 columns. Then that four columns rows should also match or else; they won’t be considered a duplicate.

5. Highlight Errors

There are 2 ways you can highlight Errors while clean Data on Excel:

Using Conditional Formatting

  • Select the entire data set
  • Go to Home –> Conditional Formatting –> New Rule
  • In New Formatting Rule Dialogue Box select ‘Format Only Cells that Contain’
  • In the Rule Description, select Errors from the drop down
  • Set the format and click OK. This highlights any error value in the selected dataset
Clean Data in Excel - Highlight Errors

Using Go To Special

  • Select the entire data set
  • Press F5 (this opens the Go To Dialogue box)
  • Click on Special Button at the bottom left
  • Select Formulas and uncheck all options except Errors

This selects all the cells that have an error in it. Now you can manually highlight these, delete it, or type anything into it.

Clean Data in Excel - Select Errors

6. Change Text to Lower/Upper/Proper Case

While importing data, we often find names in irregular forms like a lower, upper case, or sometimes mixed. Such errors are not easy to eliminate manually. Here’s a fingertip trick to bring back the consistency.

  • LOWER(text)
  • UPPER(text)
  • PROPER(text)

Steps:

  • Just type the formula you want to use, suppose “LOWER(“ and select the cell whose case needs to be changed.
  • Hit “CTRL+ENTER.”
  • The case has been changed & consistent
  • Drag down to do the same for other cells.
  • Similarly for UPPER() & PROPER()

7. Parse Data Using Text to Column

Sometimes the received Data has texts filled in one cell, only separated by punctuations. Usually, the addresses are cramped in one cell separated by a comma. To distinguish values in separate cells, we can use “Text to Column.”

Steps:

  • Select the Data
  • Go to Data> Text to Column
  • A dialogue box will appear (Convert Text to Columns Wizard – Step 1 of 3), select Delimited or Fixed Width as per your convenience.
  • Delimited is to be selected if the width isn’t fixed, click “NEXT
  • In Delimiters tick the option which separates your text in the cell. Suppose “Norwich Cathedral, Norwich, UK,” here three values are separated by commas. So we will select “Comma” for this example. And, deselect rest options.
  • View the preview & click on “NEXT
  • Select Column Data Format & destination cell address
  • Click “FINIS

8. Spell Check

Spelling mistakes are common in text files & PowerPoint. However, MS points out such errors by underlining it with colorful dashes. And, MS Excel doesn’t have such feature. But you can use it below steps to clean data on excel.

  • Select the Data
  • Press “F7
  • A dialogue box appears, which shows you the possible wrong word & it’s the possible correct spelling. Click on “Change,” if you agree with the suggestion.
  • Check & change till it says “Spell check complete. You’re good to go!

9. Delete all Formatting

In my job, I used multiple databases to get the data in excel. Every database had it’s own data formatting. When you have all the data in place, here is how you can delete all the formatting at one go:

  1. Select the data set
  2. Go to Home –> Clear –> Clear Formats

Similarly, you can clear Content, Comments, Hyperlink, or entire data (using Clear All).

Clean Data in Excel - Clear Formats

10. Use Find & Replace to Clean Data in Excel

A) Changing Cell References:

  • Press “CTRL+H” to open “Find and Replace
  • Now in Replace > “Find What” (change the reference range too) “Replace With
  • Suppose Find What: $B to Replace With: $C
  • Click on “Replace All
  • Similarly finding & replacing using reference range we can clean the Data

B) Find & Change Specific Format:

  • Press “CTRL+H
  • Select “Options
  • Now go to “Format” of “Find What.” Here you can specify the format or choose a format from the cell. Suppose you select a format.
  • Now it will show you the preview for “Find What.”
  • Click on “Format” of “Replace With.” Suppose we go for “Format…”
  • Now select format, example: Number, Alignment, Font, Border, Fill, Protection.
  • Suppose we select Color then select any color to fill the column header cell.
  • Click on Replace All
  • Instantly the format has been changed!

C) Removal of Line Breaks:

Suppose we have a data where it is separated by line breaks (same cell but different rows). To remove these line breaks, follow the below steps:

  • Press “CTRL+H
  • Find and Replace dialogue box will appear, press “CTRL+J
  • Go to the replace with box & type a single space
  • Click Replace All
  • All rows will be managed in one row within the same cell!

D) Removal of Parenthesis:

  • Select the Data
  • Press “CTRL+H
  • Type (*) in “Find What” (This will consider all characters within parenthesis)
  • Leave the Replace With column empty & click Replace
  • Parenthesis characters are removed!

Now we are on Patreon: https://www.patreon.com/pyoflife

Spread the love

4 thoughts on “10 Effective Way To Clean Data On Excel”

Leave a Comment