Leave Tracker Template in Google Sheets

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, go to the File option and click on  Make a Copy.

Note: 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 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 code H for half day to mark a leave that is counted as 0.5.Google Sheets Leave Tracker Template - Leave Codes
  • The tracker gives you month-wise total as well as the yearly total of all the leaves. It also gives you the yearly breakup of the leave types. This would be useful if you use the inbuilt codes I have provided.
  • In case you have weekend other than Sat and Sun, 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.
  • In the template, the leave tracker is for Jan-Dec. But if you want to change it to 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 Arpril-2017 to March-2018, 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.

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.

Let me know your thoughts in the comments section.

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X