Graphed LogoGraphed
Login

How to Create a Leave Tracker in Google Sheets

Cody Schneider•

Manually tracking employee time-off requests with emails and spreadsheets is a quick path to confusion and mistakes. A dedicated leave tracker simplifies the process, providing a single source of truth for who's out, when, and how much vacation time everyone has left. This tutorial shows you exactly how to build a powerful, automated leave tracker from scratch using Google Sheets.

Setting Up Your Leave Tracker Foundation

First, let’s build the basic framework. A well-organized tracker separates data entry, records, and summaries into different tabs. This makes it easier to manage and update your tracker as your team grows.

Start by creating a new Google Sheet. Rename the default sheet and create a few new ones. You should have four tabs at the bottom:

  • Dashboard: This will be your high-level summary, showing leave balances for each employee at a glance.

  • Leave Requests: This tab will serve as a running log of every time-off request submitted.

  • Employee Data: A simple table to store essential employee information, including their annual leave entitlement.

  • Settings: A place to list holiday dates and different leave types to keep your formulas organized.

Organizing your sheet this way makes it much cleaner and easier to navigate than cramming everything into a single tab.

Step 1: Populate Your Settings and Employee Data Tabs

Before building the main tracker, let's fill in the foundational info that our formulas will rely on.

In the 'Settings' tab:Create two simple lists. In column A, list your company's public holidays for the year. This is crucial for accurately calculating workdays. In column B, list the different types of leave you offer, such as "Annual Leave," "Sick Leave," "Unpaid Leave," and "Personal Day."

In the 'Employee Data' tab:Create a simple table with headers for 'Employee Name', 'Job Title', 'Start Date', and the annual entitlement for each leave type (e.g., 'Annual Leave Entitlement', 'Sick Leave Entitlement'). Fill this out for every member of your team. Centralizing this information prevents you from having to hard-code allowances into complex formulas later.

Step 2: Create the Leave Request Log

Navigate to your 'Leave Requests' tab. This is where every time-off request will be recorded. Set up the following column headers starting in cell A1:

  • Timestamp (Comes automatically if you use a Google Form)

  • Employee Name

  • Leave Type

  • Start Date

  • End Date

  • Number of Days

  • Status

  • Notes

Use Data Validation for Cleaner Dropdowns

To avoid errors from typos or inconsistent entries, convert the 'Employee Name', 'Leave Type', and 'Status' columns into dropdown menus. This is one of the most powerful features for keeping your data clean.

Here’s how to do it:

  1. Select the entire column you want to convert (e.g., column B for 'Employee Name').

  2. Go to Data > Data validation.

  3. Click + Add rule.

  4. Under Criteria, choose Dropdown (from a range).

  5. Click the small grid icon to select the data range.

  6. For the 'Employee Name' column, navigate to the 'Employee Data' tab and select the range containing all employee names (e.g., 'Employee Data'!A2:A10).

  7. Click Done.

Repeat this process for the 'Leave Type' column, but this time, select the list of leave types from your 'Settings' tab (e.g., Settings!B2:B5).

For the 'Status' column, you can create a dropdown from a list of items instead of a range. Follow the same steps, but under Criteria, choose Dropdown and enter "Pending", "Approved", and "Rejected" as your options.

Step 3: Add Formulas to Automate Calculations

Now it’s time to bring your tracker to life with formulas. We'll start by automating the calculation for the number of leave days taken.

Calculating Working Days with NETWORKDAYS

You can't just subtract the start date from the end date, that would include weekends. Instead, we'll use Google Sheets' NETWORKDAYS function, which cleverly calculates only the working days between two dates and can even exclude your list of public holidays.

Click on cell F2 (the first cell under 'Number of Days') in your 'Leave Requests' tab and enter the following formula:

Let's break that down:

  • ARRAYFORMULA(...) applies this formula to the entire column, so you don’t have to drag it down manually for new entries.

  • IF(E2:E="", "", ...) is a simple check. It says if the 'End Date' column is empty, do nothing. Otherwise, perform the calculation. This keeps your sheet looking clean.

  • NETWORKDAYS(D2:D, E2:E, Settings!A2:A) is the core of the operation. It calculates the working days between the 'Start Date' (column D) and 'End Date' (column E), while excluding any dates found in your holidays list (over in 'Settings!A2:A'). Locking the holiday range with $ signs ('Settings!$A$2:$A$15') is good practice, though not strictly needed with ArrayFormula.

