How Do You Build a Leave Tracker in Google Sheets?

Every small team hits the same wall at roughly the same size. Somewhere between five and thirty people, the informal system for tracking who is off and when quietly stops working. Requests live in email threads, the shared calendar shows half of them, and nobody can answer the one question that actually matters: how many days does this person have left?

A leave tracker in Google Sheets solves that for a surprising number of teams, and it solves it for free. You do not need a dedicated HR platform to know that someone has taken fourteen of their twenty-five days. You need one spreadsheet with a request log, a balance calculation and a view your managers will actually open.

This guide builds that tracker from an empty sheet. Every formula is included, and each step explains not just what to type but why the tracker is structured that way, because the structure is what determines whether it still works in eighteen months or collapses the first time someone changes jobs mid-year.

What is the fastest way to build a leave tracker in Google Sheets?

Create three tabs: an Employees tab holding each person's annual entitlement, a Requests tab where every booking is logged as one row, and a Dashboard tab that uses SUMIFS to total the days taken per person and subtract them from the entitlement. Add conditional formatting to flag anyone whose remaining balance drops below zero, and protect the formula columns so nobody overwrites them.

That three-tab separation is the whole trick. Most homemade trackers fail because they try to be a calendar and a ledger at the same time, usually as a grid of employees down the side and days across the top. That grid looks intuitive and becomes unusable the moment you need a second year of data or a half day.

One row per request is boring and it scales. Everything else is arithmetic on top of it.

It also means the tracker answers questions you did not design it for. Once every booking is a row with a date, a person and a type, you can ask how much sick leave the team took in Q3, or whether January is really your worst month for coverage, without restructuring anything.

Not sure whether your site is being found by the people searching for tools like this? Get a free SEO audit and see exactly where you stand.

Why the three-tab structure matters

Before typing anything, it is worth understanding what each tab is responsible for, because mixing those responsibilities is the single most common reason a tracker has to be rebuilt from scratch.

The Employees tab is your source of truth

This tab holds one row per person and changes rarely. At minimum it needs a name, a start date, an annual entitlement in days, and any days carried over from last year. Because everything downstream looks up against the name in this tab, it has to be the only place a name is typed by hand.

Add a status column as well, marked active or left. When someone leaves you do not delete their row, because deleting it silently breaks every historical calculation that referenced it. You change the status and let the dashboard filter them out.

Resist the urge to add columns here for anything that changes during the year. Job title, manager and team all belong in your people records rather than in a tracker, because every extra column is one more thing that has to be kept current for the tracker to stay trustworthy.

The Requests tab is an append-only log

Each booking is one row: who, start date, end date, number of days, type of leave, and approval status. Rows get added; they never get restructured. If a request is cancelled you mark it cancelled rather than deleting the row, which means you keep a record of what was requested and by whom.

Separating leave type matters more than people expect. Annual leave, sick leave, unpaid leave and parental leave all draw against different pools or no pool at all, and a tracker that lumps them together will happily tell you someone has overspent their holiday when in fact they had flu for a week.

The Dashboard tab is read-only output

Nothing on this tab is typed. Every cell is a formula pulling from the other two tabs. That constraint is what keeps the numbers trustworthy: if a figure looks wrong, the cause is in the data, never in a manual override somebody made eight months ago and forgot to mention.

How do you build it step by step?

Work through these in order. The whole thing takes about forty minutes the first time.

Build it with two or three real people first rather than your whole team. Entering a handful of genuine bookings surfaces policy questions immediately, and it is far cheaper to discover that your half-day rule does not fit the structure when you have three rows than when you have three hundred.

