Looking for a leave tracker Google Sheet template that can track the leave of your employees or students?
You’re in the right place.
In this post, I am sharing one of my best templates – the employee vacation tracker template in Google Sheets (you can also use this as an employee paid time off tracking spreadsheet).
This template is perfect for keeping track of, paid time off, holidays, and sick leave. You won’t have to stress about making sure you account for everyone’s leave anymore!
Click here to access the Google Sheets leave tracker template [You will have to make a copy of it to use it].
I first created this leave/vacation tracker in Excel.
Since then I have got hundreds of requests for a Google Sheets version of this leave. So I created this one in Google Sheets.
It’s a great leave tracker template and is loaded with functionalities.
So let me also take through the tracker and how to use it efficiently.
How to Use the Leave Tracker Google Sheet Template
The basics of how to use the template are:
- Specify the date and year you want to track leave for in cells A1 and A2
- Change Employee Xin the Name column to your employees or students names
- Use the codes to mark the dates with the type of leave they’re taking
Here is a snapshot of what the leave tracker looks like:
In this leave tracker:
- You can specify the year and the month. As soon as you enter the month number in cell A3 (1 for January, 2 for February), the tracker updates and displays only the columns for that month. The rest of the columns get hidden.
- You can enter any leave code you want and the leave would be counted. There are some inbuilt codes that will get highlighted in red color. Also, you can use the leave codes H1 and H2 for half-day to mark a leave that is counted as 0.5. In case you want to use your own custom leave codes, replace the ones in this list with the ones you want. Note that the last two are programmed to be half-day leaves only.
- The tracker gives you a month-wise total as well as the yearly total of all the leaves. It also gives you the leave breakup for the entire year (by leave types). So, it’s a PTO tracker Google Sheets can use for all types of leave calculations.
- In case you have a weekend other than Saturday and Sunday, you can specify the working days and non-working days by making the Yes/No selection for the days. If a day is non-working, it is highlighted as Gray in the tracker.
- You can specify the holidays in the Google Sheets Holiday tracker tab of the worksheet. These holidays are also highlighted in orange in the tracker and the leaves on these days are not counted.
IMPORTANT: In the template, the leave tracker is made to account for leaves for the month Jan-Dec. But if you want to change it to records leaves for Apr-Mar or any other year period, you can do that by changing the value in cell A1 (you will have to unhide the row). For example, if you want the year duration to be from April-2021 to March-2022, enter 4 in cell A1. 4 here would mean that the leave tracker would now start the year from April.
Note that this tracker is heavy on formulas and also uses a backend Google Sheets script. Hence it may take a few seconds to update when you change the value.
As of now, I have created this tracker for 50 employees/students. You can easily add more rows to it. Just copy and paste the leave tracker rows, and it will work fine.
FAQs on the Leave Tracker Template
I have been getting a lot of queries about this leave tracker template, so I decided to answer a few queries here first. In case you don’t find your query answered here, leave a comment and I will try to get back as soon as I can:
Q: Can I use my own custom leave codes in this tracker?
A: Yes! You can use your own custom codes by changing the existing codes 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. Also, note that the last two leave codes would only count towards half-day leaves.
Q: Can I track multiple years of leaves in this same tracker?
A: No! You can not track multiple years in the same tracker. One single sheet will act as a simple annual leave spreadsheet tracker and can not track multiple years. You can, however, create a copy of the worksheet for another year of tracking.
Q: Why is this template so slow when I make any changes?
A: Ideally, it should be fast when you are using it to simply add leaves in it. However, while changing the month, it’s likely to take a few seconds. This is because this tracker is loaded with formulas and a backend script. So let me take a few moments to update.
Q: When I change the month, the exiting leaves are reflected in the changed month as well.
A: This happens when you use cell A1 to change the month. Don’t use A1, instead, use cell A3. A1 cells need to be used in a very specific case when you want to change the starting month from Jan to something else (such as changing it to April to rack leaves from Apr-Mar).
Q: How do I put a leave a tracker in Google Sheets?
A: You can always use a standard calendar template and use your own codes to mark employee leave tyes. Otherwise, you will have to use a lot of formulas. We recommend just sticking to using a free template such as the one in this guide instead.
What goes into making this Google Sheets Leave Tracker
- A lot of formulas (such as SUMPRODUCT, VLOOKUP, INDEX, and MATCH).
- Conditional formatting to highlight leaves and holidays.
- A few lines of code to unhide and hide columns.
- Drop-down Lists to select working/non-working days.
[You will have to make a copy of it to use it – navigate to File>Make a copy].
Whether you’re looking for a sick leave monitoring spreadsheet, PTO tracking template, or leave tracker, we hope you find this leave tracker Google Sheet Template useful. You can also use this as a holiday planner or Google Sheets vacation tracker of your employees.
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.