Cleaning up an accounts file with Power Query

One of the most common tasks faced by accountants and office workers is that of data cleansing i.e. removing the extra blank columns , sub totals, blank rows so beloved of accounts packages but which all need to be removed before any sort of Excel analysis can be done...

Faster Filtering with Filter()

Filter() is one of the new dynamic array functions in Excel. So what does that mean? If you have used Excel you will know that generally it has made like a single celled organism – create your formula in ONE cell and then copy it down or across. The new range of...

How to create a unique ID in Excel

A while back one of my customers asked me about a way to create a unique ID from a customer’s name and a random number. Of course I was intrigued and using a combination of Left(), Right() and Randbetween() helped her do just that. Yum, I do love me an Excel...

How to quickly get your PDF data into Excel

A question that comes up from time to time is how to get your PDF data into Excel. There are of course many online options to do so – just do a quick Google for “convert PDF to Excel” . However in this tutorial and video I want to show you how you...

Make your Vlookup() more flexible with Match()

Ah, the Vlookup() – the staple of data linking since Excel was a child – 40 years ago…I’ve done a tutorial on it here but I want to explore how to make it more flexible . Let’s say you have a scenario where you need to pull in data and...