1. Open a new spreadsheet and rename the three tabs to Employees, Requests and Dashboard.
2. On the Employees tab, create headers in row 1: Name, Email, Start Date, Annual Entitlement, Carried Over, Status.
3. Fill in one row per person. Enter entitlement as a plain number of days, not text, or the arithmetic downstream will fail silently.
4. On the Requests tab, create headers: Request ID, Name, Leave Type, Start Date, End Date, Days, Status, Notes.
5. In the Name column, add data validation pointing at the Employees name range. Select the column, choose Data then Data validation, and set the criteria to a dropdown from a range. This stops typos from orphaning a request.
6. Do the same for Leave Type with a fixed list: Annual, Sick, Unpaid, Parental, Other. And for Status: Pending, Approved, Rejected, Cancelled.
7. In the Days column, calculate working days rather than trusting anyone to count. Use =NETWORKDAYS(D2,E2) which excludes weekends automatically.
8. To exclude public holidays too, add a small Holidays tab listing each date, then use =NETWORKDAYS(D2,E2,Holidays!A:A) instead.
9. On the Dashboard tab, list your employee names in column A with =FILTER(Employees!A2:A,Employees!F2:F="Active") so leavers drop off automatically.
10. In column B, pull the entitlement: =SUMIFS(Employees!D:D,Employees!A:A,A2)+SUMIFS(Employees!E:E,Employees!A:A,A2). That adds the carried-over days to the annual figure.
11. In column C, total approved annual leave taken: =SUMIFS(Requests!F:F,Requests!B:B,A2,Requests!C:C,"Annual",Requests!G:G,"Approved").
12. In column D, calculate the remaining balance with =B2-C2.
13. In column E, show pending days so managers can see what is in the queue: =SUMIFS(Requests!F:F,Requests!B:B,A2,Requests!C:C,"Annual",Requests!G:G,"Pending").
14. Select column D and add conditional formatting. Set a rule for less than 0 with a red fill, and a second rule for less than 3 with amber. Overdrawn balances now announce themselves.
15. Protect columns B through E on the Dashboard. Right-click the range, choose Protect range, and restrict editing to yourself.

Once step fifteen is done the tracker is live. Add a request on the Requests tab and watch the dashboard update.

Before you share it, test the edge cases deliberately. Book a single day, a request spanning a weekend, a request crossing a public holiday, and one that deliberately exceeds someone's remaining balance. If all four produce the numbers you expect, the formulas are sound.

Then add a Google Form pointed at the Requests tab. Form submissions append rows without giving anyone edit access to the sheet, which is the difference between a tracker that stays accurate and one that degrades quietly over a few months.

Want the same clarity about your organic traffic that this tracker gives you about leave? Request a free SEO audit and get a prioritised list of fixes.

How do you handle half days and carry-over?

These two cases account for most of the questions teams have after building their first tracker, and both are easier than they look.

For half days, do not fight NETWORKDAYS. Leave the calculated column as it is and add a manual Adjustment column beside it, then make Days a formula that subtracts the adjustment. Someone taking a half day on a single date gets an adjustment of 0.5. It is visible, auditable and takes two seconds to enter.

Carry-over is a policy question disguised as a formula question. Decide first whether unused days expire, roll over in full, or roll over up to a cap. A cap is the most common policy and the easiest to express: in the Carried Over column on the Employees tab, enter =MIN(5,previous_year_balance) so nobody banks more than five days.

The important discipline is that carry-over is entered once at the start of the year, by hand, as a deliberate act. Trying to make it calculate itself across years is where homemade trackers turn into unmaintainable formula chains.

When the year rolls over, duplicate the whole spreadsheet rather than clearing it. Last year's file becomes a static record you can point at if anyone queries a balance, and the new copy starts clean with carry-over figures entered once.

When is a spreadsheet the right tool?

A Google Sheets tracker genuinely is the right answer under a specific set of conditions, and recognising those honestly will save you from either overbuying software or outgrowing a spreadsheet without noticing.

Use a spreadsheet when your headcount is under roughly thirty, when your leave policy is simple enough to explain in two sentences, when one person owns the tracker and maintains it, and when you do not need employees to self-serve their own balances.

It also works well as a deliberate stopgap. If you know you will buy an HR system next year but need something credible now, a sheet gets you through with a clean data set you can import later.

