Searching... Searching...

Total Subscribers Receiving Our Weekly Excel Lessons

+55,000

CLICK TO RECEIVE OUR FREE EXCEL LESSONS

Top Excel Data Cleansing Techniques

Email this to someoneShare on FacebookTweet about this on TwitterShare on LinkedInShare on Google+
Top Excel Data cleansing

Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it.

So what is data cleansing?

In the words of Oz du Soleil, Excel MVP, it is like “Hercules being sent out to capture the three-headed dog that guards the entrance to hell!”

So you can see that Oz sees that data cleansing is hard work but one that needs to be done in order to succeed in Excel.

We get thrown data at us from external accounting systems, ERPs or downloads from the company server and most of the time it is not imported in an Excel friendly way.  This is where you and I come in to clean the data!
​You can listen to our podcast episode which was highly entertaining and full of golden nuggets by clicking on below’s image and further down I will show you the My Top Data Cleansing Techniques, which include the ones that Oz uses:

Top Excel Data cleansing

1. Unpivot Data

Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013 here:

Download Power Query here 

How to Install Power Query 2010 here

How to Install Power Query 2013 here

In Excel 2016 it comes built in the Ribbon menu under the Data tab and within the Get & Transform group.

Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within Excel, Power Pivot or the Power BI Designer canvas.

One of the best features is to Unpivot Columns.

What that does is transforms columns with similar characteristics (e.g. Jan, Feb, March…) and puts them in a unique column or tabular format (e.g. Month), which then allows you to do further analysis using Pivot Tables which was not possible before unpivoting.

Here is how this is done:

Top Excel Data cleansing

2. Find & Replace

The Find & Replace feature or CTRL+H shortcut allows you to amend your data in seconds.  Imagine you had thousands of rows of data that was downloaded from an external system with the wrong date.  A simple CTRL+H will save you heaps of time!  See how below.

Top Excel Data cleansing

3. Find Errors with Go to Special Constants

Say you have a data set and want to make sure that each column contains what it is supposed to.

For example, say you have a column which contains Dates and you want to check that there are no cells which contain Text.

You can easily check this by highlighting that column and pressing CTRL+G to bring up the Go To dialogue box (or by choosing from the menu Home > Find & Select > Go To…)

Then you need to choose Special > Constants and select the constant that you want to find in your column.

In our example you will need to only select the Text box and de-select the other boxes and press OK.  This will highlight the cells that contain text and you can begin to format these cells.

Top Excel Data cleansing

4. Find Blank Cells In Excel With A Color

In Excel you can have a data set that comes from an external source which isn’t always formatted to your liking.

One of the most common things you may encounter are blank cells in your Excel data which can hinder your analysis, especially if you are using a Pivot Table to analyze the data.

To find these annoying blank cells in Excel you will need to highlight all your data set (CTRL+*) and bring up the Go To shortcut:  CTRL+G > Special > Blanks.

Then you can fill in the blank cells with a color red and filter by that same color to drill down to these blank cells and take some formatting action.

Top Excel Data cleansing

 

Top Excel Data cleansing

5. Remove Duplicates in an Excel Table

When you have duplicates values within your Excel Table there is a quick and easy way to remove those values.

The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them.

Not to worry, Remove Duplicates to the rescue!

STEP 1: Click inside your Excel Table and select Table Tools > Design > Remove Duplicates

STEP 2: This will bring up the Remove Duplicates dialogue box.  Select only the Column box that contains the duplicates that you want to remove and press OK

Top Excel Data cleansing

6. Text To Columns: Dates

Whenever you download data from an external ERP system like Oracle, SAP, etc, you can have data that is not formatted the way you and Excel likes.

Sometimes “Date” values are downloaded as “Text”, so you cannot sort in the periodic date format.

No worries!  Text to Columns to the rescue!

Below I show you how to convert this “Text” data into “Excel friendly” data.  Download the workbook to practice this cool trick!

NB: If the dates are not converting properly after you do the Text to Columns, it may be that your computer’s Region formats need changing.

Go to the Region settings in your Control Panel and make sure that the Short Date is in this format: dd/MM/yyyy

Top Excel Data cleansing

7. Using Formulas To Clean Data

There are several formulas that you can use to clean up dirty, inconsistent and wayward data.  Below are some of the best formulas that you can use:

Download Workbook

Top Excel Data cleansing

Top Excel Data cleansing

Top Excel Data cleansing

Top Excel Data cleansing

Top Excel Data cleansing

Top Excel Data cleansing

8. Excel Add-Ins

Here are some great Excel Add-Ins that will help you clean up your messed up data with a press of a button, so no need to write any formulas:

Power Query for Excel

Ablebits

Asap-Utilities

So there you are, these are just a few data cleansing techniques that you can apply to your data today.  Join me and Oz in this Free Power Query & Data Cleansing Webinar to learn some more techniques now:
Top Excel Data cleansing
What is your favorite data cleansing technique?  Let me know by commenting below 🙂
Cheers,
John

Related Posts:

Convert Values to Dates Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column....
Fix Incorrect Formatting Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.  One of the cool uses of Flash Fill is to fix incorrect formatting in your text automatically.Ever had the scenari...
Add Hyphens To Serial Numbers Using Excel Flash Fi...  Flash Fill in Excel is a new feature that was introduced in Excel 2013.Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column....
Extract Numbers Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.  Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column. ...
Searching... Searching...

DO YOU WANT TO GET BETTER AT EXCEL?
If so, join over 45,000 professionals who get career boosting, Free Excel lessons delivered on a weekly basis!

Click here to subscribe

Leave a Comment

Your email address will never be published. Required fields are marked with *

Comments

  • Robert Wallis September 9, 2016, 1:56 AM

    Hi John, I’ve found a fast way to remove blank rows is with an add-in like QuikBots for Excel. It has a lot of useful features that make tasks like this go fast.

    Reply
    • John Michaloudis John Michaloudis September 13, 2016, 11:33 AM

      Hey Robert,

      Thanks for sharing this addin.

      Cheers,
      John

  • Zaigham February 20, 2016, 11:54 AM

    Amazing and easy to learn. Great 🙂

    Reply
    • John Michaloudis John Michaloudis February 23, 2016, 7:11 PM

      I am glad you enjoyed these tricks Zaigham 🙂
      Cheers,
      John

  • Doug Glancy February 18, 2016, 9:32 PM

    I posted a VBA routine for unpivoting/normalizing data a few years ago. I’ve been told it’s a lot faster than the Power Query method: http://yoursumbuddy.com/data-normalizer/

    Reply
    • John Michaloudis John Michaloudis February 23, 2016, 7:13 PM

      Great VBA tip Doug, thanks for sharing buddy!
      Cheers,
      John