Excel Guide: Create Employee Attendance Sheet

Walter Jul 15, 2026

Keeping track of employee attendance can be a daunting task, but Microsoft Excel makes it manageable. With its robust features and ease of use, you can create an efficient employee attendance sheet to monitor and analyze absences, tardiness, and leaves. Here's a step-by-step guide to help you create your own employee attendance sheet in Excel.

Efficient Absence & Vacation Tracker for Staff & Students in Excel
Efficient Absence & Vacation Tracker for Staff & Students in Excel

Before we dive into the specifics, ensure you have a spreadsheet software like Microsoft Excel or its alternatives, like OpenOffice Calc or Google Sheets, installed on your computer. For this guide, we'll use Excel as a reference.

an employee attendance tracker with the text employee attendance tracker, and free printable workbook
an employee attendance tracker with the text employee attendance tracker, and free printable workbook

Setting Up Your Workbook

Begin by opening a new workbook in Excel. This will serve as the foundation for your attendance sheet.

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

Next, name the workbook "Employee Attendance" for easy identification. You can do this by right-clicking on the workbook's tab at the bottom-left corner of the Excel window and selecting "Rename."

Creating the Attendance Sheet

Employee Attendance Calendar
Employee Attendance Calendar

Now, let's make the actual attendance sheet. Click on "Sheet1" tab at the bottom of the window and rename it "Attendance."

To simplify the process, you'll create different sheets for each month. However, for now, we'll focus on creating the main attendance sheet. We'll cover adding monthly sheets later.

Defining the Header

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

In the "Attendance" sheet, starting from cell A1, create the following header:

  • Employee ID (A1)
  • Employee Name (B1)
  • Date (C1)
  • Status (D1)

The "Status" column will help you track whether an employee is Present (P), Absent (A), Late (L), or On Leave (L).

Attendance Tracker Excel | Template Business
Attendance Tracker Excel | Template Business

Populating Your Attendance Sheet

Once you've set up the header, you can start populating the sheet with employee information and dates.

Fully Automatic Advance Attendance sheet in Excel
Fully Automatic Advance Attendance sheet in Excel
How to make Attendance Sheet with checkboxes in #Excel
How to make Attendance Sheet with checkboxes in #Excel
Easy Attendance Sheet Tutorial
Easy Attendance Sheet Tutorial
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 🤯
Automated Attendance Sheet in Excel
Automated Attendance Sheet in Excel
How to build an attendance tracker
How to build an attendance tracker
🔥🔥 Fully Automated Attendance Sheet in Excel
🔥🔥 Fully Automated Attendance Sheet in Excel
How to Find Working Hours and Calculate Totals in Attendance Sheets – A Step-by-Step Guide
How to Find Working Hours and Calculate Totals in Attendance Sheets – A Step-by-Step Guide

To add employee names and IDs, you can either manually input them or use a data import function if you have a pre-existing document.

Adding Dates

Now, let's add dates. In cell C2, enter the first date of the month (e.g., "2022-01-01"). Then, drag the small square in the bottom-right corner of the cell down to the last day of the month to auto-populate the dates.

Note: Ensure your rows are wide enough to display the dates by adjusting the column width. You can do this by hovering over the line between the column headers, then dragging it to the desired width.

Formatting Dates

To make your sheet more readable, format the dates. Select the dates (C2:Cn), click the "Number" group under "Home," and choose "Short Date." This will display the dates in a more user-friendly MM/DD/YYYY format.

In the final section of your attendance sheet, add formulas to count the number of days each employee is present, absent, late, or on leave. This will provide a quick overview of each employee's attendance for the month. To do this, use the COUNTIF function in combination with the "Status" column.

For example, to count the number of present days for an employee, use the formula: =COUNTIF(D2:Dn, "P")

Repeat this process for the other statuses, changing "P" to "A," "L," and "L" respectively. Adjust the range (D2:Dn) as needed.

Adding Monthly Sheets

Keeping separate sheets for each month will help manage attendance records more efficiently. To do this, right-click on the "Attendance" sheet tab, select "Move or Copy," check the "Create a copy" box, enter "January" (or the current month) in the "Before sheet" field, and click "OK."

Repeat this process for each subsequent month, adjusting the sheet name accordingly (e.g., "February," "March," etc.).

Transferring Data Between Sheets

To keep your monthly sheets updated, you can use the "Watch" feature to monitor changes in the main attendance sheet. Right-click on the "Formulas" tab in the ribbon, select "Formula Auditing," and click "Watch."

Select the cell containing the COUNTIF function for 'Present' days and click "Add Watch." This will display the watched cells in the "Watch Window" ( press Ctrl + Shift + U to view the window), which will allow you to see updates in real-time.

Regardless of the size of your team, an employee attendance sheet in Excel is a powerful tool for managing and analyzing attendance data. By following this guide, you'll create an efficient, user-friendly, and customizable attendance sheet that meets your organization's needs.

Now that you have your attendance sheet set up, ensure you save your work frequently to avoid losing data. To do this, press Ctrl + S or click the floppy disk icon in the "Quick Access" toolbar. And remember, maintaining an accurate attendance sheet is crucial for fair performance evaluations and streamlined HR processes.

Finally, consider automating certain tasks, like sending reminders to employees nearing their leave limit or generating monthly attendance reports. With Excel's robust features, the possibilities are endless. Happy tracking!