Posted on November 4th, 2015 in Charts and Graphs , Learn Excel - 22 comments

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:

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:

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.

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

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

### More Excel dashboards & trackers for you

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

 Can you extract numbers from text – homework Happy Diwali for all our readers – Animated Flower Pot Cracker in Excel for you…

### 22 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!

• Cuong Le says:

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))}

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

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?