Free Excel Staff Holiday Planner Template
An Excel leave planner template is a great, free way to get started tracking absences. Our template is highly customisable and supports tracking absences in days and hours.
Below we go into detail on how to use the template and the available customisation options.
If you're looking to reduce time spent on absence management, you may also consider our annual leave management software.
Our managed system enables employees to book absences and view planned holidays on a self-serve basis. Greatly reducing queries, manager input and holiday clashes within the team.
Absences are still approved by a manager via the holiday request system.
Start a 30 day free trial to give it a try today.
Back to the Excel template, here's what you need to know...
Basics
Enter your employee names into the name cells on the first sheet, the names on the other sheets will pull through from the first sheet.
Monthly totals are displayed at the end of each row for each employee. Yearly totals are displayed in a separate tab called Totals.
The template differs slightly based on the 'Track absences in' option selected in the customisations.
Track absences in Days
Absences are recorded by inserting the relevant code into the cell for the day. For example, in case of sick leave, enter S into the cell.
All the absence types and their codes are shown in a handy key alongside the calendar month.
Track absences in Hours
Enter the number of absent hours into the cell relating to the day of the absence.
Leave types
Leave types are provided by default when using the 'Track absences in Days' option. These include:
- Vacation
- Half Day (morning)
- Half Day (afternoon)
- Quarter day
- Sickness
- Maternity/Paternity
- Compassionate
- Time off In Lieu
- Work from home
Half days can be recorded by using the H1 and H2 codes, these count as 0.5 in the totals.
There is also an extra custom leave types which you can rename and use as you need.
Totals breakdown
If tracking absences in days, the last tab of the template contains the totals of all absences for each employee as well as a breakdown by absence type.
The 'Absence count' field shows the sum of all absences while counting half days as 0.5 appropriately. It's a sum of the 'Absences this month' column across all months.
Customisation options
Our template is designed to be useful for the majority of teams with the default options but there are a number of customisation options.
Custom start date
Any start date can be chosen for your leave tracker. The default template will contain a full year from the start date specified.
For example, if your annual leave year matches the financial year from April to March, select April as the starting month.
Number of employees
Any number of employees can be specified. Whether you have 10, 20 or 100 employees this template is for you.
The employee names are pulled through from the first month (first tab) so you only need to set them once.
Working days
Non-working days are displayed as a light gray cell in the template. This is for display purposes only, they are given no special treatment when absence totals are calculated.
Year
You can define the year in which the leave planner template will start. Since the template is generated dynamically any year is supported. The leave planner template is ready for the year 2025, 2026 and beyond.
Number of months (multi year)
How many months do you want your absence tracker to cater for?
By default the template contains 12 months (1 year), you can generate a multi year calendar template by entering 24 months (2 years) or more.
Track absences in Days/Hours
This option changes the template quite a lot.
When tracking absences in hours you simply enter a number into a cell to signify the hours of absence taken. The totals are a sum of the hours. This option does not support leave type categories like the 'days' template does.
Tracking absences in days allowances you to specify the type of absence by entering a letter into the cell instead of a number. This option also allows you to track half day and quarter day absences. You also get the added benefit of an absence breakdown table by leave type on the totals tab.
Download
Questions about how to adjust the template once it's been downloaded? Check out the FAQ at the bottom of the page.
How did we do? Leave a rating
We would love to hear how well our Excel template worked for you and how we could improve. Also check out our video explaining how to use the template on youtube.
Please consider sharing if you found the template useful! You can ☕ buy me a coffee if you're feeling generous.
Your feedback
{{ comment.comment }}
⭐
Version 4.1 released
You can now fully customise the leave types, the codes and colours. You find the new options by expanding the 'Additional options' section just above the download button.
Here's a glance:
Thank you everyone for the support, we're gonna keep improving the template!
Kieran
4 March 2024
I have used this for the last 3 years and I am a very happy customer. Thank you !!
28 November 2023
We used this for 2023 and it's been a brilliant way of managing holidays in our team.
17 November 2023
Used last 2 years fantastic
10 November 2023
Great spreadsheet - no fuss, easy to amend if you need to. Thank you.
3 years running i am using this - does exactly what we need it for. Thank you :)
Awesome, exactly what I have been looking for and trying to create - such a time saver and its clear so no hidden surprise holidays from Staff. Just the solution i was looking for :)
It is a wonderful tool and saves a lot of setting up everything from scratch. Hats off to you and the team
it's great .. thanks for creating such useful form
This is so good! Thank you sooo much!!
Brill work, thanks guys you are awesome..
Great template, very helpful!! Tank you
The best office holiday planner I have used in the last 12 years - and its free!
Thank you, the holiday planner has worked perfectly for us this year :)
Used this for 2022, love love love it, so easy to use :)
Great improvement from two years ago as the older version used to count any input in the cells as a vacation day. With this new version Sick days are separate.
Absolutely genius! I don`t know what I would do without your holiday/annual leave planner spreadsheets. Thank you so much!!
The leave planner is fantastic and the updated version (with deductibles and non-deductibles options) is even better!! Well thought through tracker and simple to use.
It is a wonderful tool and saves a lot of setting up everything from scratch. Hats off to you and the team.
This is the second year (2022) I have downloaded this as an IT outage planning tool for clients. Many many thanks for this as it fits my needs perfectly with very minor adjustments. You have saved me a lot of work 2 years running now!
THANK YOU SO MUCH FOR THIS FREE HOLIDAY PLANNER ITS BRILLIANT, EASY AND SIMPLE TO USE :)
This is Awesome! Thank you very much!
This is a fantastic tool, 2022 is my second year using it. I would love when I add Absence Types I could add more colours.
Version 4 of the template is now live!
Here's the main changes:
- Split leave types between deductible and not
- Quarter day leave type added
- Bank holidays leave type added
- Two custom leave types added
- New colours chosen by a designer
Brill, ready for the 2022 ! so easy to use.
You are star! having this for free and so beautifully! Thanks a lot.
V3 template update released!
The top requested features from the past two months have landed.
Here's the changes:
- Colour coded leave types
- Record carry over
- Remaining allowance calculation
Enjoy! (16 June 2021)
Die beste Vorlage für den Urlaubsplaner für Mitarbeiter.
Brilliant, so easy to use and has saved me a lot of work. Thank you.
Is it possible please to add more than 100 employees, especially 230? I tried but I could not. thank you
Answer: The limit has now been increased to 250 employees. Thanks for using the PTO tracker spreadsheet! (27 March 2021)
Great employee vacation tracker, it makes my life a lot easier. The only thing I need to figure out is how to sort the names alphabetically we adding new employees.
Answer: I would recommend sorting your employee names with an online tool before entering them into the tracker template.
Best absence tracker I have found. I am training my managers how to use it
This looks excellent! Is it possible to have a conversion to hours taken as annual leave (vacation) per day; our annual leave works in hours not days as staff work different shift patterns - UK. Thanks :)
Answer: There's now a new option to track the absences in hours rather than days. Just select 'Hours' in the 'Track absences in' dropdown box when generating the template. Hopefully that helps cater for your needs!
It would be good if we can have week numbers also mentioned in the calendar. Thanks.
Answer: Week numbers have been above the dates. Thanks for the suggestion
This is excellent, could you add public holidays for the UK.
Answer: We're working on it 🙂
Thank you for the very simple yet useful template. Silvie, UK
Nice work, saved a pile of effort. Thanks
You guys are awesome. To improve this template, I suggest these couple changes.
- The count for absence should exclude those entered accidentally on excluded work days (e.g. Sundays...
- Will be one of the happiest to receive the updated version which you were planning for July 2020... If it is already available, please let me /us know.
- for public holidays, I would suggest you use dummy holidays which anyone can change on himself. Currently I am in Austria, but I might be migrating to Tanzania in the coming years. Thus it would be nice if I could just replace the default holidays to the corresponding country.
I can't find enough words to praise you guys.
Answer: Thank you so much for your kind words! I will drop you an email when the updated template is ready.
I think the idea of being able to switch out the public holidays is a good idea, although it may be trickier to implement. I will definitely look into it.
I think it is important for values on the weekends to still be counted in the off chance that someone does want to mark an absence on a non-working day.
Thank you for your great feedback!
It would be useful in the holiday tracker to have sick days, but not just 1 full day, having a option to input half day sick days
Answer: We're working on a new version that will support this! Thanks for your feedback 🙂
Number of leave options to pre-fill in yourself
Answer: We're working on it!
Fantastic, customized for employees and year. Simple Interface and easy to understand. It's perfect and 10 on 10. Brilliant work guys!!!
Brilliant, perfect employee attendance tracker for our small business, thanks a million.
How do I add somebody in half way through the year or delete a leaver without messing up the macros? I have tried to make changes on the first page - January and the last page, totals but it does not follow through? Can you advise? It's a great holiday chart.
Answer: Ofcourse, I've added two sections in the FAQ to cover your scenarios. Adding an employee and removing an employee.
Let us know if you have any more questions 😊
Can you make one that is multi year?
Improvement: That's a good idea. We've added a field for the number of months under the additional options section! (19 June 2020)
This vacation tracker template has been a godsend for tracking all absence for staff, thank you so much. Public holidays for the UK would be nice but can be entered easily
LOVE IT!!!! Great work!!
Great!
Love it !!!! One Thing missing was public holidays which i have added as a Absence Type. How can we reach out for more templates?
Answer: The public holidays type is a good idea! You can reach us from our support page!
I'd like a dayshift/nightshift option for each day which i can add as "D" or "N" without counting this as absence
Answer: Maybe we could add a section for types that don't count as an absence. If more people ask for this we can add it :)
Awesome. Such a time saver
That's amazing !!! Thank you so much! It helped me a lot. 🙏 😊
Improvement: Added the year above every month to make it a bit clearer. (21 Jan 2020)
It was a great way to get me started. Thank you!
This is perfect! Can't believe it's a free download - thanks, guys. Love it!
Hi, Is there that I can add in additional leave types for example - exam leave, study leave, certified sick leave, uncertified sick leave?
Answer: Ofcourse, adding an additional leave type is not too tricky. I've added the answer to the FAQ.
Hi, Is there a way that I can add in Public Holidays so that they are treated the same as weekends?
Answer: Weekends only marked a different colour for convenience and do not affect calculations. You may change the colour of public holidays to match weekends if you like.
Fix: Fixed a bug where templates which don't start in January showed the wrong dates when the year ticked over. (3 Nov 2019)
Very useful pto template, thank you very much!
In the totals page it need to have a breakdown of totals by code so you can tell straight away how many days annual leave have been had.
Status: Added a breakdown of absences by leave type on the totals page. (16 Sept 2019)
Brilliant fantastic - thank YOU so much, saved so much time tracking time off XXXX
If you link the names from Jan all the way through each month it might be useful. Also, you should have a Bank Holiday tracker that can be set for each month.
Status: Names are now linked from the names in the first month. (24 Oct 2019)
Mark the weekends in another color (cells), but besides that - great work !!!
Status: Weekends (non-working days) are highlighted with a lighter colour. (24 Oct 2019)
Frequently asked questions
Here are some answers to frequently asked questions about maintaining the template once it's downloaded.
Adding an additional employee after downloading the template
You can simply select the bottom row of the table and drag the little square at the bottom right (the fill handle) down to the next row.
Excel will automatically adjust the formula on your new row to work. You will need to do this on every tab in the template, including the totals tab.
Here's a video of how to do it:
Removing an employee
You can simply delete the user's row from each of the tabs. Excel will adjust the formulas to continue looking at the right fields.
The totals row for that employee will break as you start deleting the rows but you can delete the totals row for the user too.
Adding an additional leave type
You can use any letter for your absence types and the monthly totals will work as expected.
To add another column on the totals tab for a breakdown of that specific leave type, do the following:
In the totals tab copy an existing column (e.g. the Work from home column) and replace the letter in each of the COUNTIF statements to your new letter.
For example:
=SUM(COUNTIF(April!D5:AG5,"W"),COUNTIF(May!D5:AH5,"W"),COUNTIF(Ju...
becomes
=SUM(COUNTIF(April!D5:AG5,"X"),COUNTIF(May!D5:AH5,"X"),COUNTIF(Ju...
Make this change to the top cell then drag the fill handle down to apply the formula to all the cells below it in the column. That's it!
You can add the new letter to the key without it affecting any of the calculations.
Gallery
