The Excel Expert Blog
Tracking Training Records in Excel
Colour code your training dates
This blog post shows you how to colour code your training records. It helps you identify who is current, who is due for renewal and who is overdue. It also allows for those once off courses - you know the ones that new staff have to complete but are only done once. ...
How to create a training roster in Excel .
A while back I had a message from a colleague. She had got a training contract but she wanted to track it on Excel. She needed to be able to see what days her courses were being delivered and by whom and the topic. She had made the - oh so common - mistake of setting...
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...
Using IFS to find out what is early, late and on time
Quite often I get people asking me how to do X or Y and this video addresses how Excel would be used to identify what is early, late and on time - assuming a 10 day buffer (either side). I am using this to experiment with the IFS function ( a new function in Excel...
Identifying once off, overdue, current and due for renewal training records
A follow up question I have received a lot is about what to do if you have once off records, expired records etc. So in this video I have addressed this issue. There's a quick revision of how you use the vlookup() function to calculate expiry dates but the main bit is...
Excel Path – Step by Step
How to link files and change the file
How to link external files into an Excel file and then change if required A common requirement is to pull data from one Excel file into another. You should begin by setting the scene. i.e. make sure you have all the files you need open. Let's call those source...
Some lesser known charts in Excel – Treemap and Funnel
Excel has really upped its games around charts in the last couple of editions but there are some charts that are not so well known. In this blog post I am going to introduce you to two of them: The Tree Map and the funnel. You can use this file to practise on and here...
Two ways to group your numbers in Excel
I was recently asked in a class about grouping entries together. In that case it was fish lengths but what I show here applies to a wide range of options. The first method shows you how to use the inbuilt facilities of Excel pivot tables to group numbers into...
How to use Index Match
In this tutorial what I am looking at is the alternative to Vlookup() - Index/Match which is - astonishingly enough - a combination of two functions: Index and Match. I give an explanation of Match in this tutorial as well. You can download the completed file here. ...
17 – How to use the if function in Excel
This blog post came about as the result of my own incompetence. I made a rookie error - FORGOT TO PRESS RECORD....So this is an example of an exercise I would use in class. You can download the completed file here.. and if you are feeling full of enthusiasm - you can...
Case Studies – How To
Creating a KPI Dashboard – Part One – prepping our data
I was recently working with a woman who needed to track monthly stats for appointments and attendance but also was tasked with the job of creating a dashboard to show the following: In this four part case study, I am going to walk you through the process from original...
How to quickly copy an Xlookup function – when you are using a table.
In this video I explore how to use Xlookup() with the table facility to copy an entry across. You can download the completed file here. You can view the video here.
Use Vlookup to compare two lists and identify what is in one and not in the other
In this tutorial I want to show you how to use vlookup along with iferror() and conditional formatting to quickly identify what is in one list - but not in the other.. Here is the completed file and you can view the video here
When you need to schedule follow ups using working days
This blog post came courtesy of a course participant. She was spending a lot of her (all too precious) time on calculating dates for follow ups? As it happens, Excel has a function for that....called Workday() You can download the completed file here and view the...
Identifying who has a birthday in your group..
I had a participant in my class who regularly brings groups on excursions and she thought it would be lovely - as indeed it would - to have a way to identify who has a birthday in the time period of the course. I began with a list of fake names and dates of birth from...
Excel – General
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...
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...
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...
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...
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...
Random
10 – 10 tips to help you work faster in Excel
[ordered_list style="lower-roman"] Table it! If you have large data sets, convert them to a table. This means that if you scroll down to the bottom, the headings will remain visible at the top Hide! :Hide the columns/rows you don’t need. Top row visible: Freeze the...
5 ideas to help with your Excel learning plan – “September is the new January”
September can be described as the new January and it's often a time when people decide to start something new or get stuck into something they have been thinking about learning. Dare I suggest that could be your Excel skills ?In this blog post I am offering 5 ideas...
Guest Post: “The Facebook of Business: Why Excel Isn’t Going Away Anytime Soon.” by George Mount
The Facebook of Business: Why Excel Isn’t Going Away Anytime Soon. George J. Mount blogs about Microsoft Excel, business analytics, and other tips for analysts at georgejmount.com. He is the author of the new course, “Hired with Excel: What Every Analyst Needs to...
The book is out – if you want to become an Excel hero/ine – have a look
Well, folks - the book is out.....you can view the table of contents here.. My intention for the book was to help all those people that I see in my class who feel stumped and stuck by Excel. If you can learn the skills in this book you will become the Excel ninja in...
Keyboard shortcuts | Macros AKA playing the recording rather than singing the song
As always, I hope you are well. As per my earlier blog post I am going to be offering two segments here: Excel Novice 5 min tip Todays's 5 minute tip is , this week, to learn 2 keyboard shortcuts for things you do most often. Repeat the same thing next week. In the...
Personal
Reflecting on 2022 – Failures & Disappointments, what went well and lessons learned.
Failures and disappointments.. I have tried for the last couple of years to really lose weight - I have failed dismally. After two years of trying - doing all the right things, tracking food, walking regularly - something in me snapped and I stopped exercising and did...
What I learned from the 2022 Tour de France
For the last couple of years I have gone into Low Power Mode for the Tour de France - which is three weeks in July. Essentially I don't do any training but I still respond to emails a couple of times a week etc. I watch it on TG4 where two fans basically talk and...
Naomi Osaka and why her experience has so much resonance
You have probably heard about Naomi Osaka's withdrawal from the French Open based on her refusal to do press conferences. I think it's an interesting light on the ever present dichotomy between the needs of the individual and the requirements of the organisation. I...
My 21 for 2021
I do love a good podcast and one of my absolute favourites is Gretchen Rubin's Happier. She does it with her sister - Liz Craft - who also has a great podcast called Happier in Hollywood with her writing partner Sarah Fain. One of their key ideas over the last couple...
My 20 for 2020 updated January 2021
This year I have been inspired by Gretchen Rubin and her sister Elizabeth Craft to try out a strategy called 20 for 2020. The idea behind this is simple, pick 20 things that you would like to do/work on and commit to doing them for the year. So the 20 I have picked...