Free Excel Staff Holiday Planner Template
By Kieran Baker
Managing annual leave is a must for teams of all sizes. This leave planner template is a great free way to get started with tracking absences.
If you are looking for a robust absence tracking tool to avoid managing a spreadsheet, checkout our flexible & easy to use online leave planner.
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.
Monthly totals are displayed at the end of each row for each employee. Yearly totals are displayed in a separate tab called Totals.
Enter your employee names into the name cells on the first sheet, the names will update automatically on the other sheets.
8 leave types are provided by default. These include:
- Half Day (morning)
- Half Day (afternoon)
- 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.
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.
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) therefore to set an employee's name you need only update the first sheet.
Working days can be specified in the generator. Non-working days are displayed as a light gray cell in the template. Non-working days are marked for display purposes only, they are given no special treatment when absence totals are calculated.
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 2020, 2021 and beyond.
Number of months (multi year)
How many months do you want your template 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.
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?
We would love to hear how well our Excel template worked for you and how we could improve.
We are planning to add support for public holidays - let us know what countries you would like to see supported!
Please consider sharing if you found the template useful!
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.
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!!
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.
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.
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.
Request a tool
Got another tool that you would find useful but can't find online? Let us know!
Your download will be starting any second.
Self-managing absence tracking
Give your team access to view and book their own absences with our online leave planner.
- Employees book their own absences
- Managers approve or deny their requests via an email
- Everyone can view planned absences
Our customers say they find:
- 👫 Increased self-organisation due to better absence visibility
- 🕒 Less time spent booking absences
- ⚠️ Greatly reduced absence conflicts
- 🧐 Booking errors are prevented
- ❓ Reduced queries to managers regarding absences