fbpx
Search
Close this search box.

Employee training tracker & calendar – tutorial & download

Share

Facebook
Twitter
LinkedIn

Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker & calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)

This is what we are going to build:

training-calendar-view

Please watch this 8 minute video before moving on. It explains how the workbook is setup and what it does.

[Watch this video on Chandoo.org YouTube channel]

Employee training tracker & calendar – how is it made?

Step 1: Get the data

The first step is to figure out what types of data we need. At the very least, we need 3 sets of data:

  • A list of people
  • A list of training courses

Let’s assume our data looks like this:

training-tracker-data-people

training-tracker-data-courses

All of this data is maintained in Excel tables (named people and courses)

Step 2: Set up a tracker to assign people to training programs

Once we have all the data, we can create a tracker (another Excel table). This allows us to map people to various courses.

Error checking at tracker level:

When you map people to training courses, there are 3 possibilities:

  1. The mapping could be duplicate
  2. The course is over capacity
  3. The mapping is OK

We can use conditional formatting to show these errorsin the tracker so that users will know if everything is ok or not.

We end up with something like this:

training-tracker

Step 3: Design calendar view on paper

We need to define goals for calendar view. Let’s say the calendar view should answer these questions:

  • What courses are happening right now (3 month window)
  • How many people have attended the courses?
  • What is the total cost
  • What is the feedback rating of the courses?
  • Show summaries for individual departments or all

Next step is to sketch the calendar view. Here is what I came up with.

training-tracker-calendar-view-mockup

Step 4: Set up slicer & scroll bar form control

Using the instructions in introduction to slicers and introduction to form controls, we set up a slicer on department and a scroll bar to select month.

Step 5: Calculate all the necessary values for calendar view

This is the engine of our calendar view. There are a lot of calculations that go in to showing  various summaries and monthly values. Explaining all of them will take forever. Instead, let me summarize the key techniques.

Once the calculations are ready, we move to next step.

Step 6: Plug the values in to Calendar view and format

One all the values are loaded to calendar view (thru linked cells, of course), we need to format.

  • Use conditional formatting to show borders, fill colors only if a cell has value
  • Use conditional formatting to change the color of star rating depending on the slicer selection.
  • Set header portion apart with colors and spacer rows.
  • Clean up and hide un-necessary stuff.

And the calendar view is ready.

training-calendar-view

Download Training Tracker & Calendar workbook

Click here to download the training tracker & calendar workbook. Play with the calendar view, Examine the formulas in hidden Calc worksheet and change inputs to learn more.

Of course, it’s not that simple.

I have summarized only the key steps. The actual process of making this calendar is a bit more lengthy and time consuming.

To learn more about how this dashboard is made, consider enrolling in our Excel School + Dashboards online course.

This workbook & detailed tutorial (45 mins) is now part of Excel School videos.

full-video-training-tracker-calendar

Please click here to know more about the Excel School program and enroll.

More Excel dashboards & trackers for you

If you manage staff then you would love these additional resources.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

26 Responses to “Employee training tracker & calendar – tutorial & download”

  1. Flennar says:

    Amazing. I'm from Brazil and i love your articles. Keep sharing knowledge. I also liked the art of the dashboard, very creative. See you later.

  2. Luke M says:

    Very cool dashboard, Chandoo. Thanks for sharing! Love it when a dashboard is designed with lots of interactivity but no macros. 🙂

  3. Dennis says:

    Truly AWESOME, Chandoo! Gratefully yours, 🙂

  4. Nick R says:

    Great just what i needed. what would be the best way to include notification for annual refresher training?

  5. Jerry Nord says:

    I really enjoy these posts. Have you new book on my Xmas list, but not sure if I can wait that long. And the pic kind of seals the deal.

  6. Diana Bowe says:

    I have to manage sick time only for a large company (9K employees) and would like to know what is the recommended dashboard or software to purchase to manage this process. do you sell any program that can work for me other than the vacation tracker?

  7. Oken says:

    This is awesome!

  8. Stephen says:

    This is an excellent use of Excel for tracking what training has been scheduled across an organisation.

    The complement to this is adding another worksheet that provides a summary of competencies / skills, that also includes (as Nick R has noted), the capacity to highlight when a particular training is expiring so that it can be considered for adding it to the schedule again.

    I expect that this would be relatively straight-forward, given that you have a record of names, dates and training that has been attended. The only additional parameter to capture would be the period for which the training remains valid, eg. 1 year.

    Great work! A great practical example of using the power of Excel. 🙂

    • Chris says:

      Stephen,

      I am new to training and what you have suggested is exactly what I am trying to achieve. Did you solve it?

      Chris

  9. Ricky Dobriyal says:

    Hi Chandoo,
    Great Article!!! I am the one who never miss any article added by you or your team. Thanks you so much for giving us all these valuable info

  10. Julian says:

    Regarding the following formula, it really confused me to sum up all the reciprocal of each number resulting from formula "COUNTIFS" then come out the number of department. I could not figure out the mathmatical logic behind? Could you please explained furthermore for me? Thanks.

    SUMPRODUCT(1/COUNTIFS(people[Department],people[Department]))

    • Julian says:

      Hi Chandoo,

      Now that I've figured out the logic you don't need to answer my previous inquiry. I really enjoy your posts?it's awesome!

  11. Jamie says:

    Very good post and useful tool.
    With some small tweaking its uses can be across a range of areas and departments.
    Keep up the good work 🙂

  12. Ahmed says:

    Very helpful, very nice and creative template. very good learning .

    thank you very much.

  13. Tannell says:

    This is a very helpful tool and easy on the eyes!
    Thank you so kindly!

  14. Thyagaraju says:

    It is very use full tool, I taken the help from this blog and I am using and it very creative.

    Thanks

  15. Milind says:

    I have seen your training dashboard, its far from real life.It high time share some real life Training dashboard , I have bought dashboard course from you it was all waste.

  16. Shanu Singh says:

    Wow this is too good tracker..

    wondered when see scroll line with function!

    Is it VBA program or how did u do it???

    Pls explain if it fine with u

  17. Pranav says:

    Great Article!!! I am looking at the article for the first time....really its great one you have added.

    Thank you so much for making it simple...

  18. Aswani Kumar Rani says:

    Dear Chandoo,

    Are you still active providing online excel courses, because last video in which i have heard you is of 2013. Actually i'am interested in excel course and presently i'am based in Abu Dhabi. Please advise

  19. Alain-Franck says:

    Thanks a lot for this amazing tool, definitely it will help to manage the different trainings ongoing. just 1 question, when you schedule a training for example for February 2018, this training is automatically showing up in February 2019. how can we deactivate this option so that we shcedule the training just once?

    thanks in advance for your support

  20. Erin Esqueda says:

    New to your site and am so amazed by all the resources you have to offer. I am most interested in the Training Calendar and how I can tailor it to my organization. Specifically renaming the departments and adding some fields, how can I do that?

  21. Lebene says:

    Hello, what happens if a certain workshop is run more than once? How do we convey this?

  22. Freda says:

    Change your life with this automated revolutionary system

    More than 524921 world leaders have joined already.

    The future of the internet is launching soon!

    Get your Invite

    https://omassive.com

    All the best
    James

Leave a Reply