Tracking PTO in Google Sheets works well for small to mid-size teams where a dedicated HR platform feels like overkill. Done right, your leave tracker handles balance calculations, leave requests, and a team calendar view — all in one shareable spreadsheet.
Your Leave Log
One row per leave request. Columns: employee name, leave type (PTO, Sick, Personal, Unpaid), start date, end date, number of days, status (Requested, Approved, Denied), and manager notes.
Number of days (excluding weekends):
=NETWORKDAYS(D2, E2)
Where D2 is start date and E2 is end date. NETWORKDAYS automatically excludes Saturdays and Sundays. If you have company holidays to exclude, add a holiday list and include it as the third argument.
Balance Tracking
On a separate Employee Balances sheet, list each employee with their annual PTO allowance, days taken, and remaining balance.
Days taken:
=SUMIFS(LeaveLog!F:F, LeaveLog!A:A, A2, LeaveLog!B:B, "PTO", LeaveLog!G:G, "Approved")
Where F is number of days, A is employee name, B is leave type, and G is status. Only count approved requests.
Remaining balance:
=B2-C2
Where B2 is annual allowance and C2 is days taken. Apply conditional formatting: red if balance is below 2 days, amber if below 5.
Team Availability Calendar
Add a calendar view showing which employees are out on each day. Use COUNTIFS to count employees on leave each day:
=COUNTIFS(LeaveLog!D:D, "<="&B1, LeaveLog!E:E, ">="&B1, LeaveLog!G:G, "Approved")
Where B1 is the date in the calendar header. This shows how many people are out each day — useful for spotting days where the team is understaffed.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have employee and leave request data already in the spreadsheet.
"I have a leave request log on Sheet 1 and an employee list on Sheet 2 with annual PTO allowances. Build a PTO tracker with balance calculations per employee, a team calendar showing coverage gaps, and flags for employees with less than 2 days remaining."
SheetXAI reads both sheets, calculates balances, builds the calendar view, and adds the balance flags.
Example 2: Your HR data is in your HRIS.
"Pull employee leave requests and balances from our HRIS and build a PTO tracker showing remaining balances, pending requests, and a team availability view for the next 30 days."
SheetXAI connects to your HRIS and builds the full leave tracker.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Headcount Tracker in Excel, How to Create an Employee Schedule in Google Sheets, and Google Sheets AI Guide.
