Beginner's Guide: How to Create a Leave Tracker in Excel

Love it or hate it, Excel (and its more popular counterpart, Google Sheets) is a little black dress in the world of work organization and productivity tools.

It’s versatile enough to serve as a decent leave tracker for startups and SMBs with simple company structure and standard leave policies.

You’re probably already familiar with the way Excel works; even if not, this instruction for yearly leave tracking with Excel is a breeze to follow through. You’ll have to apply a few formulas, but it goes no further than a simple copy-paste action. 🪄

Step 1: Outline The Leave Tracker’s Structure

There’s nothing as annoying as thinking you’re finally done after hours of tinkering, only to realize you forgot to include an important piece of information!

Don’t open Excel just yet — the initial steps will happen at the drawing board.

First, decide and sketch out what kind of leave tracker you need.

In our standard, yearly leave tracker model, you will need:

• As many rows as there are employees, with the first two rows left blank for headers;
• A column for each information category — employee names, employee information, leave types, and dates.

Step 2: Gather All The Employee Data

Now it’s time to think about every piece of information that could impact the leave approval and tracking process.

Here’s a checklist of useful information to work into the leave tracker:

• Employee names (pro tip: link the employee names to their company profiles);
• Employee ID;
• Seniority;
• Contact information;
• Department/ shift;
• Department/ shift manager;
• Employment status: part-time or full-time;
• Leave laws.
• As for the leave fields, there are 15+ types of paid and unpaid leave they could take — but we’ll keep it simple:
• Accrued PTO — How many paid days off they have at their disposal;
• Unpaid time off — How much unpaid time off do they have in total.

Step 3: Create The Key For Abbreviations

Typing everything in full will make your Excel leave tracker too messy to function properly.

That’s why in this step you’ll create a simple, easy-to-understand key for everything that goes into your tracker. Color coding can help you distinguish whether the time taken off should be paid or unpaid.

Here’s an example of what it could look like:

It is important to create something extremely straightforward and logical for anyone who needs access to the leave tracker.

Step 4: Open Excel And Start Creating The Leave Tracker

It’s time to actually start creating the leave tracker!

Open Excel and create a new workbook to begin.

a) Type in the headers

Enter the employee names, information, and leave types:

b) Create and format the calendar

In the second row, in the second empty cell from above (in our case, G2), enter the date when you’d like your leave tracker to start, in the m/d/y format. We’ll start on January 1st, 2024.

Then, select the cell and drag this row all the way to the end of the year, to the NH column. 

The cells will automatically populate with all the dates in a year:

We’ll need to shrink those days for better visibility. 

Select all the cells with dates, right-click, and pick Format Cells — then, pick Custom from the menu on the left and pick d. This will let you only display the days.

Now, select the date cells again, pick Column Width, pick 3, then OK. 

The tracker should now look like this:

c) Add weekends and non-working holidays

This step is optional, but it will make your work far easier in the future, so let’s mark the non-working days with red.

Insert a new row above the dates by right-clicking on row 2 and picking the + Insert 1 row above.

Now, pick the cell G2 and enter the following formula:

=WEEKDAY(E3)

The next step is to pick the cells from G4 to NH7, and then: 

Conditional Formatting - New Rule - Use a formula to determine which cells to format,

and enter the following formula to mark the Saturdays:

=E$2:N$2=6

Click Format - Fill, and pick the color that marks the weekends, in our case red.

To add Sundays, select the cells from G4 to NH7 again and repeat with this formula:

=E$2:N$2=7

To avoid confusion, hide the row 2, and your tracker should look like this:

d) Tracking the days off

At this point, you can start tracking the days off.

Let’s say that Bryan took two sick days off in January; sick days count as PTO according to his company policy, so HR will note them with a PTO, as shown in our key example.

To make this tracker fully functional, we’ll add the COUNTIF formula, so the days are automatically counted and noted.

Select E5 (Bryan’s counter for paid leave), and enter the following formula:

=COUNTIF(G5:NH5,“PTO”)

This will automatically add every PTO day Bryan takes. To do the same for other employees, click on E5 and drag to cover their counters:

You can use the =COUNTIF formula with the same logic to create counters for other fields.

e) Mark the month

Right-click the cell above the January 1st (G1), and then pick Format - Number - Custom, and type mmm in. 

The month’s first three letters will appear (Jan); to apply this across the tracker, click G1 where it says Jan now, and drag it till the end.

That’s it!

Your Excel leave tracker is good to go.

Unless…

You’d love to implement days off in the company calendar and see all absences in one spot. 

Or, you’d like to track time for hourly employees and need more precision. 

Or, you’d love to benefit from hiring experts across the world, but are afraid of accidentally breaking their country’s leave laws and end up avoiding it altogether.

Excel leave trackers are solid, but outdated and lack so many functionalities. You can keep adding formulas and fields to try to keep up, but why would you? Pick your battles when you can and choose a leave tracking tool you don’t have to build from scratch.

There’s a far more elegant leave-tracking solution

Opting for automated employee leave tracking apps such as Vacation Tracker is a lot easier to use and provides much more than Excel can in terms of leave tracking:

• Employee self-service — Each employee has a user profile with labels that determine their leave policies. They request days off themselves, and their Approvers handle the requests with a single click. Assign user roles and let everyone do their part!

• Head-to-toe customization — Everything can be easily pre-configured: notifications, employee departments, locations, leave policies, and more.

• Seamless integration — With integrations for Slack, Microsoft Teams, Google Workspace, and email of your choice, it fits into your digital workspace seamlessly. Days off appear on the calendar and VacationTracker dashboard for real-time worker availability and easier work planning. For custom integration, use our REST API. 

• Advanced leave tracking — Hourly PTO tracking, automated accruals, blackout periods: all set on autopilot. There’s no possibility of making a mistake, selecting the wrong field, or messing up the functionality of the entire tracker. 

In case that Excel leave tracker doesn’t look too appealing anymore, try Vacation Tracker for free!

Anja Milovanovic

A journalist turned content writer – Anja uses her investigative skills to produce high-quality SaaS, Marketing, and HR content.