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.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?