Yearly Attendance Sheet Excel Template with Formula - Free Download

Walter Jul 15, 2026

Streamlining attendance tracking for the year can be a daunting task, but with the right tools, it becomes manageable. An Excel-based attendance sheet, with the assistance of formulas, can help automate and simplify this process. In this guide, we'll explore how to create and utilize a yearly attendance sheet in Excel, complete with essential formulas for automated calculations. Best of all, you can download the template for free and customize it to fit your specific needs.

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

Before we dive into the details, ensure you have Microsoft Excel 2010 or later installed on your computer, as the provided template and formulas are compatible with these versions.

the attendance list is shown in this printable sheet for students to practice their work
the attendance list is shown in this printable sheet for students to practice their work

Setting Up the Yearly Attendance Sheet

To start, you'll need to create a new Excel workbook and name the active sheet "Attendance". This sheet will be the foundation for tracking yearly attendance.

2021 Employee Attendance Calendar Excel
2021 Employee Attendance Calendar Excel

Next, organize the sheet by adding the following headers to the first row: "Employee Name", "Week Starting", "Present Days", "Absent Days", and "Total Hours Worked". These headers will serve as a basis for tracking attendance and calculating hours worked.

Formatting the Sheet

name list
name list

To make the sheet user-friendly, apply the following formatting adjustments:

  1. Freeze the top row for easy navigation by selecting the "Attendance" sheet, clicking on the row below the headers (row 2), and then navigating to "View" > "Freeze Panes" > "Freeze Top Row".
  2. Adjust column widths to display data without wrapping.
  3. Apply a filter to the "Employee Name" column for easy sorting and filtering. Right-click the column header, select "Sort & Filter" > "Filter".

Adding Data Validation

the printable attendance sheet is shown in black and white
the printable attendance sheet is shown in black and white

To maintain data integrity, apply data validation to the "Present Days" and "Absent Days" columns. This will ensure that only valid numeric inputs are entered, preventing errors like text or negative numbers.

To apply data validation, select the "Present Days" column, click on "Data" > "Data Validation" > "Settings". In the "Settings" tab, under "Allow", select "Whole Number", and under "Minimum" and "Maximum", enter "0" and "7" respectively. Repeat this process for the "Absent Days" column, but set the "Minimum" to "0" and "Maximum" to "7".

Implementing Formulas for Automated Calculations

Printable Minimalist Black and White Employee Attendance Sheet Template
Printable Minimalist Black and White Employee Attendance Sheet Template

Now that the basic structure is in place, it's time to incorporate formulas to automate attendance tracking and calculations.

Automatically Calculating Total Hours Worked

Employee Attendance Calendar 1728
Employee Attendance Calendar 1728
Advance ATTENDANCE SHEET in Excel \
Advance ATTENDANCE SHEET in Excel \
Fully Automatic Attendance sheet  With Salary sheet in excel by learning Center in Urdu/hindi
Fully Automatic Attendance sheet With Salary sheet in excel by learning Center in Urdu/hindi
Free Excel Leave Tracker Template (Updated for 2026)
Free Excel Leave Tracker Template (Updated for 2026)
Printable Attendance Sheet Format in Excel - Time Clock MTS
Printable Attendance Sheet Format in Excel - Time Clock MTS
🔥🔥 Fully Automated Attendance Sheet in Excel
🔥🔥 Fully Automated Attendance Sheet in Excel
Attendance Tracker in Excel
Attendance Tracker in Excel
White Attendance Sheet US Legal Template
White Attendance Sheet US Legal Template
Attendance Sheet Templates | 18+ Free Docs, Xlsx & PDF Formats, Samples, Examples, and Forms
Attendance Sheet Templates | 18+ Free Docs, Xlsx & PDF Formats, Samples, Examples, and Forms

To simplify the process, use the SUMIF function to automatically calculate the total hours worked based on the number of present days. Assume that each day worked comprises 8 hours. In cell D2 (or wherever you'd like to display the total), enter the following formula:

=8*SUMIF($B$2:$B$347, C2, 1)

This formula multiplies 8 hours by the sum of the present days for each employee, using the "Week Starting" and "Present Days" columns as references. Drag this formula down to copy it for the other employees in your list.

Automatically Counting Absent Days

To identify absent days for each employee, subtract the sum of "Present Days" and "Total Hours Worked" from the total days in the year (365, assuming a non-leap year). In cell E2, enter the following formula:

=365-(D2+C2)

Again, drag this formula down to apply it to the other employees in your list.

Conditional Formatting to Highlight Absences

To help visually track absences, apply conditional formatting to highlight absent days in red. Select the "Absent Days" column, navigate to "Home" > "Conditional Formatting" > "Highlight Cells Rules" > "Greater Than". In the dialog box, enter "0" in the "Value or formula" field and select a suitable red fill or background color. Click "OK" to apply the formatting.

With these steps, you've successfully created a yearly attendance sheet in Excel with automated calculations and conditional formatting. This handy tool will save you time and simplify the attendance tracking process.

Now that you've seen how to create this useful template, download the free Excel attendance sheet template and customize it to suit your specific requirements. Happy tracking!