Leave Tracker Template in Google Sheets (Updated for 2019)

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 Google Sheets 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 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.

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-2019 to March-2020, 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 Google Sheets 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:


10 Google Sheets Tips that will save you hours every week

Bar Sparkline in Google Sheets - rtl
Google Sheets

Sparkline in Google Sheets – The Only Guide You Need

Sparklines are miniature charts that you can create within a cell. These are quite useful when you’re creating a dashboard and want to quickly show a trend, seasonal increase or decrease, or outliers (maximum/minimum) visually. According

Google Sheets

How to Insert Timestamp in Google Sheets

Google Sheets is a great tool when you’re collaborating and working with people on the same sheet. It allows people to work in the sheet simultaneously. In this tutorial, you’ll learn various to insert timestamp in Google

Google Sheets Keyboard Shortcuts - Spreadsheets
Google Sheets

101 Google Sheets Keyboard Shortcuts

Google Sheets is a relatively new spreadsheet tool in the market. It has quickly gained a lot of fan following and I see a lot of people migrating from other spreadsheets applications to Google Sheets.

  • Sumit, would it be possible for you to upload this to Google Sheets again? The file has been deleted. Thank you for all the time you have devoted to making these resources available; in particular, I have learned a lot from your Excel series.

  • Hello, the link to download is broken. When I click it says the file has been deleted. Can you please reupload?

  • Hello Sumit. Many thanks for sharing the excellent leave tracker tool. I am having a problem displaying the correct month in cell A4 in the case where my year runs from July to June of the following year. January to June don’t display correctly instead the cell just shows #N/A. Please assist.

  • Our employees are non-exempt and use there leave in hours , not days. Is there a way to change the formula to calculate in hours used, not days.

  • Hi,

    Thanks again for this brilliant tool. Will you be doing a 2019 version, or have you had enough? Thanks either way

  • 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.

  • >