How to Create Employee Attendance Sheet in Excel with Automatic Formula

Walter Jul 15, 2026

Creating an attendance sheet in Excel can significantly streamline employee management, and incorporating formulas enhances its automating capability. Here's a step-by-step guide to help you create an efficient employee attendance sheet with formulas.

Easy Attendance Sheet Tutorial
Easy Attendance Sheet Tutorial

Before diving in, ensure you have the latest version of Microsoft Excel installed on your computer. Let's start by setting up the basic structure of the attendance sheet.

Employee Attendance Tracker | Free Excel Attendance Spread | Updated for 2026 | Leave Board
Employee Attendance Tracker | Free Excel Attendance Spread | Updated for 2026 | Leave Board

Basic Setup

Begin by creating headers for your sheet. In the top row, list the dates you want to track. For simplicity, let's consider a month with 30 days:

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

Date,EmployeeID,Full Name,1,2,3,...,29,30

Adding Employees

Fully Automatic Advance Attendance sheet in Excel
Fully Automatic Advance Attendance sheet in Excel

In the row below the headers, list your employees starting from 'EmployeeID' to 'Full Name'. For instance:

ID,John Doe,Jane Smith,...

Adding Dates

How To Make attendance sheet for beginners excel
How To Make attendance sheet for beginners excel

In the first row of the dates column, input the dates, separated by commas, and copy-paste them to the remaining cells. You can easily adjust the dates if needed.

Creating Attendance Formulas

To track attendance, we'll use two formulas: 'COUNTIF' to count present days and 'TODAY' to fetch the current date.

Attendance Tracker in Excel
Attendance Tracker in Excel

Creating Criteria for Presence

In the second row of dates (Education Day 1), type the following formula to identify if an employee is present. Replace 'EmployeeID' and 'Date' with the appropriate cell references:

Attendance Sheet of Employees  | How to Use Countif Function in Excel | Calculate Total Number 🤯
Attendance Sheet of Employees | How to Use Countif Function in Excel | Calculate Total Number 🤯
2021 Employee Attendance Calendar Excel
2021 Employee Attendance Calendar Excel
Automate Attendance In Excel
Automate Attendance In Excel
How To Make Attendance Sheet In Ms Excel
How To Make Attendance Sheet In Ms Excel
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”
🔥🔥 Fully Automated Attendance Sheet in Excel
🔥🔥 Fully Automated Attendance Sheet in Excel
Tips Excel (@noir_myspecial1) on X
Tips Excel (@noir_myspecial1) on X
"Excel Trick: Create a Student Attendance Sheet Fast!"
"Excel Trick: Create a Student Attendance Sheet Fast!"
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”

=IF(ISNUMBER(SEARCH("P",INDIRECT("EmployeeID"&"."&"Date"))),"Present","")

This formula checks if the string "P" (representing present) is in the cell where 'EmployeeID' and 'Date' intersect. If it is, it returns 'Present'; otherwise, it's blank.

Auto-filling Formulas

Drag the filled formula to the remaining dates to fill the sheet. Now, your attendance sheet is ready to be used.

Tracking Absences

To make the sheet more informative, let's track absences as well. We'll use the 'NETWORKDAYS' and 'TODAY' functions to calculate the total number of working days and absent days automatically.

Total Working Days

In a new cell (e.g., E2), input the following formula to calculate the total number of working days in the given period:

=NETWORKDAYS("2022/1/1", "2022/31/1")

Total Absent Days

In cell F2, use the following formula to determine the total number of absent days for each employee:

=NETWORKDAYS("2022/1/1", "2022/31/1") - COUNTIF(E2:AG$2,"P")

Adjust the date range and the range of counts accordingly. This formula calculates the total number of working days and subtracts the number of present days to give you the total absentee days for the month.

Now, whenever you need to update the attendance, simply type 'P' for present and 'A' for absent in the respective cells. The formulas will automatically update, saving you time and ensuring accuracy.