How to track annual leave in Excel – when it falls short: 7 signs and a migration plan

The file is called leave_2026_latest_v3_FINAL.xlsx. Inside are twelve monthly tabs, a “balance” tab and colours that managers have added themselves. On Monday morning the sales manager finds out, too late, that two people are off the same week; an employee from production says their remaining leave is 12 days, not 9; accounts reads a different figure from a different file. Excel is where most companies start tracking leave, and set up properly it serves well for a long time. This article first explains how to set up leave tracking in Excel properly, then the seven signs that show when it is no longer enough, and a plan for switching without corrupting your data.

A leave tracker is a table that shows each employee’s leave entitlement, the leave they have taken and their remaining balance in date order. Set up in Excel with at least three tabs, it is both readable and catches errors early.

How do you track leave in Excel?

Excel works well when the headcount is small, one person enters the leave, approval is verbal and everyone accesses the same file. Even under these conditions, the way to keep things in order is to separate the data from the calculation:

To pull the days taken from the Entries tab into the Balance tab, a formula that adds up by employee number is enough; for example =SUMIF(Entries!A:A, A2, Entries!D:D). Remaining days are the difference between the entitlement and this total. That way the balance is never written down anywhere; it is generated from the records every time. HR updates the entitlement column on each employee’s work anniversary; if you want a formula to calculate it, put the seniority bands in a separate table and use a lookup formula. Because a band changes on the anniversary, this column must be updated by start date, not at month end or at the start of the year.

Annual leave tracker example

The Balance tab example below is fictional. HR fills in the entitlement column; the days taken column is filled by formula:

The Entries tab should have the following columns:

• Employee number
• Leave type (annual, excused, unpaid, hourly)
• Start and end date
• Number of days or hours
• Request date
• Approving manager and approval date
• HR approval and processing date
• Notes and attached document details

Six rules for keeping leave tracking in Excel safe

• Store the file in one place and share a link instead of sending copies; the more copies there are, the sooner the single source of truth is lost.
• Give editing rights to HR only; managers see a read-only summary.
• Instead of putting version numbers in the file name, log the date and the person who made each change in a change log tab.
• Back up the file at the end of every month and keep the backup in a separate location.
• If sensitive columns such as employee ID details are not needed for leave tracking, leave them out of the file altogether.
• At year end, carry the balance over to the new year’s file with a closing row instead of typing it in by hand.

Your Excel leave spreadsheet is no longer enough: 7 signs

If you see several of the following signs at the same time, the file can no longer carry the load:

1. The balance differs in two places. HR’s file and the employee’s own note, or the accounts spreadsheet, do not give the same figure.
2. Overlapping leave is noticed only afterwards. It becomes clear only after the leave has started that two people are off the same week.
3. There is no approval trail. Who approved which leave and when sits in emails or messages, not in the spreadsheet.
4. Several copies of the file are circulating. People argue over which of the versions named “latest” and “FINAL” is current.
5. Formulas break. Shifted columns, hand-typed values and deleted rows silently make the balance wrong.
6. Access is all or nothing. Everyone you share the file with sees every row; a manager cannot see only their own team.
7. Leavers and year-end closing eat up working hours. Several files are compared by hand to find the leaving balance and the leave carried over.

On its own, each of these signs is a small problem; the problem is that, as they grow together, HR spends its time fixing the spreadsheet. The first warning is usually a balance discrepancy: if two people quote different figures for the same employee, the file has lost its role as the single source of truth.

When does it make sense to stay with Excel?

If you see none of the seven signs and one person enters leave under the same rules, staying with Excel is a reasonable decision. The cost of switching, the set-up time and the time employees need to get used to a new system also count. What matters is knowing in advance when the file will reach its limit: if a new department is added, a second HR employee starts entering leave or managers want to see their own team’s status, these signs will not take long to appear.

Switching from Excel to a portal: six steps without corrupting your data

1. Merge scattered files into a single staff list, with one row per employee.
2. Freeze the opening balances as at a set date and confirm them in writing with employees.
3. Write down the rules: how days are counted, Saturdays, half days, leave types and approval order.
4. Run a two- to four-week pilot with one department and add any gaps you find to the rules.
5. Run a parallel period: new requests go through the new workflow, and Excel stays read-only.
6. Once the switch is complete, archive the Excel file with its final date and record the closing balance.

The most common mistake in the parallel period is keeping records in two places at once without making clear which one counts. Announce the switch date in advance: after that date, requests are accepted only through the new workflow, and no leave entered in the old file counts. Naming one person to help employees with their first request noticeably lightens the load of questions in the first week.

How does the portal deal with these signs?

In YazSis KurumsalPortal’s Mobile Leave Management module, employees create leave requests from their phone’s browser without installing an app. The request is checked against the remaining balance and goes first to the manager, then to HR; every approval step stays on record, approved leave is added to the team calendar and the balance is updated. Because the balance is tracked from these approved records, employees, managers and HR all work with the same up-to-date information. Records can also be exported as XLSX reports, so leaving Excel behind does not mean giving up reporting.

The portal runs on the customer’s own server, and data is not sent to a third-party cloud; standard cPanel hosting is enough. Thanks to role-based data scope, not everyone needs to see every row. How your existing Excel data will be transferred and how your rules will be reflected are clarified in a meeting before installation. If data needs to flow to or from a time and attendance or payroll system, the scope is agreed during the project.

If you recognised several of the seven signs, take a look at the Mobile Leave Management module to see what it looks like to bring requests, approvals and balances into a single workflow.