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.


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

  1. Joe Gomez says:


    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:


          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.


      • Danielle says:


        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?


  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.

  9. Neal says:

    Hi Anne, this guide has been a tremendous help and your site rocks!

    Quick question I didn’t see covered in the videos, in the Lists tab since you created a table for the employees column is there a way to sort that column alphabetically as you make updates to your employees and also have the next four columns sort to match since they’re not included in the table? Thank you for your time.

    • admin says:

      hi Neal, you had me at “this guide has been a tremendous help” :-). To sort the employee names in the list, just click on an employee name and then do Data | A to Z. That should always sort them into alphabetical order..Not sure what you mean by “Sort to match” but if you just click in the list (without selecting the entire sheet), Excel should just put them in.

  10. Cristal says:

    I have a coworker that has sent me a training tracker that they use for each employee and they have a similar book they send their supervisors to keep track of each employee throughout the year. They sent it to me to see if I could find a more efficient way for them to do this. Currently they are creating a separate sheet for each employee to enter their training and then they would have to create a separate sheet for the supervisors to keep track of each of their employees. I am sure there is a simple answer but because the sheets have already been created I am having a difficult time simplifying their project. Would you mind if I send you one to see if you could show me how to simplify? I think that would allow you to better understand my question.

  11. catherine oconnell says:

    I would also really love a copy of file you have sent to Carolina I have been tasked with the same job as her in work. this is such an excellent resource!

  12. June pearson says:


    I just came across your training on data collecting.

    I oversee 52 staff and have not been using excel in documenting all the certificates and training which for some is yearly others every five years. I am finding difficulty in keeping track of all the training. If you have additional training tutorials I would appreciate it.


    • admin says:

      hi June
      could I suggest that you try using a vlookup that references the course to a table array that lists the courses and training with duration. Then you could use that to pull in the correct duration for each one?

  13. Tony Smith says:

    Hi Anne,

    Can you send me a copy of the Training Record file please as I have been tasked with pulling together a training record and I’m finding it difficult.

    I tried following the instructions but the F3 command would not work for me.

    I also need to learn how to use the Look Up’s function. Many thanks,


    • admin says:

      hi Tony, that is being sent to you. If you are doing the tables thing with the F3. A couple of things about that. (1). Check if your function key needs to be on or off (fn key – usually at the bottom of the keyboard) (2) Sometimes you have to go through the process I mentioned twice or sometimes three times – no idea why 🙂 So just try it again. Also if you sign up at my website: http://www.the-excel-expert.com you will get a link to a free tutorial on the vlookup so that could be a good starting point.

  14. Jonil says:

    Thank you for this great example.

  15. Petra says:

    Hi Anne,
    I work in a hospital and I need to track the education for nursing staff. There are many courses and several different expiration dates. Can you please send me a copy of the spreadsheet for that (mentioned in previous comments). Thank you. Also, do I have to enter the nurses name each time for each different course? or is there an easier way to do this? Example: nurse 1 has to complete 6 courses, so I would have to enter her name (and then the course) 6 times in the data entry sheet?

    • admin says:

      hi Petra, thanks for contacting me. Good news and bad news…if you want to track what courses a user has done, yes you will have to enter his/her details every time because as far as a computer is concerned – they are 6 entries. Will send you on the file and I am also currently preparing an online course to cover all this.. if you are interested in getting a template and tutorial and getting on the email list – here is the link (no obligation, it’s free and you can unsubscribe at any time ). https://bit.ly/2uewmMB

  16. Michelle says:

    This information is very helpful. I would like to know this set up will work on to MacBook. Second, We had similar spreadsheet but it went caput with currently with 500 employees. What does the position indicate? hobbit et.al, Is it possible to lock or protect this profile so other users cannot do changes. They may change or update but needs administrative approval or somewhat similar to this set up. Hoping you can send me the link, it seems it won’t download the whole spreadsheet.
    lastly, thank you so much.

  17. Bella says:

    I’m currently doing internship, and my supervisor who is the Training Manager here asks me to do a training tracker using Excel. There are almost 100 employees here. The thing is, my SV wants me to list out all the trainings that every employee had attended and also the trainings that they will be attending. Also for the future trainings, he wants Excel to give some warning or an alert 3 weeks before the date of training. Please help me out. =(

    • admin says:

      hi Bella
      Congratulations on your internship. Couple of observations. You will need to begin with identifying a list of your 100 employees (and any other data you need to track as well e.g. department, staff grade). Find out how far back he/she wants you go to with regards to the training, a year back? 6 months back. Then come up with the list of courses each person has attended. Note that if one person has attended 5 courses there will be 5 lines for that person. Have a look at my blog post on this..http://www.the-excel-expert.com/training-records-management-with-excel-tracking-who-is-certified-and-who-is-current/ With regards to the future training, you will need to have a date for the future training and then apply conditional formatting to highlight training that is due within the next month. If you go back to my website and search for Training Records management under categories I have 5 or 6 tutorials. Have a go at that and come back to me 🙂 Anne

  18. Jennifer says:

    Hi there,
    i have about 40 employees that need a variety of safety certificates (6) and they all expire at different times. Would you happen to have something that you could email me that could help with the process of keeping them up to date.

    Much Appreciated

    • admin says:

      hi Jennifer, no problem. You can use a vlookup where you combine the roles with certification and the expiry days e.g. Health and Safety is valid for 365 days, set up a list of your employees with the date they have done the Health and Safety course and then use a Vlookup to find the amount of days it is valid for and add that to the current days. I will see if I have a file that illustrates that and send it onto you.

  19. Lindsay says:

    Hi there,

    I found this to be extremely helpful in managing all of the training for my employees. I was wondering if it is possible to receive the file with the expiry dates for different training? Thanks!

  20. Tina M says:

    I am currently working on a new employee tracking spreadsheet in excel. A lot of the excel courses I’ve seen spend most of the time on companies with selling products and not on calculating due dates. I would love a copy of the file you have been sending out. I would also like info on the online training course you mentioned you was working on.


  21. Sofia Espinoza says:


    Like others who are asking, would you mind sending me this file with the ability to enter expiration dates for different training?

    Thank you!!

  22. Helen says:

    The template provides for a single expiry date. If each course had a different expiry date how would I go about that? Than you!

    • admin says:

      hi Helen, I will be sending you a file that covers that but basically you have two data sets – one that lists the courses and their certification length e.g. 100 days, 350 days etc and you link them together using a vlookup…

      • Helen says:

        Thank you – I will try it out and if I have any issues with the linking process I be in touch. I am a new excel user – very basic skills 🙂 Appreciate your help – thank you.

Leave a Reply