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
Is there a way to add overtime and have that calculate holidays earnt from doing overtime and addding this to the remaining entitalment?
⭐⭐⭐⭐⭐Where has Toil gone? Always found this super helpful.
⭐⭐⭐⭐⭐Just perfect
⭐⭐⭐⭐⭐This is a great help for the business.
⭐⭐⭐⭐⭐Great template. Please could I ask how you add a someone new to the spreadsheet without losing any of the formulas?
⭐⭐⭐⭐⭐It was super helpful for 2024! Thank you so much for offering such a thing for free, I think that's amazing.
⭐⭐⭐⭐⭐Thanks a million once again!!
⭐⭐⭐⭐⭐Great time saver
⭐⭐⭐⭐⭐Absolute amazing
⭐⭐⭐⭐⭐Fab tracker modified for use by my company - easy to use
⭐⭐⭐⭐⭐Simple and Awesome. Thanks a lot.
⭐⭐⭐⭐⭐Used it successfully for our 5 staff in 2024 - Perfect!! Thank you !
⭐⭐⭐⭐⭐Already using it this year and looking great. Team members can easily track many aspects of their days.
⭐⭐⭐⭐⭐Excellent
⭐⭐⭐⭐⭐Brilliant. I share this with my team, allowing them to request leave and be able to see if they clash with anyone else first.
⭐⭐⭐⭐⭐Looks amazing. Will be using next year. Thank you
⭐⭐⭐⭐⭐This has been awesome for my team. Nicely done. I need assist adding a another type once I have have created the form. Can any assistance be provided?
⭐⭐⭐⭐⭐Just here to say Thank You for a lightweight and flexible solution to keep track of our small team's absences. Nicely customizable and we managed to stay committed to use it all year (in addition to all the official company tools that did not cover all our needs). Great work and very straightforward!
⭐⭐⭐⭐⭐Fantastic work and planning tool, I highly recommend it. Thank you
⭐⭐⭐⭐⭐This form is absolutely fantastic and perfectly addresses all our team’s needs. Without it, managing and tracking team vacation plans would be quite challenging. Thank you so much, Kieran! I just wanted to check if it’s possible to add public holidays for a specific year via a list, so they can be automatically marked in the corresponding cells on the monthly sheets.
⭐⭐⭐⭐⭐Super easy to set up, really helpful tool to use. I wonder when May 2025 onwards will be available to download?
⭐⭐⭐⭐⭐Very helpful spreadsheet - really easy to use - thanks
⭐⭐⭐⭐⭐Thank you so much for this excellent leave planner template. I run a small charity and it works extremely well for us.
⭐⭐⭐⭐⭐Helps me manage a fleet of 70 drivers. Could not be more simplistic!
⭐⭐⭐⭐⭐Thank You! I have been using this Tracker for 3 years now, and my coworkers are very impressed.
⭐⭐⭐⭐⭐So easy to use and keep track of holidays and absences.
⭐⭐⭐⭐⭐Great resource! Thank you.
⭐⭐⭐⭐⭐Very good, detailed and customizable template I have used this year and definitely going to keep using it.
⭐⭐⭐⭐⭐I have been using this with my team for a few year, so much more flexible than other off the shelf and HR solutions - great work
⭐⭐⭐⭐⭐I have been using this template for the past 4 years for my small team and it is just the best! Thank you so much
⭐⭐⭐⭐⭐Thanks for this tracker, except the first date column doesn't work/match with the input (it shows the week)
⭐⭐⭐⭐⭐Handy bit of software
⭐⭐⭐⭐⭐I have been using this for the past 2 years for my team of 5 and it is amazing, will continue to use! So simple and easy, thank you
⭐⭐⭐⭐⭐This tracker is a game changer for me - use it every year to track the team holidays in my office and it's easy to use and saves a lot of time trying to add up days taken etc. Thank you!
⭐⭐⭐⭐⭐Great spreadsheet - thank you. Is there any way of adding days, ie. if someone has worked overtime and needs a day or half day adding to their annual leave tally? Or if someone who works 4 days a week, works the 5th day and wants to bank it to take off sometime in the future?
⭐⭐⭐⭐Great tracker, really easy to use. Thank you
⭐⭐⭐⭐⭐Great template, thanks! Would you be able to add a department option?
⭐⭐⭐⭐What a great spreadsheet, perfect for holiday and sickness
⭐⭐⭐⭐⭐Thank you!!
⭐⭐⭐⭐⭐Thank you for this :)
⭐⭐⭐⭐⭐very well done
⭐⭐⭐⭐⭐I have been using the template for over 3 years =now, its absolutely brilliant. It has saved me so much time and effort. Thank you for creating such a valuable template.
⭐⭐⭐⭐⭐This os perfect for me to use thank you
⭐⭐⭐⭐⭐Is there a way to add holidays accrued due to overtime/overwork? that will add to the total figures on the last tab? (Hope this makes sense)
⭐⭐⭐⭐Absolutely Brilliant! Super easy to set up and use, it's made holiday tracking in our little team a dream :)
⭐⭐⭐⭐⭐Amazing and so easy to set up!
⭐⭐⭐⭐⭐Love it, for our small company it is perfect! thank you
⭐⭐⭐⭐⭐So user-friendly...
⭐⭐⭐⭐⭐This template is fantastic and very easy to use - thank you for your excellent work! However, is there any way that it can have another tab for holiday count for each month for each employee? For example: Base allowance Carry over Total allowance Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Remain - Similar to the Totals tab but for monthly figures instead. This would be very helpful for my companies monthly accrual.
⭐⭐⭐⭐⭐could you add option to start month from mondays or adding shift pattern ffor exapmle every other week forking saturday, every 3 weeks working tuesday to saturday and so on and on i am working on my shift rota with different work patterns and looking for sth like that
⭐⭐⭐⭐⭐I love the template, it's friendly, easy to use, visually nice and not boring, everything is clear. One question, does the template contain any macros? My organization does not allow to use ones like this. Many thanks!
⭐⭐⭐⭐⭐Great. I wanted to be able to see one quarter at a time with the option to see year.
⭐⭐⭐⭐The best leave tracker, very easy to use
⭐⭐⭐⭐⭐Hi just started to use this has saved me so much time and works perfectly for what I required. Many thanks
⭐⭐⭐⭐⭐Excellent, Thank You!
⭐⭐⭐⭐⭐Love this template! Keep up the good job team!
⭐⭐⭐⭐⭐amazing template, exactly what i needed. thank you!!!
⭐⭐⭐⭐⭐Brilliant spreadsheet, thank you!
⭐⭐⭐⭐⭐The is a great template, but it would better if it had a field in totals for we to see the overlap of holidays
⭐⭐⭐⭐⭐My team has been using this for the past year and it's really helpful, thank you!
⭐⭐⭐⭐⭐
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
