10 Useful Techniques For Data Cleaning In Excel Quickly
Want to know how to do data cleaning in excel? Learn top 10 data cleaning techniques in excel, shared by our data processing team.
- Remove unwanted spaces between number or words in Cell
- Based On A Range of Cell Select All Blank Cells
- Quick Fix For Numeric Digit Stored as Text
- Removing Duplicates Data In Excel
- Highlight Errors On Excel Based On Condition
- Change Text to Lower / Upper / Proper Case
- Text to Column Data Parse Using Excel
- Learn how To Do Spell Check In Excel
- Clean Data in Excel By Deleting all Formatting
- Use Find and Replace to Clean Data Using Excel
1. Remove unwanted spaces between number or words in Cell
This is the most common issue faced by our expert while doing data cleaning in excel. Let’s learn the first data cleaning technique. For example there have some blank space anywhere in cell. And it’s looking something like this. Space could be in front, end even middle of two words.
And are you still removing those blank spaces by manually pressing delete button. Here I have two way for you to make this instantly. None of them are either paid or technical. The mostly used one is the Trim Function. Microsoft Excel does have a have the function designed to remove addition blank space expect for only one single space between words. So let’s learn how to do that.
Use TRIM For Data Cleaning in Excel
- Let’s continue with the above example, so you have additional space in Cell A, and suppose you are looking to get the cleaned data in next cell. Which is Cell B in this scenario. So all you have to do is to add the formula
=TRIM(A1)on the next cell.
- Next to implement this function for multiple cell at once. You don’t have to write the function on each and every cell, you just have to select the cell where function already added. As per this example this is Cell#B. So select Cell#B and drag the fill handle down upto the range you need. Bellow is an example what you can expect after using this function.
Use VBA Technique for On The Go Data Cleaning in Excel
If you have experience to use VBA then you can go for this method. Benefit of using VBA is you can save this as a macro and whenever you need this function for a range, you just have to call this macro (That also can be done using a custom key combination shortcut. Let’s see how this can be done.
- Microsoft Excel have an add-on application called Microsoft Visual Basic for Application, we have to open this for creating a new VBA script. Tt can be open by holding down Alt + F11 Key combination.
- To create a new module you have have to Click on Insert > Module and copy paste the bellow mentioned set of script.
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "AskDataEntry"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.LTrim(Rng.Value)
- Press F5 key from keyboard to run this script or module. It will pop-up AskDataEntry Dialog Box, then you have to select a range of cells with blank space. After that click on Ok Button, it will fix all of the blank space issue on the same range. See the bellow screenshot for example.
2. Based On A Range of Cell Select All Blank Cells
Does your excel database contain blank or empty cell? And you are filling those blank cell manually? Checkout this tricks to select all blank cell at a time, and you can fill all of those blank cell with ‘0’ or ‘Not Available’ or just to highlight with a colour background.
- Either you can select any specific data range in excel or You can select the entire worksheet based on your requirement)
- Now open the Go To dialog box, press F5 to open this.
- See the bottom left side on the dialog box click on ‘Special‘ button. It will take you to Go To Special dialog box.
- Choose ‘Blanks‘ and click on OK.
- After completion Step#4, all of the blank cell will be in select mode. Now if you want to add any specific text or numeric number on all of the selected cell, just type it and press Ctrl + Enter Key (Make sure to hold down Ctrl while pressing Enter, because if you just press Enter, the value is inserted on the selected cell only.)
3. Quick Fix For Numeric Digit Stored as Text
Calculation related functions not working for a specific excel database? This is very common issue when you have import data set from any other applications or you are using database developed in other platform. Nothing to worry, this is happening because all of numeric data stored as text format. Even some people have habit to add ‘ before a numeric value. Here we are going to elaborate two possible way to solve this issue.
METHOD – 1
Fixing Test To Number Using Excel Green Triangle
If you have number stored as text in any cell then MS Excel allow you to convert this cell using couple of clicks. First of all you have to make sure this option is turned on. Please make sure whether File > Options > Formulas > Error Checking > Enable Background Error Checking is enabled.
All you have to do is select the cell which you want to convert from text to number. You will get a warning sign on the cell, click on it. You will get option like ‘Convert to Number‘. Click on it, This method is good got couple of cell like this here and there, but if you have such issue for thousands on cells then you should follow the next method.
METHOD – 2
Using Paste Special For Excel Data Cleaning
- Just input 1 on any blank cell on the worksheet.
- To copy the cell, select the cell where you write 1 and press Ctrl + C.
- Then you have to select the cell or range of cell to convert.
- Then you have to use Paste Special option (Shortcut Alt + E + S).
- Select Multiple on the Paste Special Dialog box.
- Click OK. (Now you have the converted text to number data)
4. Removing Duplicates Data In Excel
While we are doing data cleaning in Excel then the simplest but effective techniques is removing duplicated data automatically. But based on requirement we might have to just highlight the duplicate entries. Both of the things can be done.
How to Highlight Duplicate Data In Excel
- As usual you have select the range of data to check duplicate.
- Navigate to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Value.
- Here you can specify how you want the duplicate data to be highlighted, by giving a background colour or a text formatting etc.
Learn To Delete Duplicate Data In Excel
- As like the previous step you have select the data range.
- Then navigate to Data > Remove Duplicates.
- On the next popup ‘Remove Duplicates’ you have to choose ‘My data has headers‘ in case you have columns header available. See the example bellow.
- Now select the Column(s) checkbox from which you want to remove duplicates values.
- Step 5 : Finally click OK. It will remove duplicate values from the list. This is the most useful technique for data cleaning in excel while you have multiple duplicate entries.
5. Highlight Errors On Excel Based On Condition
On the others methods too we have use conditional formatting, even for this case we have to use conditional formatting to format cell or text based on condition.
- Select a specific range of data or the entire sheet.
- Navigate to Home > Conditional Formatting > New Rule
- Now choose ‘Format Only Cells that Contain‘ in the dialog box.
- Select ‘Errors‘ from the dropdown in the ‘Format only cells with‘.
- Now all you can set the format by clicking on the Format button.
- Finally click OK. Now any error on the sheet will be highlighted.
6. Change Text to Lower / Upper / Proper Case
MS Excel don’t have any menu option to change text case on a click, but changing text from lower, upper or proper case can be done using simple function.
- To convert text from to lower case the formula is
- Respectively converting to upper case use
- And use
=PROPER(CELL#)to change text to proper case. Here CELL# referred to the cell you want to use for source text.
See the example bellow for all of the 3 combination to change case in a go. Learn more about Change the capitalization or case of text from Microsoft.
7. Text to Column Data Parse Using Excel
Are you dealing with a data set where first name and last name written in a single cell eg. ‘FirstName LastName’ and now you need to split first and last name on two separate column. Excel have a great tool to parse those text.
- First select the column which you want to parse. For example you have name in Cell #A so choose Cell #A.
- Navigate to Data > Text To Columns.
- You will get a wizard box called Convert Text To Column Wizard.
- Now you have to choose proper delimited radio button to separate the text, as per our example above we have a space between first and last name. So let’s choose ‘Space‘ checkbox.
- Similarly you can use Tab, Semicolon, Comma, even it have a input box called others. Suppose you have text like Name#Sex, so to separate name and sex there have a # delimiter. For that case we can use # in the other input box.
- Now click Next (To read the next wizard screen) otherwise click Finish to button. Excel will produce those labels into separate columns.
8. How To Do Spell Check In Excel
Your final dataset should be spelling and grammatically correct. To run this tool is Excel just press F7 key. It will give a popup for spelling tool. Check the suggestion box, you have to choose the suggested word and click on Change button. Sometime this tool can show you mistake which is not a mistake actually so as per situation you can use any of the option mention bellow :
- Ignore Once – To skip or ignore the current selected mistake.
- Ignore All – Use this option to ignore all of the mistakes.
- Add to Dictionary – Use this option to add that term on your dictionary, so it won’t prompt at error.
- Change All – You can go for this option if you want to change all of spelling errors with suggestions.
- AutoCorrect – It will correct mistake as it sees fit.
- Cancel – Use this button to stop the correction wizard.
9. Clean Data in Excel By Deleting all Formatting
Have you receive some excel workbook with lot’s of formating in it, which make it unusable? (For example : multiple font type and size, font colour, border etc) We are going to learn here some excel tricks to remove all excel formatting.
- First of all we have select the area where we are suppose to remove formatting, if you want to select the entire sheet then press Ctrl + A or you can select a specific range of call using mouse.
- Navigate to ‘Editing Group‘ and click on clear drop down button.
- From the drop down menu choose ‘Clear Format‘ option. As of now all of the formatting will be removed from the selected range.
10. Use Find and Replace to Clean Data Using Excel
This is the most useful way to clean data in excel, using Find and Replace you can change reference in formulas, search and update formatting, interchange text or numbers etc. All you have to do is navigate to Home > Find and Select > Replace (You can also use keyboard shortcut – Ctrl + H.)
The find and replace popup have two important input box which is ‘Find what‘ > ‘Replace with‘. All you have to do you have to provide what you want to search for in ‘Find what‘. And the new data you want to replace with at ‘Replace with‘. It can be a cell reference like find for
$A$1 and replace with
$B$1. And if you want to search based on some formatting then click on ‘Options’.
Do you use some others method which are not available on the content, let us know about it on the comment section. This content is suggested by the executives of data cleansing services team from AskDataEntry.