The Excel Expert Blog
Tracking Training Records in Excel
How to create expiry dates in excel
A question I get asked a lot on this website is about how to create expiry dates for different courses in Excel. So here is a video that shows you how to do it. It does require that you have a list of courses with their duration in days e.g. a course...
Tracking recurring meetings in Excel – have you had your one to one meeting this month?
A while back I was working with a company and one of the issues that came up was how to track if managers had completed their monthly one to one meetings with their staff. They needed to see who had completed them and who hadn't. The issue was that while they were...
Training records management – summarizing percentage compliance
So one of the questions that comes up when I am helping customers with this is how do you calculate what percentage of your staff are compliant. In this scenario, I have set up 16 employees who each have to do a Health and Safety and Induction course. Some of them...
Tracking time-in-lieu or “how many days holidays have I got left?”
A common issue for many organisations is tracking time-in-lieu i.e. someone has a certain number of days/hours holidays(vacations) allocated but they can increase that by doing overtime and then taking time off - "in-lieu". While some organisations have a system in...
Training records management with Excel – tracking who is certified and who is current
Do you have to track who is up to date with their certification across a number of courses and departments ? This blog post should help. Recently I was working in an organisation and one of the users - new to Excel - who had been on a course I had given, had been...
Excel Path – Step by Step
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...
11 – How to freeze top row of large data set – View | Freeze Panes
Ah, yes that joyful scenario. You have a list - a great big juicy list and you want to scroll down that list but oh no my headings have disappeared...WHAT DO I DOOOOO? Well I *have* heard of people who have sellotaped the headings onto their monitor.....and that is...
06 – How to create a chart with a max/min/target line
The secret - usually forgotten sauce to creating charts in Excel is to make sure your data is properly organised - no blank rows, no blank columns and you need to go all - what I call - Judge Judy on it - "The data, all the data and nothing but the data". This video...
02 – Excel formulas – adding up numbers and dates
In this video I show you how you would use plus - + to add up numbers and dates in Excel. Here is a link to the file if you want to practise Choose where you want the answer to goType in =Click on the first cell you want to useType in +Click on the second cell. Press...
01 – Starting Excel from the beginning
One of my key ideas for this year is to create a section in my newsletter specifically for Excel newbies - so that people who are new to Excel can use it as a way to get stuck in and start understanding the terminology. If that is you, I would suggest starting by...
Case Studies – How To
How to create a vlookup that matches on column names..
I was recently teaching a class on Vlookups() - what I call one of the "usual suspects" and one of the participants had a question around how to speed up her vlookups - particularly when the headings were the same. If you are not sure how the Match function works -...
Use Excel to identify waiting times..or see if you have an appointment or disappointment..
In this video we are going to identify how to use Excel to identify waiting times. Some people have an appointment and others do not - they have a disappointment. I'm sorry but that makes me laugh. EVERY. SINGLE. TIME. Truly sad and pathetic I know... You can view the...
Using Excel to track receipts in a non-profit
Many non-profits use Excel to track their finances. In this blog post I am going to use some sample data to show how you could capture a single payment - that often has to be allocated across multiple projects and/or expenses. You can download the original file here...
How to clean up text using Excel’s Magic Ingredient – Power Query
Data cleansing is really the rubbish collection of Excel. It is not really anyone's favourite job but if you want to get all those charts and pivot tables working properly - it all has to be done. It is somewhat similar to those cookery programmes where all the cooks...
Shall I compare thee? O spreadsheets – yes, you can – with Spreadsheet Compare
I was recently asked in a class about comparing two spreadsheets - as you can in Word. I hadn't heard of anything but guess what - as with so many Microsoft products - turns out that there was a tool to do that..called Spreadsheet Compare. The interface is a bit 1990s...
Excel – General
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...
Creating a KPI Dashboard – Part Two – Calculating calendar and work days. Identifying who has met/not met the KPI
This is the second of my series in creating a KPI dashboard. You can check out the first blog post here. You can download the completed file for this session here in which I show you how to calculate calendar days, working days and use the IF function to identify...
Doing a dashboard in 20 minutes….Jamie Oliver style
In March 2022 - as part of my Excel snacks sessions - I set myself the challenge of doing a dashboard in 20 minutes. The first thing to say is that I copied the example of TV chefs everywhere by having my ingredients ready. As one class participant once observed about...
A Variation on the Index function – special Area edition
I was recently asked in a class about the second version of the Index function available from the Formulas | Function menu. To be honest I'd never come across it before. Needless to say I was intrigued and here we are - a blog post about it to brighten your day (OK,...
Random
Learning Excel vs Improving Excel | “Just practice” vs “Deliberate practice”
One of the areas that fascinates me as a trainer is how people learn. One of the big ideas over the last few years has been the idea of "deliberate practice" as outlined by Malcolm Gladwell in his book Outliers. In this book, he argues that it takes 10,000 hours of...
Excel can be used for anything! | Paul McKenna (celebrity hypnotist) said he used it to pick his fiancee…..
Well, as we start 2016, I know that some of you might be thinking of looking for a new relationship in this year and yes, Excel can help you. Paul McKenna (the celebrity hypnotist) says he chose his fiancee using an Excel spreadsheet. If this is something you want to...
Shameless plug – one of my tips got used in MrExcel’s latest (40th!) book “The 40 greatest Excel tips of all time”.
Am thrilled that one of my tips got used in MrExcel's latest Excel book: The 40 greatest Excel tips of all time. (This is the Amazon.co.uk link). This is MrExcel's 40th book. It's actually a beautifully produced colour book full of illustrations, jokes, funny Excel...
Five clever ways to become an Excel whisperer…
One - Learn how to enter data into Excel A big big chunk of working effectively with Excel is getting your data set up right. So basically that means, if you have repeating data e.g. training records, invoices, transaction, they should be entered as a single big...
Where is Excel going – 750 million users and counting – lookin’ good…
Excel is becoming somewhat like the Irish diaspora of the IT world. No matter where you go, you will find an Irish person and Excel - sometimes even together! In the article below there are a number of views from various Excel gurus and generally the view is very...
Personal
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...
OLYMPICS, FAILURE AND RYAN LOCHTE
I'vwe been watching the Olympics sporadically over the last week or so. The big battle has been in the pool amongst the swimmers. Now I have to say I'm no swimmer. I didn't learn as a child and while I did try it again, my instructor - try as she did - was unable to...
AFTER THE OVERWHELM | COMES THE SURPRISE
Last week, I blogged about how I was going to play music with a group for the first time and how I felt terrified and overwhelmed... Well, I showed up. There was one other oboe player - a 13 year old (also called Anne) who was rather more advanced than me but who was...
HANDLING OVERWHELM | EVEN WHEN IT’S STUFF YOU REALLY REALLY WANT TO DO.
I'm feeling terrified and overwhelmed. I've started taking music lessons and the teacher has decided that my next step is to participate in a "play day" with a group of other amateur musicians. I'm wondering how I could get out of it - plead a dose of the Ebola virus,...