How to Make an Attendance Sheet in Excel — With Auto Formulas

Every HR department and small business needs an attendance tracker. Here's how to build one in Excel that auto-counts present days, highlights weekends, tracks leave balance, and works for any month — no VBA needed.

What You'll Build

┌─────┬──────────┬───┬───┬───┬───┬───┬─  ─┬───┬───────┬───────┬───────┐
│ ID  │ Name     │ 1 │ 2 │ 3 │ 4 │ 5 │... │31 │Present│Absent │ Leave │
├─────┼──────────┼───┼───┼───┼───┼───┼─  ─┼───┼───────┼───────┼───────┤
│E001 │Rahul     │ P │ P │ P │ WO│ WO│    │ P │  22   │   2   │   1   │
│E002 │Priya     │ P │ L │ P │ WO│ WO│    │ P │  21   │   1   │   2   │
└─────┴──────────┴───┴───┴───┴───┴───┴─  ─┴───┴───────┴───────┴───────┘
  P = Present  A = Absent  L = Leave  WO = Week Off  H = Holiday

Step 1: Set Up the Header Row

1. Cell A1: Month → Cell B1: dropdown for month name (Jan, Feb, ... Dec)
2. Cell C1: Year → Cell D1: dropdown for year (2025, 2026, 2027)
3. Row 3: Headers — Emp ID | Name | 1 | 2 | 3 | ... | 31 | Present | Absent | Leave | WO

Auto-Generate Day Numbers

In cell C3, enter 1. In D3: =C3+1. Drag to column AG (day 31).

To show day names (Mon, Tue...) in row 2:

=TEXT(DATE($D$1, MATCH($B$1, {"Jan","Feb","Mar","Apr","May","Jun",
      "Jul","Aug","Sep","Oct","Nov","Dec"}, 0), C3), "ddd")

Step 2: Auto-Detect Weekends (Saturdays & Sundays)

Use Conditional Formatting to gray out weekends automatically:

1. Select the attendance area (C4:AG50)
2. Home → Conditional Formatting → New Rule
3. Select "Use a formula"
4. Enter: =WEEKDAY(DATE($D$1, MATCH($B$1, {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, 0), C$3), 2) > 5
5. Format: gray background, gray font

This automatically highlights columns for Saturday (6) and Sunday (7).

Step 3: Add Data Validation for Entry

Restrict attendance cells to only valid entries:

1. Select attendance area (C4:AG50)
2. Data → Data Validation
3. Allow: List
4. Source: P,A,L,H,WO,HD

Now users can only enter: P (Present), A (Absent), L (Leave), H (Half Day), WO (Week Off), HD (Holiday)

Step 4: Auto-Fill Weekends as "WO"

Use this formula in attendance cells instead of manual entry:

=IF(WEEKDAY(DATE($D$1, MATCH($B$1, {"Jan","Feb","Mar","Apr","May",
    "Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, 0), C$3), 2) > 5,
    "WO", "")

This pre-fills WO on Sat/Sun. Users only need to fill weekdays.

Step 5: Summary Formulas (Right Side)

After day 31 (column AG), add summary columns:

Total Present Days:

=COUNTIF(C4:AG4, "P") + COUNTIF(C4:AG4, "H")*0.5

Total Absent:

=COUNTIF(C4:AG4, "A")

Total Leave:

=COUNTIF(C4:AG4, "L")

Total Week Off:

=COUNTIF(C4:AG4, "WO")

Working Days in Month:

=NETWORKDAYS(DATE(D1, MATCH(B1, {"Jan","Feb","Mar","Apr","May",
    "Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, 0), 1),
    EOMONTH(DATE(D1, MATCH(B1, ..., 0), 1), 0))

Attendance Percentage:

=Present_Days / Working_Days * 100

Step 6: Hide Extra Days

February has 28/29 days, April has 30. Hide invalid day columns:

Conditional Formatting on day headers:
=C$3 > DAY(EOMONTH(DATE($D$1, MATCH($B$1, {...}, 0), 1), 0))

Format: White font on white background (hides the column visually)

Step 7: Color-Code Entries

Use Conditional Formatting to color-code each status:

• P (Present) → Green background (#c6efce)
• A (Absent) → Red background (#ffc7ce)
• L (Leave) → Yellow background (#ffeb9c)
• H (Half Day) → Light orange
• WO (Week Off) → Gray background (#d9d9d9)
• HD (Holiday) → Blue background (#b4c6e7)

This makes the sheet scannable at a glance — you can spot attendance patterns immediately.

Step 8: Protect the Sheet

1. Lock all cells: Ctrl+A → Format Cells → Protection → Locked ✓
2. Unlock only attendance entry cells (C4:AG50): select → Format Cells → Protection → Locked ✗
3. Review → Protect Sheet → set password → allow "Select unlocked cells" only

Now users can only enter P/A/L in attendance cells. Headers, formulas, and summaries are protected.

Formulas Used in This Project

• COUNTIF — count P, A, L entries per employee
• WEEKDAY — detect Saturday/Sunday
• DATE — build date from month/year dropdowns
• EOMONTH — last day of the month
• NETWORKDAYS — working days count
• MATCH — convert month name to number
• IF — auto-fill WO for weekends
• TEXT — show day names (Mon, Tue...)
• DAY — extract day number for validation