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

Attendance sheets are needed in schools, universities, and businesses to keep records of students’ and employees’ attendance. Physical handbooks are very difficult to maintain and preserve over time. Fortunately, we have Microsoft Excel to help us get rid of this annoyance. One can use this software to create a simple but effective attendance sheet to keep records of the students. In this article, we will discuss 2 methods to make a daily attendance sheet in Excel with some beautiful examples and explanations. So without further delay, let’s get started.

Here, we will learn two ways to make a daily attendance sheet in Excel. One of them is creating a daily attendance sheet manually and the other one is making a fully automated daily attendance sheet in Excel. The steps for the two methods are discussed below.

1. Creating a Daily Attendance Sheet in Excel Manually

In this method, we will learn to create a daily attendance sheet manually in Excel. The steps for this method are very simple. They are below:

Steps:

To begin, open a new Excel workbook.
Then, write the heading of Names and the Weekdays in a row.

Now, select the heading of Names and go to the Home tab.
After that, click the Fill Color drop-down menu in the Font group.
At this time, select any color you wish from the drop-down menu.
Thus, we can change the color of the heading cells.

To make the fonts of the headings Bold, first, select the cells.
Next, go to the Home tab and click on Bold in the Font group.
In this way, we can make all the texts of the other headings bold and colorful.

At this time, you need to write the names of the students below the Name heading.
Afterward, insert a border across the cells covered in the sheet. For this, go to the Home tab and select any border from the Border drop-down menu in the Font group.

Here, you need to give a title to the attendance sheet.
To do so, first of all, write the name of the title in the B2 cell.

Accordingly, select the B2 cell and drag it throughout column G where the headings end.
Subsequently, go to the Home tab.
Then, select Merge & Center from the Merge & Center drop-down in the Alignment group.

This way, we can Merge the cells of the title and place them in the Center.

Later, select the title and go to the Home tab again.
Consequently, click the Cell Styles drop-down menu in the Styles group.

In turn, a window containing different cell styles will open.
At this moment, select any Heading Style from the Titles and Headings In our case, we selected the Heading 2 option.

And so on, we can change the cell style of the title of the attendance sheet. The result is in the picture below.

Now, we can change the size of the font of the title by selecting:

Cell B2 > Home > Font Size dropdown in the Font group.

Here, we have selected 14 Font Size for our title.
We also made the cell alignment Middle from the alignment group.

In the end, we will see the final result of our attendance sheet the same as the picture below:

2. Making a Fully Automated Daily Attendance Sheet in Excel

This method will walk you through the process of creating a fully automated daily attendance sheet in Microsoft Excel. We will go over each step in great detail in order to fully automate the attendance sheet. It takes only 10 minutes to create a fully automated daily attendance sheet! Let’s begin by making our first automated turnout sheet in Excel. The steps to do so are below.

Step 1: Creating Year and Month Menu for Daily Attendance Sheet

In the beginning, type Year and Month in the upper cells of the worksheet. See the picture below for a better understanding.

Later, make a new sheet and make a list of all the months.

Secondly, return to the sheet containing the attendance sheet.
Next, select the blank cell beside the month cell.
Go to the Data tab and click the drop-down menu of Data Tools.
Afterward, select Data Validation from the drop-down.

Consequently, a Data Validation window will pop up.
Thirdly, go to the Settings tab and in the Allow menu, choose List.
Now, go to the month names sheet by clicking on Source.
After selecting the month names list click OK.

Thus, we can create a Month menu.

Here, we need to create a Year menu. The steps for this are the same as creating the Month menu.
However, you can simplify your work by entering three or four years successively which are separated by commas in the Source box of the Data Validation window. The screenshot below will help you understand the steps clearly.

Hence, the years will be included in the drop-down menu. And so on, our Year menu is also ready.

So, we can select any Year and Month as per our requirement. For example, we selected 2022 and August from the Year and Month menu respectively.

Step 2: Adding the First and Last Days of the Month to the Daily Attendance Sheet in Excel

The following steps are to include the first and last days of the month in the attendance sheet. The steps to do so are given below:

Firstly, skip one cell below the Month name cell and enter the DATEVALUE formula.
Type the formula below:

=DATEVALUE("1"&C5&C4)

Here, C5 refers to the Month name and C4 refers to the Year respectively.

After pressing Enter, we will notice that the date is in the Number form.
Now, if we want to change its format, first of all, we need to right-click the cell.
Then, we need to select Format Cells.

In turn, the Format Cells dialog box will pop up.
At this moment, we need to go to the Number tab and select Date from Category.
Afterward, select a date format as per your wish from the Type dropdown. Moreover, you can also change the location for getting different types of date formats.
At last, click on the OK button.

Consequently, the date will appear in the desired format.

Now, changing the Year (D4) and Month (D5) in the Year and Month menu will automatically update the first day cell (B7).

