You must be in search of an easy process to make an attendance sheet in Excel if you are an employer or a teacher. Usually, an attendance sheet is required to keep track of attendance. For business, an attendance tracking system is purely necessary.
Here you will find the most accessible procedures to learn how to make an attendance sheet in Excel with a formula.
What is an Attendance Sheet?
You already know what an attendance sheet is. It helps keep records of the attendance of employees, students, teachers, etc. regularly. Generally, this sheet is directly linked to the salary of the employees because it helps in calculating the number of days an employee worked and late-ins.
You can easily make Excel attendance sheet by following the methods given here.
Things Recorded in an Attendance Sheet
You can use it to record multiple values such as:
- Sick leaves, personal leaves, paid leaves, vacation leaves
- The employee’s break time
- Presence and absence of an employee or a student
- Marks of a student
- Academic results of a student
How to Make an Attendance Sheet in Excel with Formula?
Below you will find a step-by-step guide to understanding how to make attendance sheet in Excel.
- First, you need to add a serial number and a Student Name Column. For this, click on cell B5 and enter Serial No.
- Now, choose cell C5 and enter your Student Name.
- Add the name of the student and serial numbers.
- In the next step, you need to type the month’s name and days. For this, we will start in January and add 31 columns for each day.
- Now, add the month name in cell D4 and merge cells from D4 to AH4.
- Put the days starting from cell D5. And note that once you add the first few days, you can drag the Fill Handle to the right side.
- In the next step, you need to enter the absent and present columns with the formula. For this, we will add further two columns to calculate the number of days a student was present or absent. And here we need to apply the COUNTIF function. Using this function will help you count the number of cells in a range.
- Next, choose the cell AI and add the Absent column header.
- Now, go to cell AJ and enter the column header Present.
Double-click on cell AI6 and put the below-given formula:
=COUNTIF(D6:AH6, “A”)
Likewise, double-click on cell AJ6 and enter the following formula:
=COUNTIF(D6:AH6, “P”)
Now, you will notice zeros given as the values of the two earlier cells. That’s because you have no data on the attendance sheet till now.
Drag the Fill Handle down from the bottom right side of the two cells AI6 and AJ6.
As a result, you will easily copy the two cells to all the cells.
Once formula copying is done, you will see zeros are removed by default in the cells of Absent and Present Columns.
Now, your attendance sheet is ready to be used.
In the next step, you will add attendance data to the sheet. In the sheet, P is used as Present and A is used to show Absentees.
Fill all the empty cells with the student data and enter Present or Absent data. The formula will start calculating the data of columns AI and AJ.
Isn’t it easy? The attendance calculator in Excel works on the basis of present absent formula in excel. Ultimately, you understand the idea of how to create attendance sheet in excel.
Summing Up
Hopefully, you have learned how to make attendance sheet in Excel with Formula. Each step is explained thoroughly and you can keep a write paper with record of each student without creating a mess. With slight modifications, you can make more sheets for other months.
