From spelling mistakes to preference in Microsoft Excel, mistakes and inconsistencies can cause big headaches. Luckily, there are easy ways to fix these issues.
I use Microsoft Excel 2010 for my examples, although I use Microsoft Excel 2007 for work and the process should be the same.
The data we will be using is as follows:
We want everything but “Gas” to read “Cell Phone”. “Type” is our header, and we will leave that as it is.
Using Find and Replace to Create a Uniform List
Find and Replace is one of the easier techniques to learn, and it is one that is used in many different programs.
To use it in Microsoft Excel click on the Home Tab. Click on the icon on the far right that says “Find and Select” and choose “Replace”. One can also use the shortcut key “ctrl + f” and then click on the tab “Replace”.
Enter in the text that you want to change. To the right of the “Find what” box enter “Phone” and to the right of the “Replace with” enter “Cell Phone”. Click “Replace All” and all instances of Phone change to “Cell Phone”.
You may have noticed that the first entry turned into “Cell Cell Phone”. This is not what we wanted. We need to undo our changes, and be more specific with our find and replace.
Undo the find and replace by using the shortcut key “ctrl + z”, or using the small blue arrow on the top left of Microsoft Excel.
We are going to do the find and replace again, but this time we are going to be more specific. Get into the Find and Replace box again, in the Replace tab. There is box titled “Options”. Left click once. Check the box that says “Match entire cell contents”. Click “Replace All”.
That is better. All we have left is the misspelled “Cel Fone”.
Do another find and replace, and replace the term “Cel Fone” with “Cell Phone”. Click “Replace All”.
If you find yourself correcting the same data over and over again, the next section will help streamline that process for you.
Creating a Macro to Create a Uniform List
I was once working with a large data set where certain names where almost always misspelled. To solve this problem I created a macro to do a find and replace for me. What had taken my predecessor an entire day of sorting and correcting data, took me about 5 seconds to complete this task.
I like to store macros on a Personal macro workbook. The Personal macro workbook is a workbook where you can store your macros, for your use only. It is easy to email, update and it keeps the macros separate from the data.
This is very helpful if you are working on similar data sets that live on different workbooks. How to use the Personal macro workbook is a topic for another article, and it is not a requirement for this to work.
To record a macro, click on your “Developer” tab. The developer tab may not show, and you may have to add it for your specific version of Microsoft Excel. This is usually a check box under Microsoft Excel Options.
Click “Record Macro”. For shortcut key we will choose “ctrl +k” for our shortcut key. So enter “k” to the right of the text ” ctrl +”. For this example we will leave the default “Store macro in” This workbook”. That means the macro will only for for this workbook.
Follow the instructions under “Using Find and Replace to Create a Uniform List”.
Click “Stop recording”.
Now anytime you want to do a find and replace for those entries, all you have to do it hit “ctrl + k” and all of the entries change automatically for you.
Using VLOOKUP to Create a Uniform List
VLOOKUP can be a great way to change the data and make it uniform if the data that needs to be changed changes frequently. If for example you have a high turnover of employees and you want to keep a list of employee names you want changed VLOOKUP may be for you. I go into detail on how to do a VLOOKUP in the article “Change Some Categories and Leave Others the Same in Microsoft Excel “.
Easily see if Your Data is Now Uniform
To test the data and see if it is uniform I like to use remove duplicates. Remove duplicates allows me to easily see how many unique values I have, and what unique values I have.
To remove duplicates select the entire column, click on the “Data” tab, and then click on the icon that says “Remove Duplicates”. The only items left should be “Type”, “Cell Phone” and “Gas”.
Once you have checked your data, use the shortcut “ctrl + z” to undo this change and get back to your original data set.
Remove duplicates is one of my favorite tools for finding inconsistent data.
If I have a large data set of around 1000 entries but only 100 unique names, I will do a remove duplicates to bring the total down to say 101 names. I can tell right away that one of the names is spelled wrong.
I will then either compare it to a good list of names I have, or eyeball it to see if there are any names that are similar but not the same. For example “Mike Smith” and “Michael Smith”. I will then use of the examples above to correct the inconsistency.