Once you enter some test data, the 'Number of Days' column will automatically populate.

Step 4: Build Your Summary Dashboard

The 'Dashboard' tab provides a real-time overview of leave balances for everyone. This is where you’ll answer the question, “How much vacation time do I have left?”

Set up your dashboard with these headers:

  • Employee Name

  • Annual Leave Entitlement

  • Annual Leave Taken

  • Annual Leave Remaining

You can create similar sections for Sick Leave or any other leave type you track.

First, populate the 'Employee Name' column by just referencing your 'Employee Data' tab. In cell A2 of your Dashboard, type =, click your 'Employee Data' tab, select cell A2, and press Enter. Drag this formula down.

Now, let's add the formulas:

1. Leave Entitlement (VLOOKUP)

This pulls the total leave each person is entitled to from your 'Employee Data' table.

In cell B2 (under 'Annual Leave Entitlement'), enter:

  • This formula looks up the employee's name (from cell A2) in the 'Employee Data' table ('Employee Data'!A:D).

  • It then returns the value from the 4th column of that table (your 'Annual Leave Entitlement' column).

  • FALSE ensures an exact match. Drag this down for all employees.

2. Leave Taken (SUMIFS)

This is the most powerful formula in our tracker. It adds up all the approved leave days for a specific employee and leave type.

In cell C2 (under 'Annual Leave Taken'), enter:

Here’s the logic:

  • It tells Google Sheets to sum the values in the 'Number of Days' column ('Leave Requests'!F:F)...

  • ...only if the 'Employee Name' column ('Leave Requests'!B:B) matches the name in our current row (A2)...

  • ...and only if the 'Leave Type' ('Leave Requests'!C:C) is exactly "Annual Leave"...

  • ...and only if the 'Status' ('Leave Requests'!G:G) is exactly "Approved".

This prevents rejected or pending requests from being counted against an employee's balance.

3. Leave Remaining (Simple Subtraction)

This is the easy part! In cell D2 (under 'Annual Leave Remaining'), simply subtract the days taken from their total entitlement:

Drag both the SUMIFS and the subtraction formulas down, and your dashboard is complete! It will now update in real-time as you approve new leave requests.

Step 5: Polish Your Tracker with Visuals and Forms

Your tracker is fully functional, but a few quick enhancements can make it much more user-friendly.

Add Conditional Formatting

Use color to make the status of requests instantly recognizable. Highlight the 'Status' column in your 'Leave Requests' tab, then go to Format > Conditional formatting.

Create a rule for each status:

  • If "Text is exactly" Approved, choose a green background.

  • If "Text is exactly" Pending, choose a yellow background.

  • If "Text is exactly" Rejected, choose a red background.

You can also create a rule on the 'Leave Remaining' column in your 'Dashboard' to turn the cell red when an employee's balance drops below a certain number, like 5 days.

Streamline Requests with a Google Form

To really professionalize your system, create a Google Form that feeds directly into your 'Leave Requests' sheet. Go to your sheet and select Tools > Create a new form.

Create form questions that match your sheet's columns: "Employee Name" (as a dropdown), "Leave Type" (as a dropdown), "Start Date," "End Date," and "Notes." When an employee submits the form, a new row is automatically added to the 'Leave Requests' tab, with the timestamp included. All you have to do is check the sheet and update the "Status" column from "Pending" to "Approved" or "Rejected."

Final Thoughts

Setting up a custom leave tracker in Google Sheets puts you in complete control of your team's time-off management. By separating raw data from your summary dashboard and using formulas like NETWORKDAYS and SUMIFS, you can create a reliable, real-time system without paying for expensive HR software.

While DIY sheets are great for specific tasks, tracking team performance often requires pulling data from many different SaaS tools. We found this manual report-building drained hours every week, which is why we created Graphed . It connects to all your platforms - from Google Analytics to Salesforce to performance marketing channels - and lets you build dashboards and get insights just by asking questions in plain English. This eliminates the need for wrestling with formulas or exporting CSVs, turning hours of work into a 30-second task.