Employee Attendance Tracker Excel Template

To keep your attendance sheet clean and reliable, you want to prevent team members from typing random notes like "doctor appointment" or "taking afternoon off" directly into the day cells. Long text stretches the columns and breaks counting formulas.

Instead, we standardize all leave into four standard keys: V for Vacation, P for Personal, S for Sick Leave, and H for Company Holiday.

Highlight the entire 31-day attendance grid (cells C9 through AG18). On the ribbon, click the Data tab, then select Data Validation.

In the dialog box under Allow, select List. In the Source box, type: V, P, S, H. Click OK to confirm.

Now, each cell in your calendar only accepts these four approved codes, keeping your entire matrix uniform.

A wall of plain letters is hard to read quickly. By giving each absence code a distinct color badge, anyone glancing at the sheet can spot vacation blocks and sick days in seconds.

With cells C9:AG18 highlighted, go to the Home tab, click Conditional Formatting, and select Manage Rules.

In the Rules Manager dialog, create four separate "Format only cells that contain" rules:

1. Cell Value equal to "V" -> Green fill (#27AE60) with bold white text (Vacation).

2. Cell Value equal to "P" -> Slate gray fill (#95A5A6) with bold white text (Personal).

3. Cell Value equal to "S" -> Blue fill (#2980B9) with bold white text (Sick Leave).

4. Cell Value equal to "H" -> Warm orange fill (#E67E22) with bold white text (Company Holiday).

Click Apply, then OK. As soon as you type "V" or "S", the cell transforms into a clean, modern color pill.

Next, we add two calculation columns at the right end of each row to summarize the employee month.

In cell AI9 (under Total Days), we count every day that has an absence code entered. Since blank cells represent regular workdays, we can simply count non-empty cells across the 31 days using =COUNTA().

Click cell AI9 and enter: =COUNTA(C9:AG9). For Alexander Wright, Excel counts 5 total absence days (4 vacation days plus 1 sick day).

In cell AJ9 (under Attended), we calculate how many days the employee actually worked. October 2026 has 22 standard working days (excluding weekends). We subtract the absences from 22:

Click cell AJ9 and enter: =22-AI9. Excel calculates 17 attended days.

Copy both formulas down through row 18 for all employees.

Finally, look at row 19 (October total). Beneath each day column, the formula =COUNTA(C9:C18) counts how many team members are absent on that specific date.

Reviewing this bottom row gives managers immediate operational insight: on October 15, three employees are out at the same time, alerting team leads to postpone non-critical deliverables or arrange shift backup.

Across the full department, your team logged 32 total absence days and 188 attended days, achieving a solid 85.45% monthly attendance rate.