20

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.

Shudder.

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

Pivot_table_training_records

 

 

 

 

 

 

 

 

 

 

 

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

20 Responses to “Training records management with Excel – tracking who is certified and who is current”

  1. Joe Gomez says:

    http://www.the-excel-expert.com/wp-content/uploads/2015/08/training_records_management_List_not_completed.xlsx

    I could not open the file link above, could you send me an email with a copy of the training record management list for my review.

    Thanks, Be Safe!

  2. Sarah says:

    This has been so helpful! Thanks! Is there possibly a way to have different expiry dates for different training? For example, Basketweaving expires after 3 years but Knitting is 2 years?

    • admin says:

      hi Sarah, I am going to send you a file that allows you to check by a dropdown which has a list of courses (o1) and you can change the number of days in K1 and the cut off date to see what records are not certified. It’s done via conditional formatting and a formula.

      • Sarah says:

        Thank you very much!

        • Paul says:

          Hi.

          Thank you, this has been very helpful for me, and I intend to use it as the base for all my training records.

          Would it be possible for someone to send me a file that would cover the different expiry dates for separate courses.

          Paul

      • Danielle says:

        Hello,

        I am compiling a training record database for my employer and found this extremely helpful! Glad I found this before I started. Is it possible to get a copy of this file as well so that I can list different courses with different expiry dates?

        Thanks,
        Danielle

  3. Kirsty says:

    Thanks very much for this! Is it possible to capture subsets of different course? Ie. The overall course is “Knitting” but it has the elements 1. Getting started 2. casting on 3. different stitches etc. so that you know who has done what elements of the course? For a more detailed overview, if you catch my drift…

  4. Carolina says:

    I’m so glad I found this. I have been tasked (today!) with compiling the OSHA/Safety certification’s for our 100+ employees. (We are in construction) We want to keep track of who has has what certification (certified flager etc) and when those expire. Idea is to be able to look up employee and see what certs they have and be on top of who is expiring soon and to make sure those certification don’t lapse. Any thoughts? Id appreciate any help! THANK YOU>

    • admin says:

      hi Carolina, Thanks for contacting me. Couple of thoughts come to mind on this: Do you have a list of your employees and their certifications? Then you could use an if function to identify whose certification is due in the next 30 days e.g. =if((today()-certification expiry date)<=30, "Expiring", "current"). Then use conditional formatting to highlight all those entries with Expiring. That would be a start. You could then filter by "expiring" to see whose certification is due. I have done up a file for you which I am going to email onto you. Best wishes. Anne

  5. Shanta says:

    Thanks for sharing such valuable information. I have been assigned the task of keeping track of employees’ certifications and special licenses; so before they expire I can notify the employee that it’s time to renew. Sounds like the file you’ve shared with Sarah and Danielle would help me tremendously! Would you mind sending me the file as well? Thank you!

  6. Kathy says:

    I need to do exactly what Carolina is doing. If you could send me that information I would be so thankful. I have been struggling trying to come up with something in Excel or Outlook and this sounds perfect.

  7. Cindy Sealine says:

    Finding this site is a true blessing! I love the video that shows step by step how to actually do this, such a terrific learning tool! I have two questions. In our departments, we have 3 teams currently (could expand). All employees are on, and need to be certified in at least on team function, but some could need to be certified in all three. We have to audit them quarterly to ensure they are “on progress” or “off progress” in the one, two or three, that they are part of.

    Also, our employee lists changes a lot. Is there a way to import the most current list of employees from my master HR roster (say quarterly) and then have it delete the ones that are no longer with us? Without messing up current employee information?

    I love your site and plan to continue training here as much as possible. Thanks for the great tutorials.

    • admin says:

      hi Cindy, thanks for the kind words – much appreciated :-). What occurs to me as a first step would be to set up an Excel table that is linked to your master HR roster, have pivot tables built on this and then refresh. If your Excel data table is linked to your roster and has the most up to date data, your pivot tables will reflect this. Ask your IT department about the best way to connect your Excel table to the HR roster. I’d start there.

      • Cindy Sealine says:

        Great. I will work with them. What about the part where an employee has 3 different functions to be trained in….or maybe only 1 or 2 depending? How do I build that into my workbook? Each employee will be different, just depending on their role. They have to be audited in all courses, but might not need to be trained in a particular one. Is this even possible?

  8. Andy says:

    Hi Anne, Your page has been a fantastic help and the instructions were so clear and easy to understand. I would love a copy of the file you sent to Kathy and Carolina but i get access denied when I click the link. Wold it be possible to get a copy? Many thanks Andy.

Leave a Reply