Most free leave-tracker spreadsheets do one of two things: they are too basic to be useful, or so over-engineered that nobody trusts them. We have tried to land in between. This one keeps the formulas readable, handles the things UK employers actually trip over, and tells you up front what it cannot do.
What is in the spreadsheet
Seven sheets, 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: rows are people, columns are weeks. |
| Bank Holidays | 2026 and 2027 dates for E&W, Scotland and NI. Active region auto-filtered. |
The whole thing is held together by formulae. Change a date on the Setup sheet, and entitlements, used days and the calendar all update. Add a row to the Leave Log, and the Dashboard changes. There are no macros, no scripts, no add-ons.
If a cell is yellow, you can change it. If it is blue, leave it alone - it works out the answer for you. We have used this convention throughout.
Setting 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 - 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 - not 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.
Set your default annual allowance
The default is 20 days - the UK statutory minimum (5.6 weeks for a 5-day worker), excluding bank holidays. Bump it up to 25 or 28 if you are 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. 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 - see the limits section below.
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 your employer 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 is, in plain English: 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. Common choices: 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.
Of the 5.6 weeks, the first 4 weeks come from the EU Working Time Directive and have stricter carry-over rules. The 1.6 weeks comes from UK regulations and can be carried over by agreement. For most small employers this distinction does not matter day-to-day, but it shows up in tribunal cases.
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.
Considerations 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.
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 allowed but quickly turns into a maths assignment. Avoid unless you are a very small team and intend to stay that way.
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. Cover: 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 without a single owner is a spreadsheet that drifts. Pick one person who is responsible for entering approved leave, chasing pending requests, and rolling the file over at year end. In a small business this is often the founder or the office manager.
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.
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; do not let it become a December panic.
- 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
None of these signals on their own mean a spreadsheet is wrong for you. But if you are nodding at three or more, the spreadsheet is starting to cost more than it saves.
- You have had at least one "I thought I had booked that off" misunderstanding.
- 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.
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 |