Free PTO Tracker Excel Template and Interactive Tool

The interactive PTO tracker below lets you add employees, enter their annual balance, and log vacation, sick, and personal days used. Remaining balances calculate automatically and update in real time. When you are done, click Copy to Excel to paste the full tracker -- with headers and all data rows -- directly into a spreadsheet.

Below the tool you will find the exact Excel formulas to build a multi-employee PTO tracker from scratch, the structure for a date-by-date usage log, common tracking mistakes to avoid, and answers to the most frequent PTO tracking questions.

Free PTO Tracker

Add employees, enter their PTO balance and days used by type. Remaining balance updates automatically. Green = more than 5 days remaining, yellow = 5 days or fewer, red = over balance.

How to Build a PTO Tracker in Excel

The interactive tool above handles the math automatically, but if you want to build your own PTO tracker in Excel from scratch, the structure below is what works for small businesses without needing VBA or complex formulas.

The Two-Sheet Structure

The most reliable multi-employee PTO tracker uses two sheets: a Summary sheet with one row per employee showing balances, and a Log sheet where each PTO entry is recorded as a separate row. The Summary sheet pulls from the Log using SUMIF formulas. This separates the summary view that managers check daily from the running usage record that feeds it.

Summary sheet columns: Employee Name | Annual PTO | Vacation Used | Sick Used | Personal Used | Total Used | Remaining Balance

Log sheet columns: Employee Name | Date | Leave Type | Days Taken | Notes

The Formulas

In the Summary sheet, assuming employee names are in Column A starting at Row 2 and your annual PTO allocation is in Column B:

Vacation Used (Column C):
=SUMPRODUCT((Log!A$2:A$500=A2)*(Log!C$2:C$500="Vacation")*Log!D$2:D$500)

Sick Used (Column D):
=SUMPRODUCT((Log!A$2:A$500=A2)*(Log!C$2:C$500="Sick")*Log!D$2:D$500)

Personal Used (Column E):
=SUMPRODUCT((Log!A$2:A$500=A2)*(Log!C$2:C$500="Personal")*Log!D$2:D$500)

Total Used (Column F):
=C2+D2+E2

Remaining Balance (Column G):
=B2-F2

Every time someone enters a new row on the Log sheet with the employee's name and leave type, the Summary sheet updates automatically. The log also gives you a date-by-date audit trail -- useful for verifying balances and for any payroll or HR disputes.

Adding a Leave Type Dropdown to the Log

Free-text leave type entries ("vac," "Vacation," "vacation day") break SUMIF matching. Force consistency by adding a dropdown to the Leave Type column: select the cells in that column, go to Data > Data Validation > Allow: List, and enter Vacation,Sick,Personal,Holiday,Other as the source. Now every entry uses the same label and the formulas match reliably.

Calculating PTO Accrual Mid-Year

If you need to track earned-but-not-yet-allocated PTO based on hire date and accrual rate, add an "Accrued to Date" column to the Summary sheet:

=MIN(Annual_PTO, (TODAY()-Hire_Date)/365 * Annual_PTO)

This calculates how many days the employee has earned so far in the year based on how much of the year has elapsed since their hire date, capped at the annual maximum. For new hires who started mid-year, this prevents the tracker from showing a full annual balance before it has been earned.

For more detail on accrual methods including per-hour accrual, front-loaded PTO, and milestone-based accrual, see the PTO Accrual Explained guide here.

What a PTO Tracker Should Include

Common PTO Tracking Mistakes in Excel

Tracking PTO in Hours in One Column and Days in Another

Mixing units in the same tracker is the most common source of PTO balance errors. Decide on hours or days and use that unit consistently across every field, every employee, and every entry in the log. If your policy states PTO in days but your payroll system works in hours, convert at a fixed rate (typically 8 hours per day) and document that conversion explicitly in the tracker so whoever maintains it after you can replicate it correctly.

Not Tracking Sick Leave Separately From PTO

Many states -- including California, Colorado, New York, Washington, and Massachusetts -- require employers to track accrued and used paid sick leave separately from general PTO, even when they come from the same bank. Combining them in a single "PTO Used" column creates a compliance gap if the company is ever audited for sick leave compliance. Add a separate sick leave column even if your policy uses a combined PTO bank.

Letting Employees Self-Report Their Remaining Balance

The remaining balance should always be a formula that calculates from logged entries -- never a manually entered number. Employees who self-report remaining PTO will occasionally inflate it, not usually out of dishonesty but because they are counting from memory. When they take time off based on a self-reported inflated balance, the reconciliation at year end becomes a difficult conversation. The formula is the source of truth.

Not Resetting the Tracker at Year End

Define a clear process for year-end rollover before it happens: which employees carry over unused PTO (and how much), which employees have accrual caps that need to be enforced, and how the new tracker year initializes. The cleanest approach is to copy the Summary sheet at year end, archive it as the prior year record, and start a fresh Log sheet for the new year with carryover balances entered as the opening balance in the Annual PTO column.

No Backup or Version History

A PTO tracker in Excel on a shared drive with no version history is one accidental delete away from losing the full year of records. Keep the tracker in Google Drive (which has full version history) or SharePoint, or export a dated snapshot monthly. PTO balance disputes happen -- you need to be able to show what the balance was on any given date.

When to Move Beyond Excel for PTO Tracking

An Excel PTO tracker serves most small businesses well through their early growth. The signals that it is no longer sufficient are predictable. The first is reconciliation time -- when a manager or HR person is spending more than 30 minutes per pay period maintaining the tracker, it is costing more than software would. The second is balance disputes -- when employees are regularly questioning their balance and HR cannot quickly produce the audit trail that resolves the dispute, the log structure is insufficient. The third is manager visibility -- when managers have to email HR to find out whether an employee has PTO remaining before approving a request, the friction is high enough to warrant a system where managers can check balances directly.

At that point, Updoot handles PTO tracking, approval workflows, and balance visibility for $5/user/month -- no spreadsheet maintenance required.