How to Make a Leave Tracker in Excel: PTO Balance With Formulas

shape
shape
shape
shape
shape
shape
shape
shape
How to Make a Leave Tracker in Excel: PTO Balance With Formulas - featured image

A leave tracker, also called a PTO tracker, records how many days of paid time off each employee has, how many they have taken, and how many remain. Small businesses and teams often keep this in Excel because it is free, flexible and easy to share. A good tracker has two parts: a log where each leave request is entered once, and a summary where formulas count the days and show the balance. This guide shows how to build both, how to count working days without weekends and holidays, how to total leave by type, how to warn when a balance goes negative, and how to protect the file. A three-person example with numbers you can check by hand is used throughout.

Updated November 2026

In this guide

  1. Plan the two sheets
  2. Example data
  3. Step 1: build the Log table
  4. Step 2: add drop-downs
  5. Step 3: count working days
  6. Step 4: set up the Summary sheet
  7. Step 5: calculate the remaining balance
  8. Step 6: flag problems with a status
  9. Step 7: avoid overlapping leave
  10. Step 8: add a monthly view
  11. Carry over and new year setup
  12. Reports for managers
  13. Protect and share the file
  14. Common mistakes
  15. Practice problems
  16. Quick recap

Plan the two sheets

Use two sheets. The first, called Log, holds one row for every leave request with the columns Employee, Type, Start, End and Days. The second, called Summary, holds one row for every employee with the columns Employee, Entitlement, Taken, Remaining and Status. A third small sheet called Holidays lists the public holidays. Keeping the log separate from the summary means you enter each request only once, and the summary updates by itself, which prevents mistakes.

Example data

Suppose there are three employees: Ali with an entitlement of 20 days, Sara with 18 days and Omar with 15 days. Ali takes 5 days of annual leave in March and 3 days of sick leave in June. Sara takes 10 days of annual leave in July. Omar takes 4 days in April and 12 days in August. We will calculate the balance for each person. The expected annual leave balances are 15 for Ali, 8 for Sara and negative 1 for Omar, who is over his entitlement by one day.

Step 1: build the Log table

On the Log sheet, type the headers Employee, Type, Start, End and Days in A1:E1. Select the headers and press Ctrl+T to make a table, and name it LeaveLog in Table Design. A table grows by itself when you add a request, and the formulas in the Days column fill down automatically. Format the Start and End columns as dates. Our table guide explains the steps and the benefits in more detail.

Step 2: add drop-downs

Add a drop-down for Employee and Type so that names and leave types are always spelled the same way. Select the Type column, go to Data, Data Validation, choose List and enter Annual,Sick,Unpaid. For the Employee column, point the list to the names on the Summary sheet using a named range called Staff. Consistent spelling is important, because the formulas that total the days match the text exactly. See our drop-down list guide for the details.

Step 3: count working days

In the Days column, use NETWORKDAYS to count only working days between the start and end dates, including both. The formula is =NETWORKDAYS([@Start],[@End],Holidays!$A$2:$A$20). It skips Saturdays and Sundays and any date in the holiday list. For example, a request from Monday 8 March 2027 to Friday 12 March 2027 gives 5 days. A request from Friday 12 March to Tuesday 16 March gives 3 days, which are Friday, Monday and Tuesday. If your weekend is different, use NETWORKDAYS.INTL with a weekend code.

Step 4: set up the Summary sheet

On the Summary sheet, type the names in A2:A4 and the entitlements in B2:B4. In C2 count the annual leave taken with =SUMIFS(LeaveLog[Days],LeaveLog[Employee],A2,LeaveLog[Type],"Annual"). Fill it down. For Ali this returns 5, for Sara 10 and for Omar 16. Our SUMIFS guide explains how the criteria work. Sick leave is usually tracked separately, so add another column called Sick with the same formula and the criterion Sick, which returns 3 for Ali and 0 for the others.

Step 5: calculate the remaining balance

In the Remaining column use =B2-C2. For Ali the result is 20 minus 5, which is 15. For Sara it is 18 minus 10, which is 8. For Omar it is 15 minus 16, which is negative 1, so he has taken one day more than his entitlement. If your policy allows unused days to carry over, add a Carry over column and use =B2+D2-C2. Keep the entitlement in one place so you can change it for next year without changing the formulas.

Step 6: flag problems with a status

