Designing a dashboard to track Employee vacations [case study]

Posted on January 24th, 2013 in Charts and Graphs , Learn Excel - 77 comments

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

Employee Vacation Dashboard & Tracker using Excel

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:

Employee vacations tracker made using Excel tables

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.

Anatomy of the calculation engine - Employee vacation dashboard

Important formulas used in the calculations:

The key formulas & ideas used are,

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.

Excel features used in employee vacation dashboard

  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?

Detialed tutorial on Employee Vacation Dashboard - Now available in Excel School

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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

77 Responses to “Designing a dashboard to track Employee vacations [case study]”

  1. Subbaraman says:

    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
     

  2. Ludovic says:

    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

  3. Andras Ujszaszy says:

    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
     

  4. Lisa says:

    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 

    • PPH says:

      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.

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

  6. krissy says:

    hmm… i wonder how partial days could be integrated here.  for example, pto taken in half days or in increments of 2 hours?

  7. Cameron says:

    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!

  8. zur says:

    I have find dashboard, infact, application of excel command

  9. vijay says:

    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.
     

  10. Andy H says:

    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!

  11. Mohammed Mustafa says:

    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

  12. Rey says:

    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.

    • kwatzuro says:

      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

  13. hamad says:

    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??

  14. GJ says:

    great stuff.. very inspiring!

  15. Arvid Martin says:

    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?
     
     

  16. Denice Longshaw says:

    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

    • hamad says:

      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

  17. Kim says:

    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 …

  18. kwatzuro says:

     
    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.
     

  19. Ashish says:

    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?

  20. Brian says:

    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!

  21. Dave says:

    Would love to see a version that works in 2003. My employer is reluctant to upgrade

  22. gwstudent says:

    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?

  23. FKhyber says:

    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

  24. Pradeep D says:

    Excellent! What if I want to add designation to this?
     
    Thanks and regards,
    Pradeep D

  25. Pradeep D says:

    Excellent! What if I want to add Emp# & designation to this?
     
    Thanks and regards,
    Pradeep D

  26. luis says:

    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?

  27. Morgan Bardon says:

    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?

  28. Syed says:

    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

  29. Maxime Manuel says:

    Like I said in an early post YOU ARE A GENUIS!

  30. charles raymond says:

    hi
    i am trying to make an excel spreadsheet for tracking employee rotation, so that i will just look at the sheet in 3 months and know who is on rotation or who is available

  31. Shand359 says:

    I REALLY love the look and use of this leave tracker. I would love to have something similar for managing holiday etc at my work. I have even tried to get something created myself but it never seems to work……. Is there a complete version of this file somewhere that I can “aquire” that will enable me to use straight away after loading up current holidays…….. I am even willing to paypal donate to get this up and running……. Only issue I have with the demo is it only shows 3 months…… I would need to get it to show January 1st to December 31st……….. :)

    • Shaveta says:

      i think you update some calculations in sheet3…:)

    • Hui... says:

      @Shand359
      Insert 274 Columns in the two worksheets at the same location
      say before Column AA
      Then copy Column Z across to fill the gap
      You will need to check the ranges of the Conditional formats

      everything else should be ok

      274 = 366 – 92
      366 days in a year
      92 existing columns of data

      • David says:

        Thanks for this reply, very helpful – however, surely for it to work properly and with current 2013 dates some other information would be needed? Does anyone know how this can be achieved, please?

  32. Shaveta says:

    This is really great stuff. I am the first time visitor to this site and the dashboard is really amazing…

    Also it was good to read the comments above….

    Great going!!

  33. RJR says:

    Hi, how do I see the beginning of the year? The display only shows the last 3 months. Where do I edit that formula?! Thanks!!

  34. reema says:

    Extremely helpful tracker.but being a novice i need your help in one timy thing.. how do i incorporate ‘half days’ in this tracker.!!??

    Looking forward to your responce

  35. kibz says:

    how can i change the callender for 2013?

  36. Vishal Rajput says:

    Chandoo… I am impressed Great Work B’ddy…
    I am looking for HR Employee Tracker where Emp Name ,Date of joining,Salary Amt, Emp Code, Emp Address , and Leave details it has to be in Excel.

    I dont want to use excels two different files for one purpose.

    If you have some thing Please let me know…

  37. AJ says:

    I’m over my head… How do I update the dates and keep them current?

  38. Shane says:

    This looks awesome, but when I try to download the spreadsheet I keep getting an error in Excel 2010 saying “The file is corrupt and cannot be opened” :-(

  39. Asda says:

    Hi i’m having the same problem as well. Says it’s corrupt

    Can I get it emailed?

  40. Simon Phipps says:

    Hi Chandoo, Love the dashboard.

    Is there a way we can modify the leave type names on the dashboard? I can see that we can modify on the tracker, however this does not update the dashboard.

    Thanks

    Simon

  41. Rachael Kulokas says:

    Hi Chandoo,
    This dashboard is amazing. I did the download, but am unable to change the names of the vacation type table. Is it possible to have a copy emailed? I’m trying to make it work for the whole year (little success so far), but my company would pay for that. Thank you just for showing me what Excel can do if I keep learning! Sincerely, Rachael

  42. Dominica Smith says:

    I am not able to download the excel spreadsheet. When opening the zip file, only thing I see are the xml files. Please help or email the file to me @ dlsmith36@hotmail.com

    Thanks!

  43. Manish says:

    I am not able to download the excel spreadsheet. When opening the zip file, only thing I see are the xml files

    • Chandoo says:

      @Manish… the file is an XLSM file. Sometimes Windows mistakes it as ZIP file. Instead of unzipping, just open it in Excel. It will work.

      • Manish says:

        Thanks Chandoo.

        I started working day as 1 st jan 2014.
        I am getting working days as -10
        eventhough 10 reources are there in total it displays message as Manish [0 days] and 49 others most present

  44. Darly Kuriakose says:

    Chandoo,

    What is the software used to map the process as shown in the step 2 above (calculation engine) ?

    Regards,
    Darly

  45. Paul says:

    Hi Chandoo,
    Excellent site, thank you for generously sharing your extensive skills and knowledge.

    Like Brian above, I can download the dashboard but it just refuses to open. Please would you kindly email me a copy too.?

    Thanks & Regards

  46. Paul says:

    Apologies…
    Ignore me… I found the other download link further down the page and now have a working copy…

  47. Sharelle says:

    Hi Chandoo,

    Great work on this dashboard. I was wondering if it could be set up to should the entire year instead of just three months and how I might go about doing that. Since the 3 months are all based off of the latest date, I’m not sure how I would create something that simply shows me the entire year. Could you point me in the right direction, please?

    Thanks,

  48. Ng says:

    Hi Chandoo,
    Great templates!! but how do I change it to show a whole year calendar view? Appreciate if you can hint us on this. Thanks.

    • Ed Grant says:

      I am adding that to my copy of the template. It was easy to do on the calcualtion worksheet. Have to look at both the formulas and the conditional formatting. I also added more time off catergories. I am making the tempalte a tracker and planner for 12 months rolling time frame. Have not started on the dash board page exceprt for the 3 month format. The 12 month calcualtions are done and tested.

  49. Roy says:

    How can I change it to calculate 7 days a week work week.
    We work 7 day a week here.
    Thanks

  50. Ed Grant says:

    Just discovered your site looking for a vacation tracker. I studied your application yesterday and learned a massive amount of information. I am converting this to a time off scheduler/tracker to cover a 12 month rolling timeframe. I have added a 3 color heat map conditional format to fine tune the intensity of people with time off. I would love to have the calendar at the top and be scrollable with the employee data below. Right now I am struggling with that concept. I am going to try a dual picture link with transparency.

    Thanks for the wonderful template.

Leave a Reply