# Employee training tracker & calendar – tutorial & download

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

- Employee vacation tracker & dashboard
- Project planning & Gantt charts in Excel
- Monthly activity planner template
- Team to-do list template

### Leave a Reply

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

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

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