Nazmul Hossain Shovon
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.
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.
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.
- Now, double-click on cell AI6 and enter the following formula:
=COUNTIF(D6:AH6,"A")
- 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.
If i want to write a absent date 8n Excel shit
Hi Shruti,
I have attached an Excel file that you can use to find specific absent dates. Hope it helps!
Download link