Build an Employee Leave Tracker in Excel

A leave tracker in Excel records every holiday and sick day your team takes and shows how much leave each person has left. One sheet logs the requests, NETWORKDAYS counts the working days in each one, and SUMIFS and COUNTIFS turn the log into a summary. This guide builds a tracker for a small team, the kind an office manager, team lead or HR assistant keeps.

How the leave tracker is organised#

The tracker has three parts, each in its own area or sheet:

1. The log: one row per leave request. This is the only place you type leave.
2. The holiday list: the public or company holidays for the year.
3. The summary: one row per employee, with allowance, days used and days left. All formulas.

Keeping the log separate from the summary means you never edit totals by hand. You add a row to the log and the summary updates.

Step 1: Set up the leave log#

Put the log on a sheet called Log, with these columns:

Use drop-down lists (Data > Data Validation > List) for Employee and Type, so names and types are always spelled the same. SUMIFS only matches exact text, apart from upper and lower case. Turn the log into a table with Ctrl+T (Mac: ⌘T) so the Days formula fills into each new row by itself.

Step 2: List the holidays#

Put the holidays for the year in a column on the same sheet, for example H2:H4: 3 April 2026, 6 April 2026 and 25 May 2026 in this example. Use your own country's or company's dates. Real dates, not text, are important: NETWORKDAYS compares numbers, and a holiday typed as text is ignored.

Step 3: Count working days with NETWORKDAYS#

NETWORKDAYS counts the weekdays (Monday to Friday) between two dates, including both the start and end dates. Its third argument is an optional list of holidays to skip. In E2:

=NETWORKDAYS(C2,D2,$H$2:$H$4)

Ana's request runs from Monday 30 March to Wednesday 8 April. That's 8 weekdays, minus the two holidays on 3 and 6 April, so the result is 6. The $ signs keep the holiday range fixed when the formula fills down.

Chloe's two weeks in May give 9 rather than 10, because 25 May is a holiday. The NETWORKDAYS function guide covers the arguments, and the workdays lesson practises NETWORKDAYS and WORKDAY together.

Two special cases:

• Half days. Type 0.5 in the Days cell for that row instead of the formula, and note it.
• Different weekends. If your team doesn't work Friday and Saturday, use NETWORKDAYS.INTL, which has a weekend argument: =NETWORKDAYS.INTL(C2,D2,7,$H$2:$H$4).

Step 4: Total leave used with SUMIFS#

SUMIFS adds numbers that meet several conditions at once. On a Summary sheet, list the employees in column A and their yearly allowance in column B. In C2, total annual leave used:

=SUMIFS(Log!$E:$E,Log!$A:$A,$A2,Log!$B:$B,"Annual")

The first range is what to add (Days). Then come pairs of range and condition: the Employee column must match A2, and the Type column must be "Annual". For Ana, that's 6 + 5 = 11 days. Her sick day isn't included, because its type is Sick.

Annual left in D2 is =B2-C2. Sick days in E2 is the same SUMIFS with "Sick" instead of "Annual". Fill all three formulas down with Ctrl+D (Mac: ⌘D).

Step 5: Count requests and see who is off#

COUNTIFS counts rows that meet several conditions. To count how many annual leave requests Ana made:

=COUNTIFS(Log!$A:$A,$A2,Log!$B:$B,"Annual")

The result is 2. The comparison of COUNTIF vs COUNTIFS explains when one condition isn't enough.

A more useful question for a manager is: how many people are off on a given day? Put a date in H8, then:

=COUNTIFS(Log!$C:$C,"<="&H8,Log!$D:$D,">="&H8)

This counts requests that started on or before the date and end on or after it. For 7 April 2026, the result is 1 (Ana). The & joins the operator to the date in the cell. The COUNTIFS function guide and the guide to COUNTIFS and SUMIFS with multiple conditions cover this pattern in detail.

Step 6: Add warnings with conditional formatting#

Color makes problems visible without anyone having to read every number.

• Low balance: select D2:D4 on the summary and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than, set to 3, so anyone close to running out stands out.
• Over the allowance: add a second rule for values less than 0 with a red fill.
• Too many people off: next to a calendar of dates, color any day where the COUNTIFS result is 2 or more.

Practise COUNTIFS and SUMIFS to get quick at writing the summary formulas these rules depend on.

Keep the tracker accurate#

A few habits keep the numbers right through the year:

• Start a new log each leave year, or add a Year column and include it as one more SUMIFS condition.
• Update the holiday list before the year begins.
• Carry over unused days by adding them to next year's allowance column, not by editing the log.
• Record approvals in an extra column if your team needs to show who approved each request.

The same log-plus-summary pattern runs many team sheets. Pair this tracker with a shift schedule in Excel to plan cover, or with an attendance tracker for daily check-ins. Leave totals also make a good line on a KPI summary.

Practise it#

Try the exercise on this page: count Ana's working days of leave with NETWORKDAYS and a holiday list. Then practise NETWORKDAYS and WORKDAY in a live spreadsheet, and practise COUNTIFS and SUMIFS to build the summary that shows who has leave left.