SubscribeDesigning 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.
- Data entry for vacations taken by employees
- 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:
- Vacation tracker for entering dates & types of vacations.
- Calculation engine
- 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.
- Date logic
- Employee view
- 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.
- Combo box form control to select an employee to highlight their vacations
- Conditional formatting & cell grid to show vacations in a gantt chart like view.
- Highlighting selected employee’s vacations again using conditional formatting.
- Calendar view created by picture links
- Heat map of number of people away on each date using conditional formatting (similar example).
- Header section with references to calculations & cell formatting.
- 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.
| ||||
|
| ||||
|
Leave a Reply
 |
What is Power Pivot – an Introduction [video] | Can you calculate vacation days in a period? [Homework] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
37 Responses to “Designing a dashboard to track Employee vacations [case study]”
Chandoo,
An excellent example to summarize various Excel concepts learned over a period of time that has been put to use for a very useful and common place purpose. Thank you.
Subbaraman
Hello Chandoo,
I condiser myself as a pretty good Excel user but here I want to point out this is really a good and pretty example of what Excel can offer to common adminstrative tasks. By pretty, I mean it is clear, efficient and truly easy to understand and use.
Thank you for this brilliant communication
Ludovic
Congratulations, Chandoo,
I’d like to thank you that you’ve shared this excellent tool including tons of innovative solutions for the whole community.
Thank you again
Andras
I like this very much, but I’d like to have a vacation planner version for the manager to use.
3 months at a glance is fine, but I’d like to choose the range to be able to see the future planned vacations.
I’d like to see the fixed holidays shown for all employess in a separate color on the dashboard.
I’d like to somehow see the date when I click or highlight a cell
I’d probably setup a calendar template on another sheet and have the 3 month view use offsets with a scrollbar in order to scroll across the entire calendar.
Very interesting approach chandoo, very good one. I have done another tool in my way, but it is in french… sorry
We can see it :
- http://tssperformance.com/fiche2.php?id=48
- http://www.tssperformance.com/fiche2.php?id=46
- http://www.tssperformance.com/fiche2.php?id=21
Thank you again chandoo
hmm… i wonder how partial days could be integrated here. for example, pto taken in half days or in increments of 2 hours?
Hello Krissy,
That´s a very good point. Anyone can comment?.
Thanks a lot,
Suomi
Hi Chandoo,
I have been reading your posts for a while now and I have to say that you are one of the most generous people I have seen online. Everything you write here is pure gold and really useful for so many people. And you do all this without charging anything. I’m not even required to login or sign up for a newsletter (even though I just did).
With your help my excel skills have grown excessively in just a few months time. I went from hating to start up excel and facing these dreadfull CSV’s with no formatting to having excel open all the time and using every minute I can to turn boring data into visually inspiring dashboards and worksheets. Thanks very much for dedicating your time to help us. Keep up the good work!
I have find dashboard, infact, application of excel command
[...] This is based on yesterday’s employee vacation dashboard. [...]
Chandoo..!! Your latest dashboard on Employees Leave Tracking, very nice one.
I learn many things from your online portal especially with excel and your words in the portal are very clear in explaining the functions and formulas used.
Keep up your good work. Many people like me who wish to do self-learning getting practical solutions to the problems through your wp.
Man, your creativity is amazing. I hope to have this graphical aptitude one day. Thank you for the content, you are helping many people rise above Excel noob-ness to elite-ness!
Hi Chandoo,
The previous students who took part in the Excel School and Dashboard course, can they get to download the video tutorial on this dashboard. I was a student, and would be interested in how this dashboard is created.
Mustafa
This is very close to what I want to build, a vacation calendar where I can input vacation requests based on availability to a maximum of people that are allowed to be off in a given day per department. Need also a running percentile on vacations hours taken (which solves partial days taken) against the total number of entitlement by each department or shift.
Rey,
I’m in the process of finishing a file where I can track vacation taken on a particular date or dates. However, I’m struggling with partial days taken such as hours. Perhaps, you have created that we both could combine. Let me know if you like to know if you would like to work together in this project.
Regards,
Kwatzuro
i have a question
is it possible to change the weekends in excel formelas like WEEKDAY OR NETWORKDAYS
my weekends are on Friday and Saturday not Saturday and Sunday??
i should have mentioned that i use excel 2007 so using excel 2010 functions is not an option for me
great stuff.. very inspiring!
I need a formula, macro, etc. which will take a starting address and an ending address and compute the miles between them. I want the result to be available to place in an Excel cell. I don’t care how the address components are formatted, but a columns for house number, street address, city, sate and zip should work.
Any ideas?
[...] Designing a dashboard to track Employee vacations [...]
Hi Chandoo.
Have you done a video on this dashboard. It will be fantastic if you have. This is really good and comes in so handy in my line of work. I use a T&A but this will help me pull so much data out of it.
Once again THANK YOU
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
Hi Chandoo
i really liked your vacation tracker so gave it to the HR person to use … she wonders how to improve it so it calculates a sick leave and annual leave balance which accrues every two week pay period …
Hi Chandoo,
First of all I want to let you know that I have been a fan of yours for a long time. I deeply appreciate the time that you dedicate to your website and to your followers. In addition to that, I love every work that you do especially the dashboard to track employee vacations. You are the guru of Excel!
Chandoo, would it be possible to deduct partial hours from an employee accrued vacations like Krissy says? Currently our employees are allow to take a vacation days which corresponds to 8 hours of vacation; however, there are time, and our company allows it, when employees want to take 2 or four hours of vacation instead 8 hours. Can this be possible in your dashboard to track employee vacations? If so, I have created an Excel file that I can deduct vacation day(s) from a date or dates, but I am not able to deduct hours from accrued vacation days from employees. I would truly appreciate If you could modify your file to be able to accomplish this task or I can email you my file, so you can take a look at it and modify as you please.
Again,
Thank you very much for sharing your knowledge and passion for teaching others.
Hi Chandoo,
I am currently enrolled for the excel school but cannot see the tutorial of the above dashboard. Can you please point me to the right direction?
Hi Chandoo,
Hi I’ve always been amazed at your creativity and your ability to think outside the square. I’ve tried to download the employee vacation dashboard above but I can’t open it, would it be possible to email the workbook to me please? Would love to see how this one works!
Many Thanks!
@Brian
I’ve emailed the file to you
Would love to see a version that works in 2003. My employer is reluctant to upgrade
This is really slick but here’s a challenge. How would you use the SUMIFS formula if the vacation types along with the associated start/end dates were stored in multiple columns instead of a single column? Assume there are 4 distinct columns of data for the 4 vacation types. Can the entire spreadsheet work with a simple adjustment?
Thank you very much for the great work, this is a great tool for managing the employees leave, you have no idea how thankful i’m right now for leaving it free and unprotected. keep up the great work!
Greetings from Afghanistan
[...] Employee vacation tracker & dashboard [...]
Excellent! What if I want to add designation to this?
Thanks and regards,
Pradeep D
Excellent! What if I want to add Emp# & designation to this?
Thanks and regards,
Pradeep D
this is anamazing dashboard .. however I want last 5 months view ….can you please guide me ?
Also if I enrol for option I, will I not get tutorials for similar dashboard creations?
Hi there. Love this dashboard, but is there a version that shows all 12 months of the year so that it can be printed out as a wallboard?
Hi Chandoo,
i m confused with start date and end date, when i put data in both colomn but the result is not correct,(total day)
please help to use this sheet for my 48 staff