The self-service point is the one that usually forces the decision. A spreadsheet where thirty people all have edit access is not a tracker, it is a liability. You can mitigate this with a Google Form feeding the Requests tab, which lets people submit without touching the sheet, and that combination stretches the useful life considerably.

The clearest signal that you have outgrown it is not headcount but attention. When the person who owns the tracker starts spending more than an hour a month reconciling it, the spreadsheet has stopped saving money.

What are the real limitations?

Being clear-eyed about these matters, because each one is a genuine failure mode rather than a minor inconvenience.

There is no audit trail worth the name. Google Sheets version history tells you a cell changed but makes reconstructing who approved what on which date genuinely painful. If you operate anywhere that might require you to evidence leave decisions, this alone rules a spreadsheet out.

Accrual-based policies are awkward. If people earn days monthly rather than receiving an annual allocation, you need a formula referencing today's date, and any such formula recalculates the historical record every time it opens. Anyone auditing last quarter sees this quarter's numbers.

There is no approval workflow. Nothing stops a request being marked Approved by the person who submitted it, and nothing notifies a manager that a request is waiting. A Google Form plus a notification rule gets you part of the way, but it is a convention rather than a control.

Concurrent editing causes real damage. Two people adding rows at once is fine; two people sorting the Requests tab at once is not, and a mis-sorted log with formulas referencing fixed rows produces wrong numbers that look entirely plausible.

Finally, it does not scale past a point that arrives sooner than expected. At around fifty employees the dashboard recalculation becomes noticeably slow, and the number of policy exceptions you are tracking in your head exceeds what the sheet can express.

None of these are reasons to avoid building one. They are reasons to know in advance which of them will eventually force you to move, so the move is a decision you make rather than a crisis you react to.

Running a lean team and doing your own marketing too? Start with a free SEO audit and find out which pages are worth your limited time.

How does this compare to the alternatives?

There are three realistic alternatives, and the honest answer is that each wins a different situation.

Dedicated HR platforms are the right call once headcount or compliance demands an audit trail and self-service. They handle accruals, approvals and public holidays across regions without you maintaining a formula. The cost is per-employee per-month and the switching cost is real, so the decision point is genuinely about whether you have crossed the size threshold rather than about features.

A template rather than a build from scratch gets you running in ten minutes. The trade-off is that you inherit somebody else's assumptions about your leave policy, and unpicking a template that almost fits usually takes longer than building the three tabs above. If your policy is completely standard, a template wins. If it has any quirk, build it.

Shared calendars are not an alternative, though they are often treated as one. A calendar answers who is off on Thursday. It cannot answer how many days someone has left, which is the question a tracker exists to answer. Use both: the sheet as the ledger, the calendar as the view.

Microsoft Excel deserves a mention for teams already inside Microsoft 365. The formulas above work almost unchanged, and Excel handles large data sets more gracefully. Google Sheets wins on simultaneous access and on feeding data in from a form without extra licensing.

One combination worth avoiding is a tracker that lives in two places. Teams sometimes keep a spreadsheet alongside a trial of an HR tool and reconcile between them. Pick one as authoritative and let the other be a copy, because two systems that disagree are worse than either on its own.

Final Thoughts

A leave tracker is one of the few genuinely satisfying spreadsheets to build, because the requirement is precise and the output is used immediately. The three-tab structure, one row per request, and a dashboard that is pure formula will carry a small team for years.

The discipline that keeps it working is resisting the temptation to make it clever. Every manual override, every merged cell, every formula that reaches across three tabs to handle one person's unusual arrangement is a small debt that comes due when you are least able to pay it. Keep the log boring and let the dashboard do the thinking.

If you are building out a set of internal spreadsheets, it is worth setting them up consistently from the start. Our guide on how to make a calendar template in Google Sheets covers the date-handling patterns that pair naturally with the tracker above, and the two together give you both the ledger and the view.

Ready to give your website the same attention you just gave your spreadsheets? Get your free SEO audit and see what is holding your rankings back.