Shortcuts and Templates – speed up your work
A question I get asked regularly in class is about shortcuts. If you want to up your shortcut game – I would suggest identifying the 5-10 things you do most often and learn the shortcuts for those. Alternatively identify 2 shortcuts a week and practise them until they are in muscle memory. At the end […]
Keeping your formatting consistent – use a theme
If your organisation has a house style i.e. a preferred set of fonts/colours etc, this is something you can create in Excel. If on the other hand you just like to be internally consistent with your own colours and fonts, it might be worth while to use one of Excel’s pre-set styles. So let’s have […]
How to do an Xlookup in Excel
If you have Office 365 and you need to use the Vlookup() function, you should really consider using the Xlookup function, which I like to call “the love child of vlookup, index/match and if error”. It doesn’t have the limitations of Vlookup in that your data can be organised any way you want to (although […]
How to check for duplicates across worksheets – using Power Query Append
This blog post was inspired by a recent dilemma. My wonderful VA – Zita Lewis – who helps ensure that all my course participants get what they need when they need it and quite frankly has much better attention to detail than I have 🙂 had got a list of course participants and she wanted […]
How to use removed unwanted spaces with Power Query – AKA Trim
Power Query (AKA Get and Tranform) is Excel’s amazing data cleansing tool. You can use it to remove unwanted spaces in your data but I have noticed that sometimes it doesn’t remove everything that you would expect. This video shows you a technique you can use when you find it doesn’t remove all the the […]
Meet Aggregate – the function that helps you wrangle hidden rows and subtotals in your formulas..
One issue that can come up in adding up lists with subtotals and hidden rows is that the usual sum() will exclude hidden rows from your calculations. Equally if you have subtotals – you often don’t want those included in your calculations… So Microsoft have come up with a new function – called – which […]
How to identify total of top three scores
I was recently asked in a class by a participant who needed to find a way to identify the top three performers in a group. What she wanted to see was the total of their current top three scores. In order to do that I used the Large() function. Then I used Conditional formatting to […]
Creating a KPI Dashboard – Part Five – Putting it all together in our dashboard
This is going to be a longer post. I’ve broken down the final creation into a number of pieces. In this first part – you can see how I start the dashboard by applying background colour and borders, creating a dropdown list and starting my calculations. You can view the completed file here Part One […]
Creating a KPI Dashboard – Part Four – Putting together our annual stats
You can view Part One , Part Two and Part Three of this series. In this video, we are going to look at how to summarize our year to date (YTD) numbers. We are using the Iferror() and Indirect() functions to pull in the data from the matching sheets. You can download the completed file […]
Creating a KPI Dashboard – Part Three – Preparing our monthly stats
You can view part one of this tutorial here and part two here. In this tutorial what I want to look at is how to set up formulas to show our monthly stats. We already have our sheets set up and we’ve got a table set up on each one. Next step is to (a) […]