PeopleMuster

Leave tracker spreadsheet (PTO tracker for Excel)

A leave tracker spreadsheet answers one question fast: how many days does each person have left? The version below uses four tabs. Paste each block into its own sheet, set the quotas once, and every approved request updates the balance.

Use it when your team is small enough that one person approves leave and keeps the record, and you want the numbers out of email threads. Start it at the beginning of your leave year, so used days count from zero. Once more than one manager approves requests, the balance tab is the one that drifts first, so review it monthly.

TAB 1: LEAVE TYPES
Leave type | Days per year | Paid or unpaid | Needs approval from | Notice needed
Annual | [ANNUAL DAYS] | Paid | [APPROVER ROLE] | [NOTICE DAYS] days
Casual | [CASUAL DAYS] | Paid | [APPROVER ROLE] | Same day
Sick | [SICK DAYS] | Paid | [APPROVER ROLE] | Same day
Unpaid | 0 | Unpaid | [APPROVER ROLE] | [NOTICE DAYS] days
[OTHER LEAVE TYPE] | [DAYS] | [PAID OR UNPAID] | [APPROVER ROLE] | [NOTICE DAYS] days

TAB 2: QUOTAS (one row per person per leave type)
Employee | Department | Leave year | Leave type | Allocated days | Carried in
[EMPLOYEE NAME] | [DEPARTMENT] | [LEAVE YEAR] | Annual | [ANNUAL DAYS] | [CARRIED DAYS]
[EMPLOYEE NAME] | [DEPARTMENT] | [LEAVE YEAR] | Casual | [CASUAL DAYS] | 0
[EMPLOYEE NAME] | [DEPARTMENT] | [LEAVE YEAR] | Sick | [SICK DAYS] | 0

TAB 3: REQUEST LOG (one row per request, never edited after approval)
Request ID | Employee | Leave type | First day | Last day | Working days | Status | Approved by | Date decided
[REQUEST ID] | [EMPLOYEE NAME] | Annual | [FIRST DAY] | [LAST DAY] | [WORKING DAYS] | Approved | [APPROVER NAME] | [DECISION DATE]
[REQUEST ID] | [EMPLOYEE NAME] | Sick | [FIRST DAY] | [LAST DAY] | [WORKING DAYS] | Approved | [APPROVER NAME] | [DECISION DATE]
[REQUEST ID] | [EMPLOYEE NAME] | Casual | [FIRST DAY] | [LAST DAY] | [WORKING DAYS] | Pending | |

TAB 4: BALANCES (formulas, not typed numbers)
Employee | Leave type | Allocated | Used | Remaining | % used
[EMPLOYEE NAME] | Annual | =SUMIFS(Quotas!E:E,Quotas!A:A,A2,Quotas!D:D,B2)+SUMIFS(Quotas!F:F,Quotas!A:A,A2,Quotas!D:D,B2) | =SUMIFS('Request log'!F:F,'Request log'!B:B,A2,'Request log'!C:C,B2,'Request log'!G:G,"Approved") | =C2-D2 | =IF(C2=0,0,D2/C2)
[EMPLOYEE NAME] | Casual | (copy the row above down) | | |
[EMPLOYEE NAME] | Sick | (copy the row above down) | | |

TAB 5 (OPTIONAL): WHO IS OUT THIS MONTH
Employee | [DAY 1] | [DAY 2] | [DAY 3] | [DAY 4] | [DAY 5]
[EMPLOYEE NAME] | | L | L | | H
Key: L = on approved leave, H = company holiday

What to put in each blank

[ANNUAL DAYS]
Enter the yearly allowance from your leave policy. Use whole or half days, never hours, so the maths stays simple.
[APPROVER ROLE]
Name a role, such as line manager, instead of a person. The tracker then survives a manager leaving.
[CARRIED DAYS]
Enter days brought forward from last year, if your policy allows it. Put 0 when it doesn't.
[WORKING DAYS]
Count working days only. Skip weekends and company holidays, or a week off will cost someone seven days.
[REQUEST ID]
Number requests in order, like 2026-014. A fixed ID lets you point at one row when someone asks why a balance moved.
[LEAVE YEAR]
Write the year the quota belongs to. Start a fresh set of quota rows each year and keep the old ones for reference.

Before you send it

  • Paste each block into a separate sheet and name the sheets Leave types, Quotas, Request log and Balances. The formulas in Tab 4 look up those exact names.
  • Never type a number into the Remaining column. A typed balance stops updating, and nobody notices until someone is told they have days they don't.
  • Log rejected and cancelled requests too, with their status. The formula counts Approved rows only, so the history costs you nothing.
  • Lock Tabs 2 and 4 for everyone except the person who owns leave. Most spreadsheet errors come from a helpful edit to a balance cell.

PeopleMuster keeps a quota per person and leave type, and updates used and remaining days as each request is approved

Questions people ask about this letter

How do I track PTO in Excel?

Keep three things apart: the yearly quota per person, a log of every request, and a balance tab that calculates used and remaining days from the log. The template above does this in 4 tabs with SUMIFS formulas. You only ever type into the quota and log tabs.

Does this leave tracker work in Google Sheets?

Yes. The SUMIFS and IF formulas in Tab 4 work the same in Google Sheets and Excel. Paste each block, split the text on the | character, and rename the tabs to match the formulas.

Should I track leave in days or hours?

Track in days, with half days as 0.5, unless your team works very uneven shifts. Days match how people ask for leave and how most policies are written. Hours add a conversion step on every request.

How often should the leave balances be checked?

Check them once a month and before the leave year ends. A monthly look catches a request logged against the wrong type while it's still easy to fix.

Other templates