How to Create Student Attendance Sheet in Excel with Formula

In this tutorial, we’ll create a simple Student Attendance sheet with formulas in Excel, that enables recording different students’ attendance in a class over a month, monitoring the regularity of their attendance, and preserving this data for future use.

Step 1 – Adding Serial No and Student Name Columns

• Click on cell B5 and type Serial No.
• Click on cell C5 and type Student Name.
• In the cells below, enter the students’ names and serial numbers.

Step 2 – Entering Month Name and Days

Let’s use January as a sample month, meaning we’ll need 31 columns representing each day of the month.

• Enter the month name in cell D4.
• Merge cells from D4 to AH4.
• Enter the days of the month in row D starting from cell D5.
• Note that, after filling the first few days, you can select them, then drag the Fill Handle to the right to fill the series.

Step 3 – Inserting Absent and Present Columns with a Formula

Let’s add two more columns to count the number of days a student was absent or present, using the COUNTIF function, which counts the number of cells in a range that fulfill a specified condition.

• Click on cell AI5 and enter the column header Absent.
• Likewise, in cell AJ5 enter the column header Present.

• Double-click on cell AI6 and enter the following formula:

• Double-click on cell AJ6 and enter the following formula:

Because we have not added any data in our attendance sheet to count yet, zeroes are filled in cells AI6 and AJ6.

• Select cells AI6 and AJ6.
• Drag the Fill Handle down from the lower-right corner of AJ6 to copy the formula in both cells to all the cells below.

If the formula copying was successful, zeros will appear in all the cells in the Absent and Present columns.

The attendance sheet is complete and ready for use.

Step 4 – Entering Attendance Data

Let’s insert some attendance data for each student. We’ll use P to indicate that a student is present and A to indicate absent.

• Enter P or A in the empty cells for each student and for each day.
• As you enter the data, the formulas in column AI and AJ will automatically count them.

Things to Remember

• To duplicate this sheet for other months, simply copy the blank template (i.e. the attendance sheet at the end of Step 3) and either paste it below on the same sheet as many times as required (leaving a few blank rows in between each paste), or add a new sheet for each extra month required, and past the template into each.
• You may want to add some conditional formatting to the data to make it more visually appealing. But try not to make it too complex.
• Make sure you understand the structure of the sheet and how/where to enter data into it.
• Simply add new rows to capture data for as many students as you need.

Download Free Template

<< Go Back to Excel Templates