PTO Tracker Excel Template
A PTO tracker spreadsheet works best as a ledger: one row per change, with the employee, the date, the type of entry (starting balance, accrual, time off, adjustment), the hours and the status, plus a running balance per employee. Download the free CSV template below, open it in Excel or Google Sheets, and add the two formulas explained on this page. It suits one person or a small team; past a handful of employees, a shared system is easier to keep right.
Download the PTO tracker template (CSV)
The file contains synthetic example rows for two employees, A.R. and J.M. Delete them before adding your own data. It has no formulas or macros, so it opens safely anywhere.
Columns
| Column | What goes in it |
|---|---|
| Employee | Initials or a name, the same spelling every time |
| Date | The date the change takes effect, as YYYY-MM-DD |
| Entry type | Starting balance, Accrual, Time off or Adjustment |
| Hours | Positive to add, negative to subtract (time off is negative) |
| Status | Effective, Approved (future time off) or Pending |
| Running balance | Calculated by a formula; leave it empty |
Set it up in Excel or Google Sheets
- Open the CSV and save it as an Excel workbook or a Google Sheet.
- Sort by Employee, then by Date.
- Add the running balance. In F2, enter
=SUMIFS($D$2:D2,$A$2:A2,A2,$E$2:E2,"<>Pending")and fill it down. It adds every earlier row for the same employee, leaving pending requests out. - Add a summary. On a second sheet, list each employee and use
=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!E:E,"Effective")for today's balance. RenameSheet1to your data sheet's name. - Add a row for every change. Never overwrite a balance; correct mistakes with an Adjustment row so the history stays visible.
Synthetic example. With the sample rows, A.R. starts with 40 hours, accrues 3.08 twice and has 8 hours approved: the running balance on the last row is 38.16 hours.
Where spreadsheets go wrong
- Overwriting a single "balance" cell instead of adding rows, so nobody can explain the number later.
- Deducting a request twice: once when approved and again when taken. Record it once and change its status.
- Mixing hours and days in the same column. Convert days with the PTO hours to days calculator first.
- Copying formulas that stop one row short, so new rows are not counted.
- Several people editing the same file without knowing who changed what.
The time off approval workflow lists what to record for every balance change.
Frequently asked questions
How do I track PTO in Excel?
Keep one row per change with the employee, date, type, hours and status, and calculate the balance with a SUMIFS formula per employee. Do not overwrite a balance cell; add an adjustment row instead.
Is there a free PTO tracker template?
Yes. The CSV on this page is free and works in Excel, Google Sheets and Numbers. For a single person, the browser-based PTO tracker does the math for you and exports a CSV.
Can I track my team's vacation in Google Sheets?
Yes, with the same columns and formulas. Share the sheet only with the people who need balances, and keep reasons for absence out of it.
When is a spreadsheet no longer enough?
When several people approve requests, employees ask for their balance often, or you spend time each pay period reconciling numbers. That is the point where a shared system with approvals saves time.