How to Make Automated Attendance Sheet in Excel (Easy Steps)

We often use automated attendance sheets to keep records of the attendance of the employees or students. Using Excel, we can make automated attendance sheets by following some simple steps. In this article, we are going to learn these easy steps to make an Automated Attendance Sheet in Excel.

Importance of an Automated Attendance Sheet in Excel

An Automated Attendance Sheet is used to keep track of the attendance of an organization’s employees or students. It is very tiresome work to maintain attendance for a long period if it is recorded manually. So, here comes the automated attendance sheet to save us both effort and time. Using an Automated Attendance Sheet is necessary for these reasons.

In this section of the article, we will discuss 8 easy steps to create an Automated Attendance Sheet in Excel. In the following dataset, we have Employee Details of a company. We will make an Automated Attendance Sheet for them using Excel.

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

Step 01: Creating Support Sheet for Automated Attendance Sheet in Excel

The first step to creating an automated attendance sheet in Excel is to create a Support Sheet that will help us later.

• Firstly, in the Month Name column, enter the name of 12 months as shown in the following picture.

• Following that, in the National Holidays column insert the list of National Holidays based on the region you are living in. Here, we have used the Federal Holidays of the USA.

• Finally, enter the name of the weekdays in the column named Weekday.

Step 02: Creating Month and Year List for Attendance Sheet with Excel Data Validation

Now, we will create a Month and a Year List in a new worksheet. To do this, let’s follow the steps mentioned below.

• Firstly, select cell C4 as marked in the image given below.
• After that, go to the Data tab from the ribbon.
• Following that, click on the Data Validation tab from the Data Tools group.

Consequently, the Data Validation dialogue box will open.

• Now, from the Data Validation dialogue box, click on the drop-down icon as marked in the following image.
• Then, select List from the drop-down.

• Afterward, to select the source of our data click on the marked portion of the following screenshot.

• Subsequently, select all the months under the Month Name column of the Support Sheet.

• Finally, select OK.

As a result, a drop-down icon will be available beside cell C4 like the image given below.

By using this drop-down button you can select any month according to your choice. In this case, we have selected the month of July.

• Now, click on cell C5.
• After that, go to the Data tab from the Ribbon.
• Following that, click on the Data Validation tab from the Data Tools group.

• Subsequently, choose List from the drop-down menu.
• After that, in the Source box, write the name of the years as shown in the following picture.
• Finally, click on OK.

At this stage, you will see a drop-down button available beside cell C5.

Consequently, you can choose any year from the available options by using the drop-down button. Here, we chose the year 2022.

At this stage, we will create the start date and end date of each month by using the DATEVALUE function and EOMONTH function.

• Firstly, in cell C7 enter the following formula to obtain the first date of the selected month.

Here, cell C4 indicates the chosen Month and cell C5 represents the selected Year.

• Then, press ENTER.

Subsequently, you will get the first day of the selected month as shown in the following image.

• Now, in cell E7, insert the following formula to get the last date of the selected month.

Here, C7 represents the start date of the month, and 0 means that the function will return the last date of the current month.

• Following that, hit ENTER.

Consequently, you will get the end date of the selected month. In this case, it is 31 July (31 Jul).

Step 03: Assigning Weekends in the Automated Attendance Sheet in Excel

In this step, we will assign Weekly Off Days. Weekly off days varies from organization to organization. But the most common weekly off days is Saturday and Sunday. However, you can choose any day according to your convenience by following the steps mentioned below.

• Firstly, select cell C9 as marked in the image given below.
• After that, go to the Data tab from the Ribbon.
• Next, select the Data Validation option.

• Following that, from the Data Validation dialogue box, choose List from the drop-down menu.
• Afterward, click on the marked region to select the Source.

• Subsequently, select the weekdays under the Weekday column from the Support Sheet worksheet.
• After that, click on the marked portion of the following image.

• Lastly, click on OK.

• At this stage, a drop-down button will be available beside cell C9, and click on that.
• After that choose your weekly off day. In this case, we have chosen Saturday (Sat).

Consequently, you will see Saturday (Sat) is selected as a weekly off day as shown in the following picture.

By following the same steps, select your other weekly off day, Here, we have chosen Sunday (Sun) as our 2nd weekly off day.

Step 04: Entering Dates and Weekdays to the Attendance Sheet

The next step to create an automated attendance sheet in Excel is to insert the days and respective weekdays of the selected month into our worksheet. Let’s follow the steps discussed below to do this.

• Firstly, in cell F11 enter the following formula.

• After that, hit ENTER.

Whoa! What is this weird number? Don’t worry. We just need to change the cell format.

To change the cell format,  we will the steps that are given below.

• Firstly, right-click on cell F11.
• Following that, select the Format Cells option.

As a result, the Format Cells dialogue box will open as shown in the following image.

• Next, click on the Custom option from the Format Cells dialogue box.
• Afterward, type dd in the Type box as marked in the image given below.
• Then, click OK.

Consequently, you will see the following output on your worksheet.

To obtain the rest of the days of the selected month we will use the IF function. The logic is if the cell value is less than the end date of the month then increase the cell value by 1. This means increasing cell value by 1 day. But when this condition will not be true, then replace the following cell with a blank.

