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

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

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

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

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