Employee Attendance Sheet in Excel

An attendance sheet records which employees were present, absent, or on leave on each day. Many small offices and teams keep it in Excel because it is simple, free, and easy to adjust. The real benefit of Excel is that formulas can count the days and calculate the attendance percentage automatically, so you do not have to count by hand at the end of the month.

This article shows how to build an employee attendance sheet in Excel with formulas, a dropdown for marking attendance, and colours that make the sheet easy to read. To keep the example short, the sheet covers 7 days. The same formulas work for a full month if you extend the day columns.

Attendance Codes Used in the Sheet

Step 1: Create the Sheet Layout

Create the following layout. Columns C to I hold the daily attendance for 1 to 7 October 2026, and columns J to N hold the formulas.

Step 2: Add the Formulas

Enter these formulas in row 2 and copy them down for all employees.

Output:

Format column N as a percentage with one decimal place.

Explanation:

COUNTIF() counts how many cells in the range C2:I2 match the given code. For Amit, "P" appears 6 times and "A" appears once. The attendance percentage adds the present days and half of the half-days, and divides this by the number of days marked in the row. COUNTA() counts the non-empty cells, so the formula works even if only 5 days have been filled so far.

For Neha, the calculation is (5 + 1 × 0.5) / 7 = 0.7857, which is 78.6%. Here, leave is not counted as attendance. If your company treats approved leave as a working day, change the formula to =(J2+L2+M2*0.5)/COUNTA(C2:I2).

Step 3: Add a Dropdown for Marking Attendance

A dropdown prevents spelling mistakes such as "p", "Present", or "Pr", which COUNTIF would not count correctly.

1. Select the attendance cells C2:I4.
2. Go to Data > Data Validation.
3. In Allow, choose List.
4. In Source, type P,A,L,HD.
5. Click OK.

Output:

Each attendance cell now shows a small arrow. Clicking it displays the four codes, and Excel rejects any other entry. If your regional settings use a semicolon as the list separator, type P;A;L;HD instead.

Step 4: Colour the Attendance Codes

Colours help you see absences quickly.

1. Select C2:I4.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.
3. Type A, choose Light Red Fill with Dark Red Text, and click OK.
4. Repeat for P with a green fill and L with a yellow fill.

Output:

All absent days appear in red, present days in green, and leave days in yellow, so the pattern of each employee is visible at a glance.

Step 5: Show the Day Name and Count Working Days

You can show the weekday above each date so that Sundays and Saturdays are easy to spot. If C1 contains a real date such as 01-10-2026 and D1 contains =C1+1, enter this formula in a row above or below the dates:

=TEXT(C1,"ddd")

Output for 1 to 7 October 2026:

To count the working days (Monday to Friday) in this period, use NETWORKDAYS():

=NETWORKDAYS(DATE(2026,10,1),DATE(2026,10,7))

Output:

5

Explanation:

NETWORKDAYS() excludes Saturdays and Sundays. Between 1 and 7 October 2026 there are 5 weekdays: Thursday, Friday, Monday, Tuesday, and Wednesday. We can use this number as the total working days when calculating attendance percentage for a month. The same function can take a list of holiday dates as a third argument to exclude them too.

Important Points About the Attendance Sheet

• Use fixed codes such as P, A, L, and HD, and restrict them with Data Validation.
• COUNTIF() counts each code, and COUNTA() counts the days already marked.
• The attendance percentage formula depends on company policy, so decide whether leave and half days are counted before using it.
• Conditional formatting makes absences easy to see.
• NETWORKDAYS() gives the number of working days between two dates.
• To learn more about COUNTIF, data validation, and conditional formatting, see Excel Interview Questions.