An attendance and leave tracker Excel template is a workbook that records each employee’s workday status, tracks approved time off, and summarizes totals for a chosen period. A practical setup uses separate Staff, Daily Log, Leave Ledger, and Summary sheets, with consistent status labels and formulas such as COUNTIFS and SUMIFS to calculate totals.
What an attendance and leave template should contain
A useful tracker is more than a calendar with colored cells. It should keep employee details, daily attendance records, leave transactions, and reports organized so that a change in one area does not quietly distort another. A four-sheet workbook is a manageable starting point:
- Staff: employee ID, name, team, active status, leave allowance, and any opening leave balance you need to carry forward.
- Daily Log: one row per employee per scheduled workday, including the date, employee ID, name, and attendance status.
- Leave Ledger: one row for each approved leave day or partial day, with the leave type, approval status, and amount of leave used.
- Summary: monthly or other period-based counts, attendance rates, and leave balances.
Use a stable employee ID as the link between sheets. Names can be duplicated, misspelled, or changed; an ID is a more dependable key. Keep the workbook’s reporting period clear, and decide whether it covers one team, one location, or the whole organization before entering records.
Set up the workbook in Excel
In Excel for Microsoft 365 on Windows, create a blank workbook and add the four worksheets. Use short, descriptive names such as Staff, Daily Log, Leave Ledger, and Summary. Put a single header row at the top of each data sheet, and avoid merged cells inside the records: they make sorting and filtering harder.
- On Staff, enter one employee per row. Add an ID, name, team, annual leave allowance, and opening balance if your process uses one.
- On Daily Log, use columns A through D for Date, Employee ID, Name, and Status. Add an optional Notes column only for brief, necessary context.
- On Leave Ledger, use columns A through E for Employee ID, Leave Date, Leave Type, Approval Status, and Days. Enter 1 for a full day and, if your policy permits it, 0.5 for a half day.
- On Summary, specify the period start and end dates and list employee IDs. Keep the summary labels explicit, such as “Present days” or “Approved annual leave used.”
Format date columns as dates, not text. Select a data range and use Insert > Table to make it easier to filter, sort, and extend as records are added. Tables can also make formulas easier to read, although the examples below use ordinary cell references so their assumed layout is visible.
Choose attendance codes before entering data
Agree on a small set of status labels before the tracker is used. For example, a team might use Present, Remote, Absent, Annual leave, Sick leave, Holiday, and Not scheduled. These labels are examples, not universal definitions: the employer should decide what each one means and apply the same rules across the workbook.
Keep attendance status separate from the leave approval record. “Annual leave” in a daily log describes the day; the Leave Ledger can hold whether the request was approved and how much leave it consumes. If a request is pending, do not treat it as approved leave in the balance calculation. You can use a dropdown to reduce inconsistent entries: select the status cells, then choose Data > Data Validation and set Allow to List. Enter the approved labels as the source, or point to a maintained list on a worksheet.
Record only scheduled workdays if your reporting denominator is meant to be scheduled days. Do not add weekends or public holidays as ordinary absences. For shift workers or teams with different schedules, a single weekday calendar may not describe everyone’s expected workdays; record the relevant schedule or define the reporting rules for each group.
Use formulas for monthly attendance totals
Formulas save time, but they only produce meaningful totals when dates, IDs, and status labels are consistent. In the examples, the Daily Log has dates in column A, employee IDs in B, names in C, and statuses in D. The Summary has the employee ID in A5, period start in B2, and period end in C2.
With that layout, enter this in the Summary cell where you want the employee’s Present count:
=COUNTIFS('Daily Log'!$B:$B,$A5,'Daily Log'!$A:$A,">="&$B$2,'Daily Log'!$A:$A,"<="&$C$2,'Daily Log'!$D:$D,"Present")
For example, if employee E104 has 18 rows marked Present between the selected start and end dates, the formula returns 18. You can copy the formula for other employees by changing the employee ID reference through the row. Make sure B2 and C2 contain real Excel dates and that C2 is not earlier than B2.
For an attendance rate, decide what the rate means and label it. One common approach counts Present and Remote as attended, counts Absent as a missed scheduled day, and excludes approved leave and non-working days from the denominator. If the Summary has Present in D5, Remote in E5, and Absent in F5, the following formula calculates that rate:
=IFERROR((D5+E5)/(D5+E5+F5),"")
With 18 Present days, 2 Remote days, and 1 Absent day, the result is 20 divided by 21, or about 95.24%. Format the result as a percentage. This is a chosen reporting definition, not a standard that every organization must use; explain whether approved leave is excluded so readers do not mistake the percentage for a measure of all scheduled days.
Track leave use and balances carefully
A leave balance is only as accurate as its policy inputs. Allowances, carryover, accrual schedules, part-time arrangements, and treatment of different leave types can vary. Keep those rules in the organization’s approved records and configure the workbook to match them; do not assume a single annual allowance or accrual method applies to everyone.
For a straightforward balance table, suppose Staff has employee ID in A2, annual allowance in D2, opening balance in E2, leave used in F2, and remaining balance in G2. In Leave Ledger, use employee ID in A, leave date in B, leave type in C, approval status in D, and days in E. To total approved Annual leave for the employee in Staff A2, enter this in Staff F2:
=SUMIFS('Leave Ledger'!$E:$E,'Leave Ledger'!$A:$A,$A2,'Leave Ledger'!$C:$C,"Annual",'Leave Ledger'!$D:$D,"Approved")
If the ledger contains 3 approved full days and one approved half day of Annual leave, the result is 3.5. Pending or rejected entries are excluded. With the stated Staff layout, enter this in G2 to calculate a simple remaining balance:
=D2+E2-F2
For example, an allowance of 20 days, an opening balance of 2 days, and 3.5 days used gives 18.5 days remaining. This arithmetic does not determine entitlement; it simply reflects the values entered. For leave spanning multiple dates, record each chargeable day separately or create a carefully checked process that excludes non-working days according to the employee’s schedule.
Make summaries readable and useful
A monthly summary should answer practical questions without forcing a manager to inspect every log row. Useful fields may include Present days, Remote days, Absences, approved leave by type, and remaining leave balance. If the workbook serves different teams, add a team filter or make separate reports while retaining one consistent underlying record format.
Use clear number formats and labels. A date should display as a date, a leave balance should show days or fractions of days, and an attendance rate should be shown as a percentage. Conditional formatting can draw attention to a value that needs review, but color should not be the only way information is conveyed. In Windows Excel, a typical route is Home > Conditional Formatting; choose an appropriate rule and keep its meaning documented.
Freeze the header row if the log is long, and use filters to review a particular month, employee, or status. A summary is not a substitute for the source records: retain enough detail to trace a total back to its dates and status entries. If a number changes unexpectedly, filter the underlying log rather than manually overwriting the formula result.
Common spreadsheet problems and safeguards
Several routine issues can make an apparently complete tracker unreliable. Dates pasted as text may not match a date-based formula. A status entered as “Annual Leave” will not match a formula looking for “Annual leave.” Duplicate rows can inflate counts, while a missing ID can leave a record out of an employee’s summary. Set clear entry rules and review exceptions regularly.
- Use data validation lists for IDs and statuses where practical, and check that new employees are added to the Staff sheet before logging them.
- Filter for blank IDs, blank dates, unexpected status values, and duplicate employee-date pairs. A duplicate is not always an error, but it should be explainable.
- Protect formula cells from accidental editing if appropriate, while remembering that worksheet protection is not a substitute for securing confidential data.
- Restrict workbook access to people who need it, store it in an approved location, and avoid putting medical details or unnecessary sensitive notes in the log.
- Keep dated backups or use an approved version history process. Agree who can correct records and how corrections are documented.
Excel is not automatically a secure personnel system just because a workbook has a password or hidden sheet. Attendance and leave data can be sensitive. Follow the organization’s privacy, retention, and access procedures, and do not use a shared workbook link with broader access than intended.
When Excel is enough—and when to consider another tool
Excel can work well for a small team with a straightforward schedule, a limited number of editors, and someone responsible for checking the data. It is flexible, familiar, and easy to adapt for a monthly report. Its weaknesses become more significant when many people edit records at once, shift rules are complex, approvals need a traceable workflow, or payroll and scheduling systems must exchange data.
A shared spreadsheet can also create version conflicts or uncertainty about who changed a record. In those cases, compare a managed HR or time-and-attendance system, an organization-approved database, or a scheduling platform. Evaluate access controls, audit history, approval routing, export options, support, and total cost—not only the advertised feature list. Keep Excel as a reporting export if useful, rather than forcing it to serve as the official system for every process.
Frequently Asked Questions
Is there a built-in attendance and leave tracker template in Excel?
Excel may offer templates through its template gallery, but availability can vary. A ready-made workbook should still be checked for its status definitions, formulas, leave rules, and privacy suitability before you rely on it.
How do I calculate attendance percentage in Excel?
Choose a definition first. For example, divide Present plus Remote days by Present plus Remote plus Absent days if approved leave is excluded. The result will differ if your organization uses a different denominator.
How do I calculate remaining annual leave?
A simple calculation is allowance plus opening balance minus approved leave used. Use a SUMIFS formula to total approved ledger entries, and confirm that the allowance and carryover match the applicable policy.
Should weekends and holidays go in the attendance log?
Usually, not as ordinary scheduled workdays. Include a date only when it is relevant to that employee’s schedule, and define how non-working days and shifts are handled before calculating attendance.
Can more than one manager edit the tracker?
They may be able to edit a workbook stored in a supported shared location, but access, simultaneous edits, and change history depend on the storage and organization settings. Set permissions carefully and consider a dedicated system if approvals or auditability are important.
Related
- Excel Named Ranges: How They Work and When to Use Them
- Excel Named Range Table: How They Work and When to Use
- Excel Named Range for a Table Column: How It Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Expense Tracker Google Sheets Template: Setup and TipsAn expense tracker Google Sheets template is a spreadsheet for recording purchases, assigning categories, and totaling spending over…
- Excel New Comment Shortcut: Windows, Mac, and NotesIn Excel for Microsoft 365 on Windows, select a cell and press Ctrl+Alt+M to add a new comment.…
- Google Sheets Inventory Template: Build a Simple TrackerYes. You can make a practical Google Sheets inventory template with columns for each item, its location, quantities…
- Wrap Text in Google Sheets on iPad: Step-by-StepTo wrap text in Google Sheets on an iPad, select the cell or cells, tap the Format icon,…
- How to Compare Two Word Documents in Microsoft WordTo compare two Word documents, open the desktop app and choose Review > Compare > Compare on Windows.…
- Excel Power Query Tutorial for BeginnersTo use Power Query in Excel for Microsoft 365 on Windows, select your data source from the Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.