Posted on November 4th, 2015 in Charts and Graphs , Learn Excel - 14 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…

### 14 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 🙂

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