Tracking vacation in a spreadsheet works well for a small team if the sheet shows allowance, days used and days left for each person. This guide shows how. You can also use our free vacation tracker template.
Updated November 2026
Step 1: One list of employees
List each person with their annual days and any days carried over. In the example Maria L. has 20 annual days and 2 carried over, so 22 are available: =B7+C7.
Step 2: One list of requests
Add a row for every request: employee, first day, last day, type and status. Use drop-downs so names and statuses are always spelled the same; see the data validation guide.
Step 3: Count working days
Use NETWORKDAYS to count weekdays between two dates, both included: =NETWORKDAYS(B5,C5). July 5 to July 9, 2027, is five working days. NETWORKDAYS can also take a list of holidays as a third argument.
Step 4: Add up approved days
SUMIFS adds the working days that match several conditions: =SUMIFS(Requests!E5:E204,Requests!A5:A204,A7,Requests!D5:D204,"Vacation",Requests!F5:F204,"Approved"). Microsoft explains it on the SUMIFS page. Maria has 5 approved vacation days, so 22 – 5 = 17 remain. Her 3-day personal leave in November is not deducted.
Step 5: Watch pending requests
Run the same SUMIFS with “Pending” to see days that may still be used. Tom B. has 5 pending days, so his 15 available could drop to 10.
Policy matters
- Decide how carryover works before the year starts.
- Decide whether weekends and public holidays count.
- Write down how requests are approved.
Leave rules differ by place and by employer, so follow your own policy and local law.
Frequently asked questions
How do I count working days between two dates?
Use NETWORKDAYS(start,end). Add a holiday list as the third argument to exclude holidays.
How do I add up approved days for one person?
Use SUMIFS with the employee, type and status as conditions.
Does sick leave reduce vacation?
Not in this setup. Track it with its own type.
Is there a template?
Yes. The vacation tracker template on this site is ready to use.