Leave & Attendance Tracker in Excel

Last updated: 27 June 2026 · Formulas tested in Excel 2010 and later.

A leave and attendance tracker in Excel is a grid where rows are employees and columns are the days of the month, each cell marked P (present), A (absent), L (leave) or WO (weekly off). COUNTIF then totals present, leave and absent days per employee, giving paid days and loss of pay (LOP) that flow directly into the salary sheet. One sheet per month drives the whole payroll.

Key takeaways

• Use a day-wise grid with codes: P present, A absent, L paid leave, WO weekly off, H holiday.
• COUNTIF totals each code per employee: =COUNTIF(range,"P") for present days.
• Paid days = P + L + WO + H; LOP days = A (unpaid absences).
• Feed paid days into the salary sheet to pro-rate Basic, HRA and allowances for LOP.
• Use data validation (a dropdown of codes) to prevent typos and keep counts clean.
• Track a leave balance (opening + accrued − taken) alongside attendance for each employee.

Fact box. In payroll, paid days = present + paid leave + weekly offs + holidays, and loss of pay (LOP) days are unpaid absences. Earnings are pro-rated as monthly salary × paid days ÷ total days in the month, so an accurate attendance count directly determines net pay. (Source: standard Indian payroll practice.)

What columns should an attendance sheet have?

A monthly tracker has identifiers, a column per calendar day and summary counts. The structure:

Keep a separate Leave balance block (opening balance, leave accrued, leave taken, closing balance) so you can approve leave against entitlement.

How do I count attendance with COUNTIF?

Each summary column is one COUNTIF across that employee's day cells. If the daily grid for a row sits in C2:AG2:

Then in the salary sheet, pro-rate each earning: =ROUND(Monthly_Basic*PaidDays/TotalDays,0).

Tip: Add data validation (Data ▸ Data Validation ▸ List) on the day cells with the allowed codes P, A, L, WO, H. A dropdown stops stray entries that would break your COUNTIF totals, and you can colour-code with conditional formatting.

Fact box. A single COUNTIF per code turns a month's attendance grid into the paid-days and LOP figures payroll needs. Because the salary sheet pro-rates on paid days, fixing one wrong cell automatically corrects that employee's gross and net pay. (Source: Excel best practice.)

How do I track a leave balance?

Maintain an opening balance and adjust it as leave accrues and is taken. For each employee:

Closing balance = Opening balance + Leave accrued − Leave taken

In Excel: =OpeningBalance+MonthlyAccrual-COUNTIF(C2:AG2,"L"). Carry the closing balance forward as next month's opening balance, so the running entitlement stays accurate and you can flag negative balances.

How to build the tracker step by step

1. Create a monthly sheet with Emp ID, Name and a column for each calendar day.
2. Add data validation dropdowns (P, A, L, WO, H) on all day cells.
3. Add COUNTIF summary columns for each code.
4. Compute Paid days and LOP days.
5. Add a leave-balance block (opening + accrued − taken = closing).
6. Link Paid days into the salary sheet to pro-rate earnings.
7. Copy the sheet for the next month and carry leave balances forward.

How Ankeshan helps: Ankeshan keeps the attendance grid, leave balances and paid-days counts inside Excel and feeds paid days straight into the salary sheet, so LOP pro-rating and net pay update automatically. (Launching soon — join the waitlist for the free template, no sign-up.)

Frequently asked questions

How do I auto-count present days in Excel? Use =COUNTIF(range,"P") across the employee's daily cells. Add separate COUNTIFs for L, WO, H and A to get paid days and LOP.

What is the difference between paid days and LOP? Paid days are present days plus paid leave, weekly offs and holidays. LOP (loss of pay) days are unpaid absences that reduce salary proportionally.

How do paid days affect salary? Each earning is pro-rated as monthly salary × paid days ÷ total days in the month, so fewer paid days mean lower gross and net pay.

Can I prevent wrong attendance codes? Yes — apply data validation with a dropdown list of allowed codes on the day cells, which keeps the COUNTIF totals reliable.

Sources

• Standard Indian payroll practice (paid days, LOP pro-rating, leave accounting).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Payroll in Excel (pillar) » · Salary Sheet Format in Excel » · How to Generate Payslips from Excel » · CTC vs In-Hand Salary Breakup »