Make Automated Attendance Sheet in Excel (With 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.


Download Practice Workbook


Importance of an Automated Attendance Sheet

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 the effort and time. Using an Automated Attendance Sheet is necessary for these reasons.


8 Steps to Make Automated Attendance Sheet in Excel

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.

how to make automated attendance sheet in 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: Constructing Support Sheet

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.

how to make automated attendance sheet in excel Constructing Support Sheet

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

how to make automated attendance sheet in excel Constructing Support Sheet

Read More: How to Create Employee Attendance Sheet with Time in Excel


Step 02: Creating Month and Year List

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.

how to make automated attendance sheet in excel Creating Month and Year List

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.

how to make automated attendance sheet in excel Creating Month and Year List

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

how to make automated attendance sheet in excel Creating Month and Year List

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

how to make automated attendance sheet in excel Creating Month and Year List

  • Finally, select OK.

how to make automated attendance sheet in excel Creating Month and Year List

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.

how to make automated attendance sheet in excel Creating Month and Year List

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

how to make automated attendance sheet in excel Creating Month and Year List

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

how to make automated attendance sheet in excel Creating Month and Year List

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

how to make automated attendance sheet in excel Creating Month and Year List

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.
=DATEVALUE("1"&C4&C5)

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

  • Then, press ENTER.

how to make automated attendance sheet in excel Creating Month and Year List

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.
=DATEVALUE("1"&C4&C5)

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.

how to make automated attendance sheet in excel Creating Month and Year List

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

how to make automated attendance sheet in excel Creating Month and Year List

Read More: How to Create a Monthly Staff Attendance Sheet in Excel


Step 03: Assigning Weekly Off Days

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.

how to make automated attendance sheet in excel Assigning Weekly Off Days

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

how to make automated attendance sheet in excel Assigning Weekly Off Days

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

how to make automated attendance sheet in excel Assigning Weekly Off Days

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.

how to make automated attendance sheet in excel Assigning Weekly Off Days

Read More: How to Make Daily Attendance Sheet in Excel (2 Effective Ways)


Step 04: Entering Dates and Weekdays

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.
=C7
  • After that, hit ENTER.

how to make automated attendance sheet in excel Entering Dates and Weekdays


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

how to make automated attendance sheet in excel Entering Dates and Weekdays

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.

how to make automated attendance sheet in excel Entering Dates and Weekdays

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

how to make automated attendance sheet in excel Entering Dates and Weekdays

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.
=IF(F11<$E$7,F11+1,"")

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.

how to make automated attendance sheet in excel Entering Dates and Weekdays

  • 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.
=TEXT(F11,"ddd")

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

  • Next, press ENTER.

how to make automated attendance sheet in excel Entering Dates and Weekdays

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

how to make automated attendance sheet in excel Entering Dates and Weekdays

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


Step 05: Using Conditional Formatting for Off Days

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.

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

  • 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.
=F$12=$D$9

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.

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

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.

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

  • Then, enter the following formula in the Format values where this formula is true box.
=F$12=$C$9

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

  • Afterward, click on OK.

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

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

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

  • 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.
=MATCH(F$11,'Support Sheet'!$C$5:$C$15,0)
  • After that, click on the Format option and select your preferred formatting options.
  • Finally, click on OK.

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

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.

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

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

how to make automated attendance sheet in excel Using Conditional Formatting for Off Days

Read More: How to Create Monthly Attendance Sheet in Excel with Formula


Step 06: Inserting Attendance Data

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.

how to make automated attendance sheet in excel Inserting Attendance Data

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

how to make automated attendance sheet in excel Inserting Attendance Data

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

how to make automated attendance sheet in excel Inserting Attendance Data

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


Step 07: Using COUNTIF Function

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.
=COUNTIF(F13:AJ13,"P")

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.

how to make automated attendance sheet in excel Using COUNTIF Function

  • Now, insert the following formula in cell AL13.
=COUNTIF(F13:AJ13,"A")

Here, A is the sum criteria.

  • After that, press ENTER.

how to make automated attendance sheet in excel Using COUNTIF Function

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

Read More: How to Create Student Attendance Sheet in Excel with Formula


Step 08: Saving 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.

how to make automated attendance sheet in excel

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

how to make automated attendance sheet in excel

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

how to make automated attendance sheet in excel

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.

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. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo