How to Create Student Attendance Sheet in Excel with Formula

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you how to create student attendance sheet in Excel with formula 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.


Download Free Template

You can download the free template from here.


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

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.

Create Student Attendance Sheet in Excel with Formula


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.

Create Student Attendance Sheet in Excel with Formula

Read More: How to Track Attendance in Excel (with Detailed Steps)


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.

Create Student Attendance Sheet in Excel with Formula

  • Now, double-click on cell AI6 and enter the following formula:
=COUNTIF(D6:AH6,"A")

Create Student Attendance Sheet in Excel with Formula

  • In the same way, double-click on cell AJ6 and type in the following formula:
=COUNTIF(D6:AH6,"P")

  • 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.

Read More: Attendance Sheet in Excel with Formula for Half Day (3 Examples)


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.

Read More: Attendance Sheet with Salary in Excel Format (with Easy Steps)


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 occur any changes to your student’s data, make sure to modify them accordingly before entering the data of 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.

Conclusion

I hope that you understood all the steps to create a student attendance sheet in excel with 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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

2 Comments
  1. If i want to write a absent date 8n Excel shit

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo