Much of the sales data you import is not in the format you want it. In fact, some of it isn’t even usable at first.
The most illegible gibberish is rife with misspelled words, improper cases, non-printing characters, and unwanted trailing spaces. Before you even begin to work with such data, you first have to give it a good clean.
Luckily, Excel contains many features to help you get your new data into the format you want it. While some of these tasks can be performed with the quick click of a button, other, more complex tasks require you to manipulate one or more columns with a formula to create usable values. Here, I’ve listed seven of these data cleansing functions in order of complexity, starting with the easiest to use.
1) Fix spelling errors
Nothing leaves a worse first impression than a spelling mistake, but Spell Check can ensure that your reputation stays unmarred by typos. Look for this function under the Tools dropdown.
2) Delete all formatting
The chances of you importing data that’s already in the format you want is pretty low. Once you have imported data from an external source, you’ll have to clear the formatting before you can use it. To do this, simply select the data set, click on the ‘Clear’ dropdown, and then select ‘Clear Formats’.
3) Remove extra spaces
Most extra spaces are difficult to spot, but trailing spaces are not even visible. The TRIM function gives you an alternative to the painfully slow hunt-and-peck approach.
Syntax: =TRIM(text)
4) Select and treat all blank cells
Blank cells can cause much confusion, but you can fill them in with a ‘0’ or ‘Not Available’ by following these steps:
- Select data set
- Press F5 for the dialogue box
- Click on ‘Special…’ button
- Select ‘Blanks’
- Click ‘OK’
5) Use Find and Replace to select data
This function is very useful for finding and changing specific values and formulas. To learn how to add and remove line breaks, replace formatting, and change cell references with this time saver, I suggest you read this blog on data cleansing by trumpexcel.com.
6) Highlight errors
To find errors in your data, you can use the dialogue box and the following steps:
- Select data set
- Press F5 for the dialogue box
- Click on ‘Special…’ button
- Select ‘Formulas’
- Uncheck all options except ‘Errors’
- Click ‘OK’
7) Remove duplicates
Duplicate entries can be a real problem, especially if you regularly combine data from multiple records. The Remove Duplicates function weeds out those extra entries, but you need to know which columns to select. If you leave all columns for a data set selected, only rows that are completely identical will be affected. For a more targeted approach to data cleansing see this blog by excel-easy.com.
Ask the expert
Sometimes you just don’t have the time or resources for a large data cleansing job. In these cases, it’s best to give your data to a specialist whose expertise can ensure that you have the best data possible for your next campaign. GCL Direct has 26 years experience in data management, and can answer any questions you may have about data cleansing and management. To learn more about our services, visit our website for more information.
Image Credit: League Digital
