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

Excel Path – Step by Step

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

Case Studies – How To

Put away the calendar and use a formula

In this tutorial I am going to show you how to calculate the difference in hours/days/working days between two sets of dates. Put away our calendar/s and learn how to use these formulas instead. You can download the completed file here You can view the video here...

How to analyse Case time duration in Excel

I was recently asked by a participant in a class for some help with analysing case time duration - basically what was the average time duration from the opening of a support case to it being closed off and what time band it fell into. This required the use of the ever...

Creating a calendar in Excel –

In this video I want to show you how you would set up a calendar to use in Excel. We will be using the Text() function to extract days and months and then Conditional Formatting to show up the different types of leave. You can download the completed file here. You can...

Cleaning up emails

What happens if you get a list of names and emails from a colleague/customer but they are in a format that you can't use e.g. someone has CC'd you on the email but you need to have them in proper cleaned up format... In this video I use a file from my one of my...

Excel – General

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

Personal

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

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