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

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


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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

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