After that, to show the last date of any month besides the first date, we need to follow the steps below:

Firstly, skip a cell next to the first date and type the EOMONTH function:

=EOMONTH(B7,0)

Here, B7 denotes the first date just formed.

Then, press ENTER. Again, we will get the date in a number format.
To solve this problem, either we can follow the previous steps or use the Format Painter tool to apply the formatting from the first date cell to the last date cell.
Subsequently, we can see that the first and last dates are as desired in their respective cells.
Changing the month from the drop-down menu will automatically adjust the first and last dates.

Step 3: Filling up Serial No. and Name Columns of the Attendance Sheet

Now, we need to complete the main attendance sheet. So, without further delay, let’s get started.

First, type Serial No. and Name as heading on the top of their respective columns.
Next, type all the serial numbers and names.

Step 4: Entering the Dates of the Month in the Daily Attendance Sheet in Excel

At this time, we will start entering the dates. To do so:

Firstly, we need to select the cell where we want the dates to start.
Then, type the formula:

=B7

Here, B7 refers to the cell of the first date which we have already created.

Pressing the Enter button, we will get the date identical to the first date.

Now, we need to change the format of the date. To do so:

First, right-click the new date and select Format Cells.

Hence, the Format Cells dialog box will open up.
After that, go to the Number tab > Category > Custom > Type > dd > OK.

For a better understanding, see the picture below.

Thus, we will notice the date visible as “01“.

Next, we need to add the second date of the corresponding month.

For this, select the blank cell beside the first date and type the IF function:

=IF(E9<$D$7,E9+1,"")

Here, E9 denotes the first date we just created and D7 is the last date of the month. Now, the reason for using the $ sign before the row and column numbers of D7 is to lock the cell.

The formula expresses that when the first date is smaller than the last date, add the next date. If it’s not true then leave it empty (“”).

When we press Enter, we will again see the Number format. To solve this issue, copy the formatting from the neighboring cell using the Format Painter tool.
Later, we will see the result just like the picture below:

Here, if we want to add all the dates of the month then,

We need to drag the cell of the second date to the right till we reach the month’s last date.
Furthermore, we can also format the cells of the dates to make it more representable.

Step 5: Change Formatting of the Days of the Month and Add Weekdays to the Daily Attendance Sheet

However, if we want to change the formatting of the first and last date on top of the table first, we need to select the desired cell.
Second, right-click the cell and select Format Cells.

After the appearance of the Format Cells window follows the steps below:

Number tab > Category > Custom > Type > dd mmm > OK.

See the picture below for a better understanding.

Now, the top two dates will be visible like in the picture below:

The following step includes weekdays in the sheet. For doing this, follow the steps below:

In the beginning, click on the empty cell below the first date and type the TEXT function:

=TEXT(E9,"ddd")

Here, E9 denotes the first date.

To see the result, press the Enter button. Now, we can see that the weekday on the first date of the month is displayed in the cell (E10).

Finally, to get all weekdays, drag the first cell until the last date of the corresponding month.
So far, the attendance sheet will look like the screenshot below.

In the end, we can add a title to our daily attendance sheet to make it more attractive. The procedure to do so is already discussed in the previous method.

Download Practice Workbook

Download the practice workbook from here.

Excel Attendance Sheet.xlsx

Conclusion

I hope the above methods will be helpful for you to make daily attendance in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

John Sep 4, 2023 at 3:33 PM

How can I make the table empty and save data for each year and month? As is, when I change the month (or the year) I have to clear the table and start anew. And it saves the values of one month for all the months.
- Reply
  Sourav Kundu Sep 11, 2023 at 1:23 PM
  Hi JOHN,
  Thank you for sharing your problem with us. We have got a simple solution to your problem. You can use a VBA event for this purpose. Whenever you need a blank attendance sheet for a new month, you just have to change the value of the green-colored cell G5.
  First, Right-click on your worksheet named Automated.
  Then, click on View Code.
  
  Then copy the following VBA code and paste it into the opening window:
```
Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Me.Range("G5")) Is Nothing Then
   
      Me.Copy After:=Sheets(Sheets.Count)
      Me.Range("E11:AI100").ClearContents
    End If
End Sub
```
  Now, go back to the Automated sheet and insert Daily Attendences.
  
  Now, write something in the G5 cell say N, and press Enter.
  Instantaneously, the Automated sheet will be cleared and will create a new sheet Automated (2).
  
  Now, go to the Automated (2) sheet and you can see the previous attendance record has been copied to this sheet.
  
  The original worksheet is blank and is ready to take instructions from scratch.
  
  Note: You can change the green color cell reference from the VBA code.
  
  We hope that we have provided a reasonable solution to your problem. If you have more queries you can always mention them in the comment section.
  
  Regards,
  Sourav Kundu
  Exceldemy.