PTO & Employee Leave Tracker Template (Excel + Google Sheets)

What's included: policies, employees, requests, and balances in one file

The workbook has six sheets: Instructions, Policies, Employees, Requests, Balances, and Dashboard. Policies is where you configure up to three leave types, typically PTO, sick, and personal, each with its own accrual rate, annual cap, carryover limit, and seniority tiers.

Employees lists each person's hire date plus the days carried over from a previous system, per policy (capped automatically by each policy's carryover max). Requests is your intake log: employee, leave type, start and end date, with working days calculated automatically using NETWORKDAYS and a status column for Approved or Pending. Balances then rolls all of that up per employee, per policy, showing accrued year-to-date, days taken, and days remaining.

As with every TableTemplates file, entry cells are validated and shaded pale yellow, and the underlying formulas are protected so the sheet stays intact as your team grows.

How automatic PTO accrual works (monthly, with tenure tiers)

Set a monthly accrual rate in the Policies sheet, along with an annual cap and up to two tenure tiers, for example an extra day after two years and another after five. If your policy is written per pay period, convert it to a monthly rate (for example, 26 biweekly periods of 0.58 days is about 1.25 days a month). Balances then calculates each employee's accrued total as the lesser of the annual cap or their months of service multiplied by the accrual rate, plus any tenure bonus they've earned, then adds capped carryover from the prior year.

That accrual engine is the core gap in this category: the free tools and even the highest-rated paid Etsy dashboard we reviewed all require someone to manually enter or update a lump PTO balance. None of them calculate accrual from a hire date, month by month. This template does that math for you, and recalculates it automatically as time passes.

Multi-policy setup: PTO, sick leave, and personal days

Most spreadsheets treat leave as one flat pool with a color-coded label. Here, PTO, sick leave, and personal days are genuinely separate policies, each with its own accrual rate, cap, and carryover rule, so an employee can be accruing PTO at one rate while sick leave accrues at another and personal days don't accrue at all if that's how your policy works.

This matters for any business that has to actually administer distinct leave types under different rules rather than track a single vacation balance. Balances reports each policy separately (accrued, taken, left), so you can see PTO usage apart from sick leave without building a second spreadsheet.

Carryover caps and the annual dashboard

Each policy has its own carryover cap, so unused days above the cap don't roll into the new year automatically inflating balances; the accrual formula applies the cap before adding carryover to new accrual. The Dashboard visualizes the year as a 12-month calendar with a conditional-formatting heatmap showing days taken by employee and month, alongside KPIs for total days taken this year, pending requests and average PTO left.

That combination, per-policy carryover math plus a visual annual calendar, is not something we found bundled together in any competitor: free tools that let you set a fiscal-year start don't automate carryover, and the one paid dashboard with overlap monitoring doesn't automate accrual or carryover at all.

How this compares to free trackers and VBA-based Etsy dashboards

The free Excel trackers commonly found online (generated leave planners, static Smartsheet downloads) are essentially calendars with color-coded absence types. They're fine for logging days off, but every balance is entered by hand, there's no concept of an accrual rate, and there's no policy-level distinction between PTO, sick, and personal time.

The most feature-rich paid competitor we reviewed is a VBA-driven Excel dashboard, priced around $20, with overlap monitoring and support for up to 100 employees. It's genuinely capable, but it's explicitly Excel-only: it requires macros to be enabled, doesn't work on Google Sheets, and has limited Mac support with no mobile compatibility at all.

This template closes both gaps at once: it automates accrual, tiers, and carryover so nobody is hand-entering balances, and it does it with plain formulas, no macros, so the exact same file opens and calculates correctly in Excel, Excel for Mac, and Google Sheets.

Excel vs Google Sheets, and who this is for

You receive both a .xlsx build for Excel and a matching Google Sheets version, using the same SUMIFS, COUNTIFS, INDEX/MATCH, IFERROR, and EDATE formulas throughout so accrual, carryover, and NETWORKDAYS-based day counts calculate identically either way. Choose whichever platform your team already uses for HR paperwork.

It's built for small businesses and HR-of-one teams who need to administer real accrual rules, not just log absences, but don't want to buy HR software or fight with a macro-locked Excel file that half the team can't open on their laptop or phone.