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:
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:
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:
- The mapping could be duplicate
- The course is over capacity
- 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:
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.
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.
- SMALL formula to fetch the courses scheduled in a particular month
- INDEX formula to fetch various values from people, courses & tracker table.
- SUMIFS (and COUNTIFS) formula to sum & count various things that meet conditions.
- SUMPRODUCT formula when sumifs won’t just get what we want.
- Calculating average rating by selected department employees for a particular course
- TEXT formula to display currency values in the calendar view.
- REPT formula along with star symbols to show rating.
- IFERROR, because #DIV/0! is not the prettiest value on your output worksheet.
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.
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.
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.
26 Responses to “Employee training tracker & calendar – tutorial & download”
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.
Very cool dashboard, Chandoo. Thanks for sharing! Love it when a dashboard is designed with lots of interactivity but no macros. 🙂
Truly AWESOME, Chandoo! Gratefully yours, 🙂
Great just what i needed. what would be the best way to include notification for annual refresher training?
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.
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?
This is awesome!
In cell Calc!D20, there is a formula which is put inside a pair of {} and it uses tracker[i]. Could you please explain how it works?
Thanks
{=SUM(--(COUNTIFS(tracker[Person Name],people[Name],tracker[i],3)>0))}
@Cuong
This is called an array formula
To use it enter =SUM(–(COUNTIFS(tracker[Person Name],people[Name],tracker[i],3)>0))
then press Ctrl+Shift+Enter , instead of the usual Enter
Excel adds the curly brackets
I would read these for an introduction to Array Formula:
http://www.cpearson.com/excel/arrayformulas.aspx
http://www.excel-easy.com/functions/array-formulas.html
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. 🙂
Stephen,
I am new to training and what you have suggested is exactly what I am trying to achieve. Did you solve it?
Chris
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
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]))
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!
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 🙂
Very helpful, very nice and creative template. very good learning .
thank you very much.
This is a very helpful tool and easy on the eyes!
Thank you so kindly!
It is very use full tool, I taken the help from this blog and I am using and it very creative.
Thanks
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.
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
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...
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
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
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?
Hello, what happens if a certain workshop is run more than once? How do we convey this?
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