In this day and age, our data dependence 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 the top 10 Super Neat Ways to Clean Data in Excel as follows.
1. Get Rid of Extra Spaces
When it comes 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.
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 the 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
When you want to Clean Data On Excel Sometimes 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 convert 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 the operations category)
- Click OK. This converts all the numbers in text format back to numbers.
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 & format colour
- Press OK
- All duplicate values will be highlighted!
B) Delete Duplicates:
- Select the data & go to DATA > Remove Duplicates
- A dialogue box will appear (Remove Duplicates), and tick columns whose duplicates need to be found.
- Remember to 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 to it too.
- Click OK!
Duplicate values will be removed! Suppose you select 4 of 4 columns. Then that four column 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 cleaning 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
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 them, or type anything into them.
6. Change Text to Lower/Upper/Proper Case
While importing data, we often find names in irregular forms like 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 the 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 them with colourful dashes. And, MS Excel doesn’t have such a 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 in one go:
- Select the data set
- Go to Home –> Clear –> Clear Formats
Similarly, you can clear Content, Comments, Hyperlink, or entire data (using Clear All).
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, for example, Number, Alignment, Font, Border, Fill, Protection.
- Suppose we select Color and then select any colour to fill the column header cell.
- Click on Replace All
- Instantly the format has been changed!
C) Removal of Line Breaks:
Suppose we have 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!
