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.

In this tutorial, you will learn the Top 8 Techniques on Data Cleaning in Excel!

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.

Watch Data Cleaning in Excel on YouTube and give it a thumbs-up!

YouTube video

Top Excel Data Cleansing Techniques | MyExcelOnline

Follow the tutorial on Data Cleaning in Excel and download this Excel workbook to practice along:

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.

 

YouTube video

Top Excel Data Cleansing Techniques | MyExcelOnline

Download this Excel workbook to practice along: