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.

Many business owners have fallen victim of poor service quality from several self-acclaimed data entry experts. Which has led to loss of their business funds and even affected their business negatively. How then do new businesses that are just migrating to the data entry scene navigate this turbulence?

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.

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.

These and many more would determine their suitability for the role and even the quality of service they would render. Remember that the goal is to hire the best hands without compromising quality. Very tricky but worthy pick point when you are about to hire someone for data entry.

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.

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.

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.

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

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

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

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.

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

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

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.

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

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 =LOWER(CEEL#)

• Respectively converting to upper case use =UPPER(CELL#)

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

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.

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

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.

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.

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.

Author
JOYDEEP

Joydeep Singha is the Founder at AskDataEntry – India’s leading online virtual administration services provider for businesses and individuals. He is a seasoned marketing professional who is an expert in digital marketing and entrepreneurship.