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

Method 1 – Creating a Daily Attendance Sheet in Excel Manually

Steps:

Open a new Excel workbook.
Write the headings Name and weekdays in a row.

Select the heading Names and go to the Home tab.
Click the Fill Color drop-down menu in the Font group.
Select any color from the drop-down menu.
Repeat for the weekday headings.

Select the heading cells.
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.

Insert the names of the students below the Name heading.
Insert a border across the cells covered in the sheet: go to the Home tab and select any border from the Border drop-down menu in the Font group.

Write the title of the sheet in the B2 cell.

Select the B2 cell and drag it throughout column G where the headings end.
Go to the Home tab and select Merge & Center from the Merge & Center drop-down in the Alignment group.

This Merges the cells of the title and places the text in the Center.

Select the title and go to the Home tab again.
Click the Cell Styles drop-down menu in the Styles group.

A window containing different cell styles will open.
Select any Heading Style from the Titles and Headings. In our case, we selected the Heading 2 option.

Make other formatting changes as needed.

Change the size of the font of the title by selecting the Font Size dropdown in the Font group. We have selected 14 Font Size for our title.
We also made the cell alignment Middle from the alignment group.

Here’s our sample attendance sheet.

Method 2 – Making a Fully Automated Daily Attendance Sheet in Excel

Step 1 – Creating the Year and Month Menu for the Daily Attendance Sheet

Type Year and Month in the upper cells of the worksheet and reserve a cell for each of the values.

Make a new sheet and insert a list of all the months.

Return to the sheet containing the attendance sheet.
Select the blank cell beside the month cell.
Go to the Data tab and select Data Validation (under Data Tools).

A Data Validation window will pop up.
Go to the Settings tab and in the Allow menu, choose List.
Click on Source and go to the sheet with month names.
Selecting month names and click OK.

This creates a Month menu.

Create a Year menu as well. The steps for this are the same as creating the Month menu. However, you can simplify your work by entering years successively separated by commas in the Source box of the Data Validation window.

The years will be included in the drop-down menu.

We selected 2022 and August from the Year and Month menus, respectively.

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

Skip one cell below the Month name cell and enter the DATEVALUE formula.
Use the formula below:

=DATEVALUE("1"&C5&C4)

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

After pressing Enter, the date is in the Number form.
Right-click the cell and select Format Cells.

The Format Cells dialog box will pop up.
Go to the Number tab and select Date from Category.
Select a date format from the Type dropdown.
Click on the OK button.

The date will appear in the desired format.

Changing the Year (D4) and Month (D5) in the Year and Month menu will automatically update the first day cell (B7).
Skip a cell next to the first date and use the EOMONTH function in D7:

=EOMONTH(B7,0)

Use the Format Painter tool to apply the formatting from the first date cell to the last date cell or reapply the Date format manually.
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

Insert Serial No. and Name as headings on the top of their respective columns.
Type all the serial numbers and names.

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

Select the cell where you want the dates to start. We chose E9, diagonally above the Name heading.
Use the formula:

=B7

We will get the date identical to the first date.
Right-click the new date and select Format Cells.

The Format Cells dialog box will open up.
Go to the Number tab, choose Custom, and put dd in Type.
Press OK.

The date changes to “01“.

Select the blank cell beside the first date and insert the IF function:

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

When we press Enter, we will again see the Number format. Copy the formatting from the neighboring cell using the Format Painter tool.

We need to drag the cell of the second date to the right until you reach the month’s last date.
Format the cells of the dates to make it more representable.

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

Select the cells for the first and last date on top of the table.
Right-click on the selection and select Format Cells.

In the Format Cells window, put dd mmm in Type (choose the Custom category) and press OK.

Here’s how the cells are formatted.

Click on the empty cell below the first date and insert the TEXT function:

=TEXT(E9,"ddd")

E9 denotes the first date.

Press the Enter button. The weekday on the first date of the month is displayed in the cell (E10).

Drag the first cell until the last date of the corresponding month.
The attendance sheet will look like the screenshot below.

Add a title to the daily attendance sheet to make it more attractive. The procedure to do so was already discussed in Method 1.

Download the Practice Workbook

Excel Attendance Sheet.xlsx

<< Go Back to 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.