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 assigned the task of doing just this. She needed to track who had completed various certifications in the past two years and then to present those numbers analysed across departments and job grades.

She asked me to have a look at what she had done. Two things struck me. First of all, how much work she had put into this. Secondly, that she had set it up in such a way that she’d have to do the same amount of work every month. And I won’t even talk about the Everest of work she would have to face when she was asked to compile that work  for year end.


Essentially what she had to do was track the number of people who had current certification (done in the last two years) for a number of courses. This had to be calculated across departments and positions.

I took on the project and now she has an Excel file that (with the use of dropdown lists, vlookups, if functions and pivot tables) she can easily keep up to date. And if she is asked for annual numbers – no big deal. pie_chart_tracking frustrated_woman

So what I am going to take you through here is how to set up something similar. There will be a file at the end that is all set up for you – so feel free to adapt for your own organisation.

Step One – Data Preparation

  1. The first step was to list the names, positions, departments  and (current employment) status of all the staff members.
  2. I also thought it would be interesting to add the gender as well in case we ever needed to analyse the courses by gender.
  3. List all the courses they had to attend.
  4. List the departments they were all in
  5. List the various positions available
  6. List the various current employment statuses available.
  7. They were all done and then converted to tables (which will allow for updating/adding of courses etc)
  8. Then, using Data Validation, they were added as dropdown lists. The beauty of using the table approach is that when any of these need to be added to, that can be done so in the original table.
  9. They are all listed in the Lists sheets in the file.

You can download the file here …training_records_management_List_not_completed

You can see how the data was entered in the video below.

Step Two – Data Entry – Should feel like the first time – every time.

The natural approach of the inexperienced Excel user is to set up the data the way they think they want it to appear at the end. That means users often set up headings going across (instead of down) and try to get away with entering data once. Alas, that’s not how Excel works. They have seen a completed pivot table. That’s sort of like trying to form flour into a loaf without cooking it. And then doing it again…

What you must do is capture your data line by line.

Every line should be entered as if it has never been entered before. That means that in our Data Entry sheet we must enter every course as if it has never been entered before…yes, I know that seems like a lot of work and that we are entering stuff multiple times. And yes, we are. BUT the beauty of this is that – once you know how to use a pivot table – it’s easy to do analysis and – more importantly – to keep it up to date. Also if we use dropdown lists for it, it does make it much simpler. And of course vlookup functions to pull in the correct position and status.

You can see how it is done here.

Step Three – adding formulastracking

Yes, there’s more! We need to add two formulas to this Data Entry list.

  1. Convert the Data Entry list to a table (by clicking anywhere in the table, press Insert – Table). When you do this it means your formulas automatically copy down.
  2. How to track if our certification is current. “Current” in this case means in the last two years (720 days). Essentially this formula measures the difference between the course date and a cutoff date (that could be today() or a date of your choosing, checks if it’s less than 720. That means a “yes”. Otherwise, it means a No). It looks like this. This is the formula in the Certification Current? column.
  3. =IF(($M$1-[@[Date of course]])<=$K$1,”Yes”,”No”)
  4. However, I came across another problem which was that some people are very enthusiastic course goers i.e. they might have attended the same course more than once in the last two years, but they only should be counted ONCE. I struggled with this. But with the help of the Internet. God bless Google. God bless Experts Exchange. And in particular may the sun always shine on  Ejgil Hedegaard who came up with the humdinger of a formula in UniqueID heading.

You can view the completed entries in this file – training_records_management_List_data_entry

Step Four – adding your pivot tablesvictory

At this point, your data will be coming together nicely and once you have your data set up properly…your life around this will be so much easier…

OK, so you have slaved over your data, now your boss wants to know  how many different grades in each department have done the course…so for that we need our trusty pivot table.

  1. Click in your Data Entry list.
  2. Click on Design – Summarize with pivot table.
  3. Put it on a new worksheet. Set up your pivot table like so..













Two final steps…

  1. Add a chart to that…click in pivot table – Options – Pivot Chart. I chose 100% Stacked Column – (third on top row from left) but you may choose a different one.
  2. And if you add new entries into your data entry sheet as people complete certification – all you have to do is hit Refresh (click in pivot table – Options – Refresh) or click in pivot table and press Alt and F5.

You can view the completed files here: training_records_management_List_completed

A Columbo moment – to finish off. columbo

There is a detective series from the 1970s starring Peter Falk and the final moments of the show were usually heralded by Columbo saying “there’s something bothering me….” and I had that moment with this data set. I found myself wondering how accurate this data is – because what about people who have not attended any courses – quite possible if someone has just started – and it made me realise that I needed to add another layer to it. I did assume that the list of names in the Lists sheet would be complete….

So if you have a look at the Percentage of all sheet – you will see there that I have added another table (linked to the pivot table) that shows the percentage of people completed as a percentage of the total for each position…

This may not be relevant for you but you can view how I did it in the video below…

You can view the completed files here: training_records_management_List_completed

So if you have been assigned this task this approach allows you to track records for a number of years, allows you to easily summarize the


New! In response to many comments about the expiry date, here is a link to the file that gives expiry dates – using a vlookup function.