Add a Status column with a formula such as =IF(E2<0,"Over limit",IF(E2<=3,"Low","OK")), assuming E holds the remaining days. Omar then shows Over limit, and anyone with three days or fewer shows Low. Add conditional formatting to color the text red for Over limit and orange for Low. This lets a manager see at once who needs attention. Our IF statements guide explains how to build nested conditions like this one.

Step 7: avoid overlapping leave

For team planning, you may want to know when two people are away on the same day. Add a column in the Log called Overlap with a formula that counts other requests that include the start date: =COUNTIFS(LeaveLog[Start],"<="&[@Start],LeaveLog[End],">="&[@Start],LeaveLog[Employee],"<>"&[@Employee]). A result above 0 means that someone else is away on the first day of this request. Use conditional formatting to highlight these rows. For a visual calendar, you can use the grid method from our Gantt chart guide.

Step 8: add a monthly view

A small table that shows the days taken in each month helps with planning. List the months in a row and use SUMIFS with the month start and end dates, for example =SUMIFS(LeaveLog[Days],LeaveLog[Start],">="&B1,LeaveLog[Start],"<="&EOMONTH(B1,0)). This counts leave by the start month, which is simple and good enough for most teams. For leave that crosses a month end, split the request into two rows, or add a separate formula that counts the working days inside each month.

Carry over and new year setup

At the start of each year, copy the file and save it under the new year. Clear the Log table by selecting the data rows and choosing Delete, Table Rows, which keeps the formulas and the table intact. Then update the entitlements in the Summary sheet. If your policy lets people carry over unused days, type each person’s balance from the old file into the Carry over column, up to the maximum that the policy allows. For Sara, a balance of 8 days with a limit of 5 means a carry over of 5. Keep the old file, since it is the record of what was taken.

Reports for managers

Managers often ask who is away next week and how many days each person has left. Filter the Log table by the Start column to show upcoming leave, or sort it by date. On the Summary sheet, add a total row with the sum of the days taken and the days remaining, which gives the figures for the whole team. For our example the total entitlement is 53, the annual leave taken is 31 and the total remaining is 22. A simple chart of days taken by employee makes the report easier to read in a meeting.

Protect and share the file

Lock the formula cells in the Summary sheet and the Days column, then protect the sheet in the Review tab, so that nobody changes a formula by accident. Leave the Log input columns unlocked. Save the file in a shared folder with a clear name and the year, such as Leave Tracker 2027. Leave records are personal data, so limit access to the people who need it. At the end of the year, copy the file, clear the Log and update the entitlements, so that you keep a record of the old year.

Common mistakes

The most common mistake is to count calendar days instead of working days, which overstates the leave taken. Another is to type leave types in different ways, such as Annual and annual leave, so the totals miss some rows. A third is to forget the holiday list, so public holidays are counted as leave. A fourth is to enter half days as whole days. For half days, add a column called Half day and subtract 0.5 when it is ticked. A fifth is to overwrite the formulas in the Days column.

Practice problems

Try these. Problem 1: a request from Monday 1 March 2027 to Friday 5 March 2027 has 5 working days. Problem 2: from Thursday 4 March to Monday 8 March has 3 working days. Problem 3: if an employee has 22 days and has taken 9, the balance is 13. Problem 4: if the entitlement is 15 and 16 days are taken, the balance is negative 1. Problem 5: build the tracker with three employees and check the summary against the numbers in this guide.

Quick recap

Use a Log sheet for requests and a Summary sheet for balances. Make the log a table, use drop-downs for names and types, and count working days with NETWORKDAYS and a holiday list. Total the days by employee and type with SUMIFS, subtract them from the entitlement, and add a status column to flag low and negative balances. Protect the formulas, share the file carefully and start a new copy each year. With this setup, you only type each request once.

Frequently asked questions

How do I make a leave tracker in Excel?

Create a log table of requests, count working days with NETWORKDAYS and total them with SUMIFS.

How do I calculate the remaining leave balance?

Subtract the days taken from the entitlement.

How do I exclude public holidays?

Add a holiday list and use it as the third argument of NETWORKDAYS.

How do I track sick and annual leave separately?

Use the leave type as a second criterion in SUMIFS.

Can I count half days?

Yes. Add a half-day column and subtract 0.5 when it is ticked.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *