How to Create Student Attendance Sheet in Excel with Formula

In this tutorial, I am going to show you how to create student attendance sheets in Excel with formulas in a few easy steps. This type of monthly attendance sheet enables you to keep records of the students absent or present in a class. It also helps to monitor the regularity of the students and preserve this data for future requirements. In the following section, we will create a simple and ready-to-use monthly attendance sheet with predefined formulas to save a huge amount of time.

How to Create Student Attendance Sheet in Excel with Formula: Step-by-Step Procedures

Step 1: Adding Serial No. and Student Name Column

In this first step, we will add 2 columns to denote the student’s serial number and name. We will also insert some sample names inside our datasheet.

• First, click on cell B5 and type Serial No.
• Next, select cell C5 and type Student Name.
• Here, enter the students’ names and serial numbers.

Step 2: Typing Month Name and Days

We will be making this attendance sheet using January as the sample month. So, we will insert 31 columns representing each day of the month.

• To begin this step, enter the month name in cell D4 and merge cells from D4 to AH4.
• Next, enter the days starting from cell D5.
• Note that, after filling the first few days, you can drag the Fill Handle to the right to fill the series.

Step 3: Inserting Absent and Present Columns with Formula

In this step, we will add two more columns to count the number of days a student was absent or present. For this, we will apply the COUNTIF function. This is a premade function in Excel that counts the number of cells in a range that fulfill a specified condition.

• Next, click on cell AI and enter the Absent column header.
• Similarly, go to cell AJ and type the column header Present.

• Now, double-click on cell AI6 and enter the following formula:

• In the same way, double-click on cell AJ6 and type in the following formula:

• After that, you will see zeros as the values of the two previous cells. This is because we do not have data on our attendance sheet till now.
• Next, drag the Fill Handle down from the lower-right corner of the two cells AI6 and AJ6.
• Consequently, this will copy the formula of the two cells to all the cells below.

• Then, if the formula copying was successful, you will notice zeros in all the cells of the Absent and Present columns.
• At this point, the attendance sheet is complete and ready for use.

Step 4: Entering Attendance Data

Now that our attendance sheet is complete, we will try it out by inserting attendance data for each student. Here we will use P to indicate a student is present and A to indicate absent.

• Here, enter student attendance data in the empty cells for each day.
• Also, as you enter the Present or Absent student data, the formula of column AI and AJ will start counting them.

Things to Remember

• You may want to add some conditional formatting to the data to make it more visual. But try not to make this too complex.
• Try to understand the core structure of the sheet and how to enter data inside it.
• If there are any changes to your student’s data, make sure to modify them accordingly before entering the data for a new month.
• Remember that, you only need to enter the student’s information once and then copy them to the sheets for other months.
• If you have a large number of students, you can expand this sheet to fit your needs.

Download Free Template

You can download the free template from here.

Conclusion

I hope that you understood all the steps to create a student attendance sheet in Excel with the formula. You can use these same steps to create sheets for other months and make slight modifications. Also, you can easily download the template I have provided and immediately start entering your own data inside it. If you have any queries, please let me know in the comments.

<< Go Back to Excel Templates for Education | Excel Templates