Most free leave-tracker spreadsheets are either too simple to handle growing teams or too complicated to use. This one has reliable formulae that don't break and handles all the things your broken tracker probably falls down on currently.
What is in the spreadsheet
There are seven tabs, in this order:
| Read Me | How it works and the order to fill it in. |
| 1. Setup | Company name, leave year, region, default allowance. |
| 2. Employees | One row per person. Pro-rated entitlement is auto-calculated. |
| 3. Leave Log | One row per leave entry. Days are auto-calculated. |
| 4. Dashboard | Per-person summary with a usage bar. |
| 5. Year Calendar | Year-at-a-glance grid where rows are people, columns are weeks. |
| Bank Holidays | 2026 and 2027 dates for E&W, Scotland and NI. Active region auto-filtered. |
The formulae are all set up for you so that if you change a date on the Setup sheet, the entitlements, used days and the calendar all update. Add a row to the Leave Log, and the Dashboard changes. There are no complicated bits that normal spreadsheet users wouldn't follow like macros, scripts, add-ons (we're not sure what they are either!?).
If a cell is yellow, you can change it. If it is blue, leave it alone as it works out the answer for you. We've used this format throughout to help stop people breaking the sheet and everything falling apart!
If you're looking to avoid spreadsheets and potential version clashes or broken formulae altogether then set up a free trial of Book Time Off. There's no card required and you'll be up and running in 5 minutes.
→ Start free trialSetting it up in 5 steps
Allow yourself 10 minutes for a small team. Five for the steps below, five for adding your people.
Pick your leave year
On the 1. Setup sheet set the leave year start date. The end date is calculated for you (12 months later minus a day). Most UK businesses choose 1 January or 1 April. We discuss the trade-off further down.
The Setup sheet · leave year section, with the input cell highlighted.
Pick your region for bank holidays
England & Wales gets 8 bank holidays in 2026. Scotland gets 10 (including the one-off World Cup bank holiday on Monday 15 June). Northern Ireland gets 10 (St Patrick's Day and the Battle of the Boyne). Pick yours from the dropdown and the right dates filter through automatically to every formula in the file.
If half your people are in Edinburgh and half in London you usually pick the region that matches each employee's contract rather than your office address. The simplest workaround in a spreadsheet is to track each region separately or to set the region for the majority and manually adjust day counts for the minority. You could always run two trackers, one for each office.
Set your default annual allowance
The default is 20 days and the UK statutory minimum (5.6 weeks for a 5-day worker), excluding bank holidays. Bump it up to 25 or 28 if you're more generous. You can override per employee on the next sheet.
Add your team
Move to the 2. Employees sheet. Each person gets one row. Yellow columns are for you to fill in: name, start date, leaver date (if there is one), days per week, allowance, and any carry-over. The right-hand columns calculate themselves.
Notice Chloe started in February so her entitlement (17.5) is pro-rated. Emma is leaving in September, so hers (18.7) is pro-rated too.
Log a leave entry
Move to the 3. Leave Log sheet. Pick the employee from the dropdown, choose a leave type, enter the start and end dates and set the status. The Days column calculates working days off (Mon-Fri) excluding bank holidays for your region.
The Days column on row 5 returns 4 · Tuesday to Friday after Easter Monday (which is a bank holiday and not counted).
Sick days, unpaid leave and "other" leave do not reduce the annual allowance. Only entries marked Annual count towards the dashboard. So you can use the same log to keep track of everything.
Opening it in Google Sheets
Same file and it'll take two minutes.
- Save the .xlsx file to your computer.
- Go to drive.google.com and upload it (or drag-and-drop into a folder).
- Right-click the uploaded file → Open with → Google Sheets.
- Google Sheets will convert it. All the formulas, dropdowns and conditional formatting carry across.
Once converted, the file lives in Drive and you can share it with the team like any other Sheet. The colour-coding stays. The dropdowns stay. The bank-holiday formula stays.
Google Sheets has the same fundamental limit Excel does: anyone with edit access can change anything. There is no row-level "this person can only see their own leave" mode. If that matters for you then it might be worth moving to an online holiday tracker like Book Time Off.
Handling the tricky bits
The 5.6 weeks rule
UK statutory paid holiday is 5.6 weeks per year for almost all workers. For a 5-day-a-week employee, that works out at 28 days and employers may include the eight bank holidays inside that 28, or grant them on top.
The maximum statutory entitlement is capped at 28 days. So somebody working 6 or 7 days a week is still only entitled to 28 statutory days, not 33.6.
Part-time staff
The same 5.6 weeks rule, just multiplied by their working days. Round up to the nearest half-day.
Dilan: 3 days a week (Mon, Tue, Wed)
Dilan works 3 days a week.
- Statutory entitlement:
- 3 × 5.6 = 16.8 days
- Rounded up:
- 17 days (excluding bank holidays)
- If contract is "incl. bank hols":
- 16.8 days total, of which around 5 fall on his working days
- Free annual leave to spend:
- ~12 days
In the spreadsheet, set Dilan's Annual allowance to 12 (the freely-bookable days). The Days column on the leave log will count only the working days he actually books off.
Mid-year joiners
The spreadsheet pro-rates automatically. The formula works out their full-year allowance, multiplied by the fraction of the leave year they will have been employed.
Chloe: starts 15 February 2026
Chloe joins on 15 February into a January-to-December leave year. She has a 20-day allowance for a full year.
- Days from start to year end:
- 15 Feb 2026 → 31 Dec 2026 = 320 days
- Days in the full leave year:
- 365
- Pro-rata fraction:
- 320 / 365 = 0.877
- Entitlement this year:
- 20 × 0.877 ≈ 17.5 days
Mid-year leavers
Same formula in reverse. The pro-rata factor uses the time they will have been employed during the leave year, not the full year.
Emma: leaves 30 September 2026
Emma started in 2020. She is leaving on 30 September. Her full-year allowance is 25 days.
- Days employed during this leave year:
- 1 Jan 2026 → 30 Sep 2026 = 273 days
- Pro-rata fraction:
- 273 / 365 = 0.748
- Entitlement this year:
- 25 × 0.748 ≈ 18.7 days
If Emma has used more than 18.7 days by her leave date, you may be able to recover the excess from her final pay but only if her contract or written consent allows it.
Carry-over
Carry-over is at your discretion. Some cap it at 5 days, or require people to use carried-over days within the first 3 months of the new year. The spreadsheet has a Carry-over from last year column that simply adds to this year's entitlement but only for people who were employed at the start of the leave year.
Bank holidays
The Bank Holidays sheet is pre-loaded with every official bank holiday for 2026 and 2027 across all three UK regions, taken from GOV.UK and gov.scot. The "Active" column dynamically pulls just the dates for the region you chose on the Setup sheet.
For 2026 specifically:
- England & Wales: 8 days
- Scotland: 10 days (includes Mon 15 June, the one-off World Cup bank holiday)
- Northern Ireland: 10 days (includes St Patrick's Day and the Battle of the Boyne)
When you log a leave entry that spans a bank holiday, the bank holiday is automatically excluded from the day count. Book Tuesday to Friday around Easter Monday, and the spreadsheet returns 4 not 5.
That's where upgrading to an online holiday tracker comes in. Book Time Off pulls the right bank holidays from the GOV.UK feed automatically and never deducts them from anyone's allowance, tracks days used and days remaining as leave is approved, and enforces your carry-over limit with a use-by date. No formulae to maintain.
→ Start a free trialConsiderations for new employers and small teams
If this is the first time you have had to track leave properly, a spreadsheet is more than fine to start. Here are the things that will save you from rebuilding it in six months. And for the wider process around the file (requests, approvals, clashes and records) the staff holiday management guide covers the lot.
Choose your leave year on purpose, not by accident
The two common UK choices are 1 January to 31 December and 1 April to 31 March. There is no statutory rule. A third option, anniversary-based, where each person's leave year starts on the date they joined is technically fine but quickly makes things complicated. Avoid unless you are a very small team and don't mind the admin hassle!
Calendar year (Jan-Dec)
For: Easy to remember. Aligns with the calendar everyone uses. Christmas is at the end so people use up their balance before then.
Against: Big rush of December bookings.
Tax year (Apr-Mar)
For: Aligns with payroll, pension and HMRC reporting. Smooths summer demand because the year is fresh.
Against: Slightly less intuitive for staff.
Write the policy down
One side of A4 is enough at first. It needs to cover the basics: how much leave, when the leave year runs, whether bank holidays are included, the notice period for requests, what happens to unused days at year-end, what the rules are on sick days during leave, and who approves leave. ACAS has good templates if you do not want to start from scratch.
Make one person the owner
A spreadsheet with multiple owners becomes a nightmare for conflicting copies. Pick one person who is responsible for entering approved leave, chasing pending requests, and rolling the file over at year end. If there's more than one then use an online holiday tracker like Book Time Off.
Communicate it the same way every time
Decide the channel for leave requests · email, a Slack message in #time-off, a form · and stick to it. Random verbal requests at the coffee machine are how things get missed. Again an online leave tracker like Book Time Off handles requests and approvals in one place.
Take stock quarterly
Once a quarter, glance at the Dashboard. Look for two things:
- People with too much leave left. If somebody is in October with 18 days remaining, they are not going to use it. Have a conversation early rather than awkwardly denying a month long request for the whole of December!
- People in the red. The Dashboard turns the Remaining cell red if it goes negative. Catch it in time and there is no awkward conversation about owed days.
Plan for cover, not just absence
The Year Calendar is for spotting clashes early. If three people in the same team all want the last week of August, you want to know in March, not on 20 August.
What this tracker does well
It is free, and it stays free
No trial timer, no upgrade prompts inside the file.
Bank holidays are correct
2026 and 2027 dates are baked in for all three UK regions.
It pro-rates automatically
Mid-year joiners and leavers are calculated for you.
It works in both Excel and Sheets
One file, two homes. Same formulas. Same dropdowns.
The formulas are readable
If you want to know how a number is calculated, click the cell. No hidden macros.
It survives small teams
Built for up to about 30 employees. After that it gets unwieldy.
What this tracker cannot do
Honest section. Every spreadsheet leave tracker · including ours · runs into the same wall eventually. Knowing where the wall is up front saves you a lot of pain.
Two people editing at once breaks it
Excel has limited co-authoring; Sheets is better but still has overwrite races. The file becomes "the latest save wins," which is not the same as a record of truth.
No mobile request flow
Staff cannot request leave from their phone in the way they can from an HR app. They have to message someone and wait.
No approval workflow
"Approved" is just a word in a cell. There is no audit log of who approved what, when, or what the request looked like before it was approved.
Permissions are all-or-nothing
You either share the whole file or you do not. There is no "Alice can see her own balance but not Ben's salary band."
Year-end rollover is manual
You have to copy the file, rename it, clear the leave log, carry forward balances, and update the year. Easy to forget; easy to mess up.
No notifications or reminders
Nobody is automatically told when a request is filed, approved, or about to clash with a colleague's leave.
Accruals are a snapshot, not live
The spreadsheet shows entitlement for the whole leave year. It does not "drip-feed" 1/12 of the allowance each month · relevant if a new starter leaves shortly after joining.
When you will know it is time to move on
We won't turn this into a sales pitch but there are obvious limitations of spreadsheet tracking. In our experience once you get to four or more people in your team these things start to happen:
- You have "I thought I had booked that off" misunderstandings.
- The owner of the file is on holiday and nobody else can find it.
- Two people both update the file in the same afternoon and one of their changes vanishes.
- You crossed 15-20 employees and the rows are getting hard to scan.
- You have multiple managers approving leave and there is no way to keep them in sync.
- Year-end rollover took half a day and you found three errors afterwards.
- You want staff to be able to see their own balance without asking.
- You have started thinking about TOIL, hours-based contracts, or shift patterns.
If any of the above resonate and you're weighing the free file against annual leave tracking software, our roundup of the free staff holiday booking systems compares every free option honestly. Or go straight for a free trial of Book Time Off. There's no card required and you'll be set up in minutes so nothing to lose.
→ Start free trialFor the full story of what breaking point actually looks like (with screenshots of the alternative), read how to track annual leave without a spreadsheet.
Sources
| What | Where |
|---|---|
| Statutory holiday entitlement (5.6 weeks) | gov.uk/holiday-entitlement-rights |
| Working Time Regulations 1998 | legislation.gov.uk · WTR 1998 |
| Calculating holiday entitlement (worked examples) | gov.uk/calculate-your-holiday-entitlement |
| ACAS guidance on holidays and absence | acas.org.uk/checking-holiday-entitlement |
| UK bank holidays · England, Wales, Scotland, NI | gov.uk/bank-holidays |
| Scottish bank holidays (incl. 15 June 2026 World Cup day) | gov.scot/publications/bank-holidays |