One of my teammates has the responsibility of creating a leave tracker template in Excel for the entire team. This tracker template is then used to track vacations/holidays and planned leaves of the team members.
Till now, she used a simple Gantt chart in Excel but wanted something better with more functionalities.
So, I created this Excel Leave Tracker Template to make leaves management easy and track monthly and annual leaves by her team members.
You can also use this as a vacation tracker template or student attendance tracker if you want.
Excel Leave Tracker Template
This Excel Leave Tracker template can be used to record and monitor employee leaves for a year (of a financial year where you can choose the starting month of the year).
You can track 10 different leave codes for an employee – vacation leaves, sick leaves, maternity/paternity leaves, casual leave tracking, leave in lieu of overtime, and half days, etc.
It also provides a monthly and yearly total of different types of leaves that can be helpful in project planning and leave management.
It uses a bit of conditional formatting, a few DATE functions, array formulas, and a simple VBA code.
Download the Excel Leave Tracker Template (tracking for 20 employees/people)
Looking for the Google Sheets version of this Leave Tracker Template? Click here!
How this Excel Leave Tracker Template Works?
- Use the triangle icons next to the month name to move to the next/previous month (the template updates itself to show the dates for the selected month). There is a short VBA code that runs in the background whenever you change the month. It shows you the selected month only and hides all the other months.
- This Excel template can be used to track leaves for over a year. You can select a start month and can track leaves for a year. For example, if you follow the April-March cycle, select April 2023 as the starting month.
- Note: The value in cell A1 is to change the time period of the leave tracker ONLY. DO NOT use Cell A1 to move to the next month while recording leaves. Use the triangle icons next to the month names to go to the next/previous month and mark leaves.
- You can specify the working days and non-working days (Weekends). At the right of the leave tracker, there is functionality to specify the working days by selecting Yes from the drop-down. If you select No, that day is marked as a non-working day in the leave tracker.
- As soon as you specify the non-working days, those weekdays get highlighted in gray color in the leave tracker.
- You can update the holiday list in the worksheet named “Holiday List”. It will automatically be reflected in the tracker by highlighting those days in Orange color.
- To enter the leave record for employees, use the relevant codes based on the leave type (you can customize these leave codes). For example, in the case of sick leave, use S, in the case of Vacation, use V, as so on.
- There are two codes reserved for half-day leaves. you can enter H1 or H2 for a half-day leave.
- As soon as you enter the leave code for any employee, it gets highlighted in red (in the case of half-day, it gets highlighted in yellow). If that day is a weekend or holiday, the color would not change.
- Column NJ (highlighted in green in the pic below) has the number of leaves of that employee in that month. It counts the leaves on working days only (those on weekends and/or holidays are not counted). Half-day leaves are counted as 0.5.
- UPDATED: Column NK (highlighted in light red in the pic below) has the number of annual leaves taken by an employee. It counts the leaves on working days only (those on weekends and/or holidays are not counted). Half-day leaves are counted as 0.5.
- Columns NL to NU gives the leave break-up by leave code (for the entire year). This could be helpful to keep a track of the type of leave that has been availed. Note that while Half Leaves are counted as .5 leaves in the total count, in the leave break-up, it is counted as whole numbers. For example, 2 half leaves would lead to 1 leave count, but you’ll see two half leaves in the leave breakup.
I have created this leave/attendance tracker template for 20 employees. If you want to add more, just copy-paste the formatting and formulas for additional rows.
Also, since there is a VBA code involved, make sure you always save it with .xls or .xlsm extension.
Download the Leave Tracker Template
Note: To update this template for any year, simply change the year value in cell A2. For example, to make it for 2017, just change the value in A2 to 2017. Also, you need to update the holiday list for the specified year.
The download file is completely unlocked so you can customize it to your needs.
Here is another version of the template that can track leaves for 50 employees.
Want to learn how to create awesome templates and dashboards? Check out the Excel Dashboard Course
FAQs on using this Leave Tracker Template
Since I created this vacation/leave tracker, I have been inundated with emails and comments. What you see now is a refined version that has been possible due to all the feedback that I have got.
Based on the questions I get repeatedly, I have created this FAQ section so that you can get an answer faster (instead of waiting for me to respond).
Here are the most common questions I get about the Leave tracker template:
Q: I tried downloading the file but it downloaded as a zip. How do I use it?
A: I have fixed this issue, and now you should be able to download the Excel file directly.
Q: When I change the month, the existing leaves are reflected in the changed month as well.
A: This happens when you use cell A1 to change the month. You need to use the arrow icons to change months. Cell A1 is to be used only to set the starting month of the calendar. For example, if you want the calendar to start from April, make cell A1 value 4. Now to move to March, use the triangle icons.
Q: I need to track multiple years of leaves. Do I need to create a new worksheet for each year (financial year)?
A: Yes! This leave tracker can only track leaves for a 12 month period. You need to create a copy for each year.
Q: Can I use my own leave codes?
A: Yes! You can change the leave code in cells NX8:NX17. You also need to specify the same code in cells NL5:NU5. For example, if you change the Work from Home leave code to X, you also need to change it in NR5.
I hope you find this leave/vacation tracker helpful.
Are there any other areas where you think an Excel template could be helpful? I am hungry for ideas and you are my gold mine. Do share your thoughts in the comments section 🙂
Related Excel Project Management Tutorials and Templates:
wow. its awesome leave tracker. how to change holiday for 2019? Plz guide me
Change it in the “Holidays” tab
Hello, How do I add more employees? I have 100 employees
I love your tracker. If there is option to mark 2nd & 4th Saturdays as Non Working day, it will be more use full. I know there is option to mark as leave days, but i need it show as non working day. Please look in to it and add this if possible.
Thank you.
why is showing orange color on some colom and can i chose my on color based on type of leave ??
How can I reduce the list from 20 employees to 5 employees
Hello, please advise how I could add two columns for back-up persons within the view for each month. How should I edit the formula? Many thanks for the help!
I love your tracker, but is there any way to track using hours instead of days? A few employees work 10 hour days instead of 8, and we are also allowed to take a few hours of vacation or sick time. Thank you.
Hi,
I’m trying to add the Team manger, however, got error with the date and is there is any way to get over view of each month team manger wise and month wise.
Hi, want to ask if there’s a way to summary into another sheet the balance of leave taken from leave tracker sheet? I have main menu to show all the balance used but got error when I link formula to the “Leaves this year”. Anybody can help? thx
Is there a way you can assign each employee a total number of leaves alloted through the year so the they can be deducted. For example, employee 1 gets 10 “leaves” a year and as she/he uses a vacation day/personal day then it gets deducted from the total number? thank you!
Hi I need 2019 Leave tracker template
Just change 2019 in cell A2
So you’re saying that dates will fall on the same days in 2019?
The leave tracker sums W worked from home, it is quite cool idea to track this but it is not counted as holiday so how can I amend the sumproduct formula?
I have the same question. I would like to track but do not want to count it as leave.
I’d love to have a way to specify the working days for each employee. In my organization, we don’t all have the same workweek. That said, what a fantastic resource this tracker is (especially for nonprofits like us) — thank you!
Wondering if it is possible to add another column next to Employee name. The additional column would be group/reporting manager.
Hi Sumit,
Excellent tracker, thank you so much. Would like to check if I can delete the leave codes as I do not need so many in my tracker.
Your reply will be highly appreciated.
Thank you!
If I want to add more leave name and code can I do it? I tired doing it it works however when I put the code in tracker it wont give me the red color.
Do you have a 2019 one yet? I tried changing the cells and it does not work. it will not add days of leave.
Thanks
2019 is working you just need to change the year A2 to 2019
Just wanted to ask for the 2019 template. I have tried changing the year but it did not work correctly.
is there a way to log days worked in Lieu (eg. code L day worked in Lieu reduces the total days taken)? great spreadsheet by the way!
Can you create a 2019 spreadsheet. Would like to be ready for the new year.
explains above how to change to 2019 just edit the date then add the holidays
Hello I did change the date and edit the holidays but is does not change the leaves per year column. How do I change that?
you should only change 2018 to 2019 then it auto populates with the dates, the leaves per year auto populates when you add holidays, maybe you have lost some formatting, download and try with a fresh sheet
When I add in leave the cell does not fill with colour as you said it would. Why would this be?
Hello. I would like to add one more half day as H3. how do I do that
Hi did you get any reply on this as I want to do something same.
No I havent
Add another row, then you need to amend the conditional formatting.
Can you please update for 2019? I like this spreadsheet but cannot seem to edit correctly for 2019.
This is awesome!! Nice skills! I’ve tweaked the layout a little and added a team column next to the name column. I’ve adjusted to macros to show the first 2 columns (A&B) when the month skip arrow is pressed. The only thing I’m having an issue with is now January 1st is now the last entry on the calendar (NJ5 is Jan 1 every year, should now be in C5). This is clearly because I’ve added a column and its making reference to the wrong cell to start on, but not sure which of you formulas to adjust. Its not even an issue as all the dates are there but, my OCD in overdrive!! Please help…
hey do you know how to amend the formula so it excludes when summing the total holidays for the month and for the year please?
thanks
Edit in conditional formating.
Pretty please for 2019!! Thank you!
Can you fix your formula for “Week #”? You have it as a hidden row. It’s row 7. It won’t work since it references Sheet3 when of course there is no Sheet3.
Sheet 3 is hidden, you can show it.
Is there a way to log days where they may have only had to take off a couple hours?
This is fantastic and has helped me so much with my employee’s leave. Can you please do one for 2019? pretty pretty please!
I like the leave report but I need more information – Each one of my associates accumulates time/hours on their anniversary. I want the tracker to track time used as well as time remaining for the year. How can I do that without messing up the sheet?
How to add columns without disturbing the formulas. need extra colum for department and id #.
What if there is no set day off?
In “working days table” select “yes” means all day is working.
When an employee leaves and want to delete the row in the middle of the leave tracker spreadsheet. The leave tracker does not work when leave code is entered for other employees how do you delete the row??
Hi
If I want to see two months data together then what changes I should do in the file. like I want to see October’18 and November’18 at once on sheet.
Please let me know what changes should I do in macro and date formula.
It will be huge help.
Regards
Sachin Sharma
Good day, i hope that there is some help that i need with the following problem, i have a sheet, kolom A has the name APG with all kind of codes.
and on row 1 starting from kolom B there are 30 cels with code AA AB AE enz. the problem is that i need an formula to get the result of the APG and the code AB or something like it to give as a result for example 50%, is this posible with a formula.
Hope that you are able to help me with this problem.
Best regards Teet.
Hi hope you can help me, there’s an error on my leave registre it says ‘ unable to set the Hidden property of the range class” . What is this means?
There’a alway an error “UNABLE TO SET THE HIDDEN PROPERTY OF THE RANGE CLASS”
Is there a way to delete some of the leave codes completely from the form? For example, I only need 4 codes, when I delete the extras I disturb majority of the form.
So help full, but I cant add more column after the name, for gender or sth els. Please help!
Dear all,
first of all, thank you very much. the excel to count annual leave is very helpful.
is it possible to insert an extra cell or column for starting date of each employee my leave, let say, 12 days per year, and “as of today”, how many days he or she is entitled? Then, there’ll be the balance of each staff.
Also, just in case, use the financial year Apr 2018 to Mar 2019 instead of Jan 2018 to Dec 2018, will it be difficult to modify the excel?
Hi, do you have a 2019 version?
HI HALLO, IS IT POSSIBLE TO INSERT AN EXTRA COLUMN FOR STARTING DATE OF EMPLOYER MY LEAVE GOES FROM STARTING DATE TO ONE YEAR AFTER THAT.
Hi Sumit, this tracker is very useful! I would like to know if there is anyway we can record in one day, an employee took 1/2 day annual leave and another 1/2 day unpaid leave?
Thank you LOVE this!! Is there any way to get this to track hours down to 15 min. 1/4 of an hour? We do time in hours and down to 15 min. of Personal Time. Thank you again for this it is AWESOME!
nice
Can I use this with Mac??
I love this tracker! Thank you so much for making my life easier during 2017 and 2018 🙂 Is there a chance for 2019 one?
Great Job!
Can you please help me how to add additional columns to set Total annual leave for each employee and to display total leave taken and remaining for each employees.
How do I add a column for employee ID left to name
This is a great tracker. How can you assign your own colors for each type of leave?
Hey Lisa, You can do that using conditional formatting. Select the entire leave area section, go to Condition Formatting (in Home tab) and click on manage rules. There you can create your own rule
That worked…thank you!
So, I have to do this for each month? Is there a way to apply this once for all months/years?
Hello Sumit, I love this tracker but I need to use it to track points that my companies drivers get for certain driving violations. That being said, I was wondering if you could let me know how to change the values for the different codes? For example I would be changing Sick to Preventable accident and would need to change the 1 to a 2. Any help would be greatly appreciated!
How to change Saturday and Sunday holidays to Friday