• Now, we can use the following formula in cell G11.

Here, F11 is the 1st date of the month. $E$7 is the end date of the selected month.

Note: We have used Absolute Cell Referencing for cell E7 ($E$7). Because we need to compare each cell with this same cell.

• Consequently, by dragging the Fill Handle, we will get the rest of the days of the selected month.

At this stage, we will enter the names of the weekdays. Here will use the TEXT function to add the names of the weekdays. Let’s follow the steps mentioned below.

• Firstly, enter the formula given below in cell F12.

Here, ddd is the format of the date. This means the output will contain the first 3 letters of weekdays.

• Next, press ENTER.

• Following that, use the Fill Handle to obtain the rest of the data as shown in the image given below.

Step 05: Using Conditional Formatting to Highlight Weekends

Now, we will use the Conditional Formatting feature of Excel to mark both the National Holidays and the Weekly Off Days by following the steps mentioned below.

• Firstly, select cell F13 as marked in the image given below.
• After that, select the Conditional Formatting option.
• Then, choose New Rule from the drop-down.

Consequently, the New Formatting Rule dialogue box will open as shown in the following picture.

• Following that, select the Use a formula to determine which cells to format option.
• Next, insert the following formula in the Format values where this formula is true box.

Here, $D$9 represents a Weekly Off Day (Sunday).

• After that, click on the Format option and select your preferred formatting options.
• Finally, click on OK.

Note: Here we have used Absolute Cell Referencing for cell D9 ($D$9), but for cell F12 we only fixed the Row Number. Because this will allow us to copy the formatting for all cells across the same row.

• Now, select the newly created rule.
• Following that, click on Duplicate Rule as marked in the following image.

• After that, select the duplicated rule and then click on Edit Rule.

• Then, enter the following formula in the Format values where this formula is true box.

Here. $C$9 indicates another Weekly Off Day (Saturday).

• Afterward, click on OK.

• Now, click on Apply from the Conditional Formatting Rules Manager dialogue box.
• Next, click on OK.

At this stage, follow the previously mentioned steps to open the New Formatting Rule dialogue box as shown in the image given below.

• Following that, select the Use a formula to determine which cells to format option.
• Next, insert the following formula in the Format values where this formula is true box.

• After that, click on the Format option and select your preferred formatting options.
• Finally, click on OK.

Note: Here, we have used the MATCH function to highlight the National Holidays throughout the year.

• Subsequently, select Apply.
• Then, click on OK.

• At this stage, select cell F13 as marked in the following picture.
• After that, click on the Format Painter icon.

• Following that, select the cells F13:AJ17 to paste the copied formatting in the cells F13:AJ17.

• Consequently, you will see that the Weekly Off Days and the National Holidays are marked according to your specified formatting.

Step 06: Entering Attendance Data in the Automated Attendance Sheet in Excel

In this step, we will create a drop-down option for inserting attendance data that is present (P) or absent (A). To do this, we will follow the steps mentioned below.

• Firstly, select the cells F13:AJ17.
• After that, go to the Data tab from the Ribbon.
• Subsequently, click on the Data Validation option.

• Following that, from the Data Validation dialogue box, choose List from the drop-down.
• Then, in the Source box type P,A.
• Now, click OK.

• At this stage, click on the drop-down button beside cell F13.
• Choose P if the employee was present on that day. On the other hand, choose A if the employee was absent on that day.

• Similarly, for the other cells input the relevant attendance records or respective employees.

Step 07: Using Excel COUNTIF Function to Automatically Count Attendance

At this stage, we will use the COUNTIF function to calculate the number of Total Present and the Total Absent days. To do this, let’s use the following steps.

• Firstly, enter the following formula in cell AK13.

Here, F13:AJ13 is the range of the sum and P is the sum criteria.

• Following that, hit ENTER.

• Subsequently, by dragging the Fill Handle, we will obtain the rest of the data as marked in the following image.

• Now, insert the following formula in cell AL13.

Here, A is the sum criteria.

• After that, press ENTER.

• Consequently, drag the Fill Handle and you will get the following output as marked in the image given below.

How to Use Formula for Late Attendance in Excel

Step 08: Saving Automated Attendance Sheet as Template

Now, we will save the workbook as Template. As a result, we will be able to use this template in the future without going through all the above-mentioned steps. Let’s use the following steps to do this.

• Firstly, go to the Files tab from the Ribbon.

• Following that, select the Save As option as marked in the following screenshot.

• After that, rename the file according to your convenience.
• Then, choose the Excel Template (*.xltx) from the drop-down list.
• Finally, click on Save.

• To check the newly saved template, click on the New option as shown in the following picture.
• Click on the Personal tab.

There you go! You can now see your saved template. You can simply click on it and start using it.

💡 Things to Remember

• While saving the file as Template, don’t use a customized destination to save. Just use the default path suggested by Excel. This will automatically save the template to the Custom Office Template folder.

Download Practice Workbook

Conclusion

Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to make automated attendance sheet in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.

<< Go Back to Attendance | Formula List | Learn Excel