Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| As of | 46342 | Change the date, the leave year and the warning level on the Settings sheet. Requests are typed on the Requests sheet. | |||
|---|---|---|---|---|---|
| Employees | 10 | Off on the "as of" date | 1 | ||
| Vacation days taken | 152 | Pending requests | 6 | ||
| Sick days taken | 29 | Pending days | 26 | ||
| Other days taken | 13 | Low vacation balance | 2 | ||
| Total days off (approved) | 194 | Over allowance | 1 | ||
| Employee | Vacation entitled | Taken | Pending | Left | Status |
| Olivia Martin | 22 | 15 | 8 | 7 | OK |
| Ethan Brooks | 20 | 13 | 0 | 7 | OK |
| Priya Patel | 27 | 20 | 5 | 7 | OK |
| Marcus Johnson | 20 | 12 | 0 | 8 | OK |
| Hannah Lee | 18 | 10 | 0 | 8 | OK |
| Daniel Garcia | 28 | 15 | 5 | 13 | OK |
| Grace Thompson | 21 | 18 | 0 | 3 | Low balance |
| Tyler Nguyen | 18 | 15 | 2 | 3 | Low balance |
| Sophie Wilson | 22 | 15 | 0 | 7 | OK |
| Caleb Foster | 24 | 19 | 6 | 5 | Over allowance |
Example from the “Dashboard” tab, with the numbers and formulas that are in the file.
What is inside
- Dashboard: The year at a glance: employees, vacation, sick and other days taken, who is off on the "as of" date, pending requests and days, and how many people have a low balance or are over their allowance. Below it a balance for the first 15 employees, days off by month, the split by leave type and by department, and four charts.
- Requests: The log you type into: employee and leave type from dropdowns, first and last day, a Half day column and a status of Approved, Pending or Declined. It counts the working days and a Check column flags a person or leave type that is not on your list, a missing date, an end date before the start and a request that overlaps another by the same person. Room for 1,000 requests.
- Balances: One row per employee: vacation entitled (yearly days plus carry-over), taken, pending and left, then sick and personal allowance, taken and left, other leave taken, total days off and a status of OK, Low balance or Over allowance.
- Calendar: A team calendar for the month you choose: L for approved leave and P for pending, on working days only, with a count of people off each day and each person's days off in the month.
- Employees: Your people with department, yearly vacation, sick and personal days, and vacation carried over from last year. Room for 40 employees.
- Holidays: Your public holidays. A holiday that falls on a weekday is taken out of any request that covers it.
- Settings: Company name, the leave year, the "as of" date, the low balance warning level, the calendar month, and the lists of leave types and departments.
The file opens on a start tab (“Start here”) that explains how to use it.
What makes it different
Most leave trackers are a grid where you colour days and count them by hand. This one starts from the request, so the days are counted for you, holidays and half days included, and the balance, the calendar and the dashboard all come from the same list. The sample is a ten-person company with 46 requests over 2026, with the date set to 16 November 2026 so you can see someone on leave, pending requests and two people running low.
How do I track employee vacation and sick days in Excel?
Keep one table of requests rather than one grid per person: employee, leave type, first day, last day and status. Count the working days of each request, then add them up per employee and type with SUMIFS. Subtract that from the yearly allowance to get what is left.
In the sample, the ten employees have taken 152 vacation days and 29 sick days in 2026 from approved requests, and 12.5 days of personal and other leave. Only approved requests come off a balance, so a pending request never changes it until you approve it.
How do I calculate leave days and balances without counting weekends and holidays?
Count the Monday-to-Friday days between the first and the last day, then take away the holidays on your list that fall on weekdays. A request for Monday 23 to Friday 27 November 2026 is 5 weekdays, but this sheet counts 4 because 26 November is on the Holidays list. A single day with Half day set to Yes counts 0.5.
Vacation entitled is the yearly days plus any days carried over, and left is entitled less approved days taken. Priya Patel, with 22 days and 5 carried over, has 27 entitled, 20 taken and 7 left, with 5 more pending in December. Check your own contract and local rules for entitlements and carry-over: this sheet counts, it does not decide what is owed.
How do I see who is off and spot low or negative balances?
The Calendar shows each person against each day of the month, so overlaps in the team are easy to see, and the last row counts how many people are off. The Dashboard says how many people are off on the "as of" date (1 in the sample) and counts the pending requests (6, for 26 days).
A person is flagged Low balance when vacation left drops below the level you set, 5 days in the sample, and Over allowance when any of vacation, sick or personal leave goes negative. In the sample, Grace Thompson and Tyler Nguyen are low with 3 days left, and Caleb Foster is over his sick allowance by 1 day. For hours and shifts rather than days off, see the employee attendance template, and for pay see the payroll and attendance template.
How to use it
- On Settings type your company name, the leave year, today's date (the "as of" date), the warning level for low vacation balances, and the month the Calendar shows. Check the leave types and departments.
- On Employees list each person with a department, their yearly vacation, sick and personal days, and any vacation days carried over from last year.
- On Holidays list your public holidays. They are not counted as leave days.
- On Requests add one row per leave request: employee and leave type from the dropdowns, first and last day, "Yes" in Half day for a half day, and the status (Approved, Pending or Declined). Working days are counted for you.
- Open Balances to see for each person the days entitled, taken, pending and left, and a status (OK, Low balance or Over allowance).
- Open Calendar to see who is off on each day of the month, and Dashboard for the totals, charts and pending requests.
Before you start:
- Capacity: 40 employees, 1,000 requests, 30 holidays, 6 leave types and 6 departments. The Dashboard lists the first 15 employees.
- Working days are Monday to Friday. Holidays on the Holidays sheet that fall on a weekday are taken out of a request. A request on one day with Half day set to Yes counts 0.5.
- Only Approved requests are subtracted from a balance. Pending requests show in their own column so you can see what is left if they are approved.
- A request counts in the year of its first day. A request that runs over New Year is best split into two rows.
- Vacation entitled = yearly vacation days + days carried over. Sick and personal leave have their own yearly allowances. The other leave types (unpaid, bereavement, parental) are counted but have no allowance.
- The Check column on Requests flags a missing employee, a leave type or date problem, an end date before the start date, and a request that overlaps another one by the same person.
- The Calendar shows L for approved leave and P for pending on working days only. Change the month on Settings to move on.
- Check the rules of your country, state and contract for entitlements and carry-over. This sheet counts days, it does not decide what you owe.
Still running this on Excel?
This sheet suits a start and a limited amount of data. If your way of working grows, or you need more than one person working on it at the same time, Softwiro can build you a program that fits the way you work.
| In this sheet | In Custom Software from Softwiro |
|---|---|
| One file that one person edits at a time, and copies reach you on WhatsApp. | A program with a login for each user and permissions you define, and everyone sees the same data. |
| You type everything and check the numbers by hand. | We design the screens around your work steps, so each person records only what concerns them. |
| Columns and formulas are fixed as the sheet was designed. | We build what you need: reports, alerts and screens your way. |
| Backups and security are your own responsibility. | The program runs on a secured server with regular backups. |
Frequently asked questions
Is this leave tracker free?
Yes. Download it, use it for your team and change anything you like. There is nothing to sign up for.
Does it work in Google Sheets and LibreOffice?
Yes. It uses ordinary formulas such as SUMIFS, COUNTIFS, INDEX and MATCH, so it works in Excel 2016 and later, LibreOffice and Google Sheets. In Google Sheets, upload the file to Drive and open it with Sheets.
How many employees and requests can it hold?
40 employees, 1,000 requests, 30 holidays, 6 leave types and 6 departments. The Dashboard lists the first 15 employees. At the start of a new year, save a copy, change the leave year and clear the requests.
How are half days and holidays counted?
A request with the same first and last day and Yes in Half day counts 0.5. Holidays on the Holidays sheet that fall on a weekday are not counted. Weekends are never counted.
Does it handle carry-over and accrual?
Carry-over is a number you type for each person: it is added to the vacation entitled. There is no automatic accrual by month, so type the yearly total, or the part-year amount for someone who joined during the year. Check your local rules for what may be carried over.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
More free templates for: Human resources
More free templates
- Weekly Timesheet Template for Excel
- Monthly Habit Tracker Template for Excel (With Streaks)
- General Journal and Trial Balance Template for Excel
- Delivery Tracking Spreadsheet for Excel (Dispatch Tracker)
- Real Estate Lead Tracking Spreadsheet (Excel)
- Production Tracking Spreadsheet for Excel
- Piece Rate Payroll Calculator for Excel
- Garment Costing Sheet for Excel
- Poultry Flock Tracker for Excel: Eggs, Feed, Mortality and Profit