<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=159779227907527&amp;ev=PageView&amp;noscript=1">

Welcome To Our Blog

Seven functions that make Excel a great data cleansing tool

Jenny Reardon | 6 July 2016

7 functions that make Excel a great data cleansing tool
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:

  1. Select data set
  2. Press F5 for the dialogue box
  3. Click on ‘Special…’ button
  4. Select ‘Blanks’
  5. 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:

  1. Select data set
  2. Press F5 for the dialogue box
  3. Click on ‘Special…’ button
  4. Select ‘Formulas’
  5. Uncheck all options except ‘Errors’
  6. 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.

New Call-to-action
Image Credit: League Digital