Today we show you an attendance tracker excel template. The attendance tracker excel purpose is to entering and recording the absence or attendance for your employee. The employee attendance sheet in excel for office and perfect for small company in any industry.
THE MENUS
There are 3 sheets in attendance tracker excel:
Calendar View
Employee monthly attendance sheet template excel displays the calendar for selected year. There is a field to select employee. Then, it marks the dates when the selected employee takes the leaves by coloring the cells.
On Calendar, Saturday and Sunday is automatically greyed as they are weekend. After you set the Holiday dates in Settings menu, the Dates will turn grey too.
Below the calendar, there is statistic which work as symbol legends too. The statistic will calculate employee attendance sheet in excel.
Employee Leave Tracker
Menu for recording the leave details. The details including Employee Name (or ID), Leave Date, Reason, and leave days length.
Settings
Menu to setting the options for drop down in other menus and Holiday dates. The drop down include the employee list and type of list (or reason).
HOW TO USE
First open the Calendar View menu. Select the year in Year cell from drop down button. Currently, the provided years in employee attendance tracker excel 2017 are from 2010 to 2020.
Next, go to Setting menu. Enter the employee name in Employee List and Type of Leave, as well Company Holidays. You need to manually enter the holidays for each year. Make sure that the new entered values are all inside the tables.
Then, go to Employee Leave Tracker menu. Enter the Leave records by selecting Employee from drop down list. Enter the Start and Leave Date. Select the leave reason in Type of Leave. The Days column will automatically calculate the Leave Days. The Days column will not count the holiday or weekend dates.
NOTE: if the Days column do not correctly calculate the days, try to enter the Start and Leave Date in DD MMM YYYY format (e.g. 2 Dec 2025). This problem may be encountered after you enter the years aside 2010 to 2020. Read Modify to know how to add year.
After that, go back to Calendar View menu. Select an employee on Employee cells. Their leave date will automatically colorize.
- Blue color means sickness
- Orange means vacations
- Green means bereavement
- Yellow means other.
If you forgotten, do not worry because the Statistic below the calendar will tell.
The Key Statistic will display the total leaves, total working days, number of their leave based on leave types, and the comparison with previous years.
MODIFY
Do not worry, you can add years by manually typing the year (e.g. 2025). Other way is by adding the value into the drop down button. To do this, first select the Year cell. On Data tab, click Data Validation. A warning will appear, click Yes. On Data Validation dialog box > Setting tab > Source, enter the newest year values with comma to separate. Click OK.
If you add new leave types, it may not displayed properly in Calendar and Statistic. For Statistic, you may manually create the new field for total leave types. You can copy the formula below. Change the red text part with the cells ID in Setting menu which contain the new type:
=SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],”>=”&DATE($AN$5,1,1),tblLeave[End Date],”<“&DATE($AN$5+1,1,1),tblLeave[Type of Leave],Settings!$D$8)
For Calendar, you can manually fill the shading on background.
SUGGESTION: due to the complexity, it’s better to stick to provided leave type values. If you want to be more details, better create new column for description in Employee Leave Tracker sheet.
CONCLUSION
Free employee attendance tracker is open source, but may be too complex to edit in Calendar menu. You may need programming skills to do so. However, the other feature is easy to modify.
Attendance tracker excel is perfect for regular office with 5 workdays. attendance tracker excel can help you in looking at employee’s KPI.
To download attendance tracker excel, please click on of this button: