Data Cleaning Techniques and Why Your Organization Needs Them

The analogy of data as the new oil is troublesome. It has driven some polarizing opinion pieces in journals the world over, but in some ways, the comparison holds. Like oil, data is precious to organizations and enterprises, but of little use to anyone until it has been refined.

Worse though, dirty data harms its users, of which your organization is one. That's why you're doing your stakeholders a disservice if you ignore the need to eradicate dirty data and ensure that yours is as clean as it possibly can be.

But how do you make sure your data is clean, or, more to the point, how do you get it cleaned up in the first place?

If that’s the question forming in your mind right now, you’ll be glad to know that there are techniques aplenty to turn dangerously dirty data into a much more beneficial resource, and you’ll find some of them explained in the text that follows.

Data cleaning is not one of those tasks for which there is any right time. Quite simply, there is no better time than the present. There is a high chance that it will need plenty of work to clean it up because, without attention, data quality only deteriorates over time. It won’t get better on its own.

Moreover, the application of data cleaning techniques should not be a one-time exercise. It bears repeating—left unattended, data will get dirty as time goes on.

Once you have undertaken an initial project to get your data clean, regular revisits will save you from much more expensive cleaning projects in the future. They will also be faster, less costly, and less labor-intensive than the first-time effort.

That’s quite a list of business advantages, but if it seems improbably positive, just think about how bad data can impact decision-making, marketing, productivity, efficiency, customer experience, and operating costs.

If your business has not applied data cleaning techniques, it’s probable that duplicate, missing, invalid or inappropriate data is hampering all of the above business elements to some degree. While it would be wrong to suggest that your data will ever be spotless, its impact on business performance is proportionate to its completeness, accuracy, validity, and appropriateness.

There is such a thing as good enough, and that is a standard that can be measured. If you haven’t measured it, the chances are that, right now, it’s not good enough.

If your company is already a participant in the world of big data, the use of Microsoft Excel for cleaning purposes will be impractical. However, as already stressed, all enterprises owe it to themselves to keep data clean. For the smaller business, Excel is a readily available tool. Indeed, you probably use it extensively as of now.

When you clean data with Microsoft Excel, there are a few rules that you will need to follow. They will help you to clean your data safely and without undue risk. It’s essential to take precautions if you decide to clean your data without external help. After all, it’s all too possible to make mistakes and introduce errors into your data without realizing it—which is not ideal when the idea is to eliminate data problems.

The first thing you will do when using Excel as a data-cleaning tool, is to extract the data from its source. Once you have that data in an Excel file, apply the following three rules when performing the cleanup:

1. Copy the data into a new spreadsheet, and complete the data cleaning in the copied version. That way, if you do make any errors, you can start again by taking another copy from the original spreadsheet, which will not have been affected by any errors.
2. Before working on a column of data, copy that column into a separate worksheet. Then perform the cleaning, and copy the results back to the column in the first worksheet. That’s an extra safeguard to avoid introducing errors into the data as you clean it.
3. After all the data in the spreadsheet is clean, load the entire data set back to the source system.

When it comes to the task of cleaning your data, the fundamental techniques to know are those that highlight and identify the typical characteristics of dirty data, and those that save time and effort in the cleaning process. Here are some examples of those techniques:

Any spaces existing between characters and over and above the single space between words, can throw a spanner in the works of your business information systems. Use the “trim” function in Excel to remove all unwanted spaces.

Blank cells in your spreadsheet typically indicate missing data values. You can highlight them using Excel’s “find and replace” function. Then you need to decide what to do about them. For example,

• You may want to delete the records with missing values.
• You might prefer to impute the values using an average or a calculation.
• You could use a flag-value such as “0” or “missing” to highlight the fact that the value is blank.

For example, numbers entered as text are a common data-quality issue. Use Excel’s formatting functions to ensure all numerical data is formatted as a number. Similarly, you might wish to make sure that all text is in upper, lower, or proper case. To do this, you can use Excel’s UPPER, LOWER, and PROPER formulas.

You can use the “Remove Duplicates” function in Excel to highlight where entire records are duplicated. This is one of the few tasks you should probably complete before applying the #2 golden rule described in the previous section of this article. You can’t check entire records entered in columns and rows while cleaning column-by-column. To highlight duplicate values for removal from individual columns, you can use conditional formatting.

Other tasks that Excel can help you to complete quickly and with minimal effort include checking for spelling errors and typos and removing outlying values that might overly distort analysis results.

Outsourced data cleaning is not so much a technique as a strategy—one that saves you from being over-concerned with technique. If your enterprise operates internationally, has a wide range of IT solutions deployed, or is involved with AI or IoT technology, clean data is especially critical.

In these situations, outsourcing can be more cost-effective than trying to keep on top of regular checking and cleaning of massive volumes of data. You can leave your data science consulting partner to manage the quality of your data, and focus on its use as a valuable commercial resource.