Designing a dashboard to track Employee vacations [case study]

Designing a dashboard to track Employee vacations [case study]:
HR managers & department heads always ask, “So what is the vacation pattern of our employees? What is our average absent rate?”
Today lets tackle that question and learn how to create a dashboard to monitor employee vacations.

What do HR Managers need? (end user needs)

There are 2 aspects tracking vacations.

1. Data entry for vacations taken by employees
2. Status dashboard to summarize vacation data

Based on my interaction with few HR managers, the below questions are asked most often when it comes to vacation tracking:

• What is the absent rate of our employees (in any year or latest 3 month period)
• What are the vacation patterns for individual employees (or teams)
• On which dates most employees are absent?
• Who is taking most (or least) vacation days?

A look at the completed Vacation Dashboard

Take a look at the completed dashboard (click to enlarge).

Constructing Employee Vacation Dashboard

The construction process can be broken in to 3 steps:

1. Vacation tracker for entering dates & types of vacations.
2. Calculation engine
3. Dashboard design & formatting

Step 1: Creating a tracker for vacations

The best way to create a tracker is to use Excel tables. Set up one with 4 columns – Employee name, vacation type, start date & end date, like below:

By using tables, we can continue to add more vacation data (or remove older data) and all our formulas continue to work seamlessly.
Additional tables required…
Apart from the main vacations table, we need below tables:

• Employees table – to keep the names of employees
• Vacation types table – to keep the type of vacations
• Holidays table – with official holiday dates

Step 2: Calculation engine

There are 3 portions in our dashboard and each of them requires certain calculations.

1. Date logic
2. Employee view
3. Calendar view

For all the views, the main driver is latest date, which is the maximum value of end date column in vacations table (=MAX(Vacations[End Date]))
Tip: Use Max to find latest date
Although the calculations are not very complex, explaining each of them can be very tedious. So let me summarize them with a diagram.

Important formulas used in the calculations:

The key formulas & ideas used are,

• Range lookup formula
• SUMIFS formula
• Calendar formulas
• EDATE, EOMONTH, WEEKDAY, NETWORKDAYS Formulas
• The lovely INDEX formula

Step 3: Dashboard design & formatting

This dashboard is an excellent example of synthesis - combination of multiple Excel features to create something very simple and easy to use.

Excel features & ideas used:

There are many Excel features & ideas used in this dashboard. First take a look at the illustration below.

1. Combo box form control to select an employee to highlight their vacations
2. Conditional formatting & cell grid to show vacations in a gantt chart like view.
3. Highlighting selected employee’s vacations again using conditional formatting.
4. Calendar view created by picture links
5. Heat map of number of people away on each date using conditional formatting (similar example).
6. Header section with references to calculations & cell formatting.
7. Hyperlink on a rounded rectangle shape to link to tracker sheet.

Formatting the dashboard:

The basic layout of dashboard is just 3 boxes – a big summary box on top, a large employee view box (70%) and a small calendar view box (30%).
The fonts are Calibri & Cambria default fonts in Excel 2007 or above.
I used variations of Tan color in most areas of dashboard (headers, box backgrounds, buttons etc.) and shades of pink, blue, green & gray for marking the vacations. Orange is used to highlight selected employee’s vacations.
Although there is a lot of data, I designed this dashboard with minimal clutter. It is very easy to use (there is only one input control).

Download Employee Vacation Dashboard

Click here to download the employee vacation tracker & dashboard workbook. Play with it to learn more.

How do you like this dashboard?

I have thoroughly enjoyed the process of building this dashboard. I especially loved how picture links, conditional formatting heat maps (color scales) & simple calendar logic all have blended in to create a stunning calendar view.
What about you? Do you like this dashboard? How would you have designed it? Go ahead and share your feedback, ideas & suggestions for improvements in comments. I am eager to learn from you.

Want to learn more about this dashboard?

If you want to learn how this dashboard is constructed in a detailed fashion (along with 6 other dashboards & ton of material on dashboard design process) then please consider joining in our Excel School Dashboards program. Just today, I have uploaded a lesson (35 mins) on Employee Vacation dashboard to our Excel School website. You can use it and 32 hours more of video instruction to become awesome in Excel.
Click here to know more & join our Excel School program.