Select Page

## 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...

## 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...

## 16 – Conditional formatting in Excel

So for this I am going to walk you through one of the exercises I do with my learners when I am teaching Conditional formatting - particularly using the Highlight Cells Rules options. This is a file with the exercises in it. Here are the questions The number of...

## 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...

## 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...

## 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...

## 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) use group entry...

## 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...

## 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...

## Learning Excel and the plane of all possibilities to know what to ask

This year I attended a conference about John O Donohue , an Irish philosopher/mystic/poet who died in his early 50s. At that conference a friend of his called Dr Dan Siegel talked about how quantum physics and mysticism were now converging and  discovering what they...

## 5ks, cows and Claire Underwood

Last July I decided to start running. I was inspired by the site of women running in the morning as I drove to work. They always seemed the epitome of grace and power to me. However I never saw myself as a runner. I was the girl who hid in the toilets at school when...