Excel data cleansing process

The typical issues experienced when trying to scrub or clean data can be categorised into a number of areas (we cover each in a separate module). By working through the raw data you should be able to spot which of these apply (generally more than one will apply) and then use the correct tool or formula to clean your data.

Don’t be scared

Instead of spending hours manually deleting, copying and pasting, you should be able to clean any messy dataset (like the one below) in a few clicks.

excel-data-cleaning-fundamentals

The key is not to be scared. You should approach it like you would the task of eating an elephant. One bite at a time.

See which of the above issues you want to address. Then try out some of the solutions we provide. Because you are only working on a copy (very important) it doesn’t matter what you do, but it is better to try something than to just stare at the computer.

We typically try out the tools first (Filter, GoTo Special etc) until we understand what will and won’t work.

Like it or not, you sometimes need to restart the process a few times (hence the copy rule). This is because occasionally the order of cleanup may be important. Do you delete the subtotals first or do you fill in the gaps? It depends, but the only way you will figure it out is if you try.

Back to: Get & Transform- Excel’s data cleanup tool (aka PowerQuery) > Data Cleanup Fundamentals