Leave Tracker Template in Google Sheets (Updated for 2018)

Looking for a leave tracker template in Google Sheets that can track leaves of your employees or students?

You're at the right place.

In this post, I am sharing one of my best templates – the leave tracker template in Google Sheets.

Click here to access the leave tracker template [You will have to make a copy of it to use it].

To create a copy, click on the blue Make a Copy button.

Leave Tracker in Google Sheets - Make a copy
I first created this leave tracker in Excel. Since then I got hundreds of requests for a Google Sheets version of it. So I created this one.

It's a great tracker and is loaded with functionalities.

So let me also take through the tracker and how to use it efficiently.

Google Sheets Leave Tracker Template

Here is a snapshot of how the leave tracker looks like:

Google Sheets Leave Tracker Template - Demo

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. Rest of 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.Leave Tracker in Google Sheets - Codes
  • The tracker gives you 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). 
  • In case you have 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.Google Sheets Leave Tracker Template - Change Working Days
  • You can specify the holidays in the Holidays 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-2018 to March-2019, 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. 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).

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 hide and unhide columns.
  • Drop-down Lists to select working/non-working days.

Click here to access the leave tracker template [You will have to make a copy of it to use it].

Hope you find this useful.

You May Also Like the following Tutorials/Templates:

  • I removed several holidays from the holiday list and they are still showing up highlighted in orange on the spreadsheet even though they do not appear when I select the holidays tab. How can I get rid of the orange highlight?

  • Really useful sheet thanks. I’d like to add the first week of january 2019 into my figures ( so 12 months +1 week), could you advise on the easiest way to achieve this please?

  • Hi,
    Can i have 2 or more different public holiday list in the Holidays tab of the worksheet??
    If can, how can i add 1 more public holiday list and these holidays will highlight in orange in the tracker ???

  • This is such a great spreadsheet. Thank you so much for making it available. Is there any way you can think to use it to track Time Off in Lieu in half day increments? I thought about using the existing half day cells, but then they would add to the number of days of leave taken, where as I would want a separate tally. Again, thanks very much.

  • When I continue in the “Holidays” table with a new order of 13 and beyond, the problem arises because this leave automatically counts in the total number of days?

  • Hi Sumit,

    I want to update the font of the leave in different color. Can you please let me know if it can be change.

    Thank you.

  • Hello – is it possible to include the bank/public holidays count within the total leave number of days? Thanks in advance for your help.

  • Everytime I mark H as Child Care, it counts as 0.5 wherein I want to consider it as 1 as well. Please advise.

    • I found one limitation to the program as well. If we have future requests for the Vacation, there should be a separate column for it, e.g. Vacation Requested (using the code “R”. And there should be a total of it in that column). So whenever I plug in R in any of the cells, it should add in that column.
      p.s. RH for Half day request.

      • Hey Aaresh.. you can do this by changing the leave codes. So you can change 2 of the existing leave codes, and also change the column headers in NL6:NS6 accordingly.

  • >