Free Excel holiday and leave tracker (also available for Google Sheets)
Use our free Excel leave planner template to start tracking your team’s time off today.
No matter how small your company is, you will need to track time off as soon as you hire your first handful of employees.
Our Excel holiday tracker (that you can download to use as an Excel file) is a good place to start — download it here for free and start using it straight away!
What you can do for free with our Excel holiday tracker
Our holiday tracker is great if you want an off-the-shelf solution to manage time off for your small team.
It allows you to:
- Visualise a time-off calendar with your employees’ past and future leave
- Set a leave allowance for each of your team members
- Reuse the template each year and update the bank holidays
- Calculate how much holiday team members have already taken, and how much they have left
- Get all regular leave types such as public holidays, holidays, maternity, and sick leave.
- Set custom employee leave types and decide which ones are deductible from your leave allowance
How to use our Excel holiday tracker
We built our free leave tracker template to be very intuitive, but you may want to read these step-by-step guidelines to set it up correctly the first time you use it.
1- Download our template: once you click, the sheet will be automatically downloaded — once that’s done, rename it and save it.
2- Add your employees: head to the ‘Team members’ tab, and replace the sample data with:
- Your employees’ names
- Their department (Function)
- Whether their holiday allowance is set in days of leave or hours (Unit)
- Their holiday allowance
3- Add your leave types: now go to the ‘Lookups’ tab and add the types of leave you use at your company in the Leave Types column (for example, full-day, half-day, sick leave, long-term sick leave, parental leave, public holidays, etc.). Remember to select whether or not each type of leave is deductible from the allowance you previously set in the ‘Team members’ tab. You can also customise the identity code for each leave type.
4- You’re now ready to record your team’s time off: now, every time you approve a leave request, you can record it using the ‘Time off’ tab. Ignore the first two columns on the left (they self-populate). Simply pick the employee’s name from the drop down, add the leave start date, the amount of time they’ll be away and the type of leave they’re requesting.
5- Check remaining time off allowance: if you go back to the ‘Team members’ tab, you’ll see the numbers have adjusted, based on the data you added in the ‘Time off’ tab. Use this tab to see the number of days off your team has already taken, how many are booked for the future, and what’s left for the rest of the year.
6- Get a full overview with the time-off calendar: the ‘Calendar’ tab in the sheet is where you can visualise your team’s past and future time-off after it’s been recorded in the ‘Time off’ tab. This monthly calendar template is a view only tab, as all fields will be automatically populated from other tabs. What you can configure is the month and year field on the top left corner — use that to navigate to the period you want to have a look at.
You’re ready to go!
Limitations of the Excel holiday tracker
As brilliant as it can be, this is still a spreadsheet! Using spreadsheets to manage employee time off comes with a few downsides:
- You’ll still have to fill the sheet manually with time-off dates and duration every time someone requests annual leave.
- Spreadsheets do break sometimes. If you’re not an Excel wizard, it’s easy to make mistakes — and once a formula is broken it can be hard to fix.
- People can’t book time off using the sheet. Every request still has to pass through you, via a holiday request form template.
- You'll have to go back and forth between emails and spreadsheets, likely to lose information in the process, making the process prone to human error and miscommunication.
FAQ on how to use the Excel holiday tracker
How do I create my own holiday tracker in Excel?
If you own an Excel licence, it's pretty straightforward – just go to Excel and create a new file. You'll then be able to create your own with your own requirements or take inspiration from ours.
What if I don't have Excel?
That's quite simple actually – we've already created a version of the Excel holiday tracker on Google Sheets. Google Sheets is the equivalent created by Google and is completely free to use. Simply download our spreadsheet below.
What if I have more than 30 employees at my company?
If you have more than 30 employees, we highly recommend you consider another solution to handle your holiday tracking.
The task may seem easy, but it will only get more complicated as you grow. Updating the sheet will add on top of your other responsibilities, and you won't be safe from making errors.
If you're interested in finding out about how to completely automate the process, our HR software is here to help – with a time-off process that runs itself, we can help you build an automated holiday tracker in just a few clicks. No more hassle, and complete peace of mind when it comes to handling leave at your business. Start a free trial with us today to find out more.
How do I add a new team member?
To add a new team member to the sheet, it's quite simple:
- Go to the "Team members" tab
2. Simply write the name and all the details related to the team member below the previous one
- Your team member will have been added to the sheet and you can record their leave and absences
Can I automate the Excel holiday tracker more than it already is?
Unfortunately, an Excel holiday tracker has its limitations – you will still be the person handling all the leave processes in the background. Although the sheet will help you a great deal, it will still take a lot of your time to juggle between requests, emails, amendments and updating the cells.
If you want to completely automate the process, then a leave management system like Charlie could be beneficial – your employees handle their own requests and amendments. Your only job is to approve or deny in one click (with optional comments). Have a look and judge for yourself with our free trial.
Why are some cells blocked?
In the Excel file, you'll notice that some of the cells are blocked. This is to make it easier for you – some cells don't need to change, so you'll see a locker icon on the tabs.
What if a team member wants to cancel or amend their holidays?
If you wish to cancel or amend a request made by a team member, here's how to proceed:
- Go to the "Time off" tab
- Find the request the team member put in
- Once you find it, simply reset the allowance to 0 or change it to the number of days they wish to take
- You'll then find the allowance has been reset automatically in the "Team members" tab
Of course, this is not ideal, as the process is really error-prone, but this is how you proceed once a team member has requested an amendment.
You may also be interested in our resources:
