Data cleansing is really the rubbish collection of Excel.
It is not really anyone’s favourite job but if you want to get all those charts and pivot tables working properly – it all has to be done. It is somewhat similar to those cookery programmes where all the cooks all – miraculously – have the right ingredients and the vegetables have somehow washed and chopped themselves…who me? sarcastic…NEVER…
It’s a job that Excel users generally find tedious and repetitive but let me introduce you to Excel’s magic piece of kit – the Excel Tool That No One – except Excel people – Has Ever Heard Of …Power Query AKA Get and Transform. The beauty – or the tragedy – is that it is completely free (built into Office 365 – on the Data ribbon as I show in the video below) and has been since it became available in Excel 2010 – although you do need to download and install it.
You can also download it for Excel 2013 at the same location. This website gives you instructions on how to install these versions.
Here is the file I used and you can view the video here