Employee Vacation Schedule Template in Excel: Setup Guide

How-to basics · Updated November 22, 2026

An employee vacation schedule template in Excel is a shared calendar that shows who is requesting or taking time off and when. In Microsoft Excel for Microsoft 365 on Windows, create a date row, list employees down the first column, enter a consistent leave code in each person’s date cell, and add simple totals and formatting to make conflicts easy to spot.

What the template should track

A useful schedule is more than a colored calendar. It should make it possible to answer practical questions quickly: who is away, on what dates, whether a request is pending or approved, and how many people from a team overlap. Decide what the workbook is meant to show before building it. A team calendar for coordinating coverage does not necessarily need the same details as an individual leave-balance record.

For a straightforward annual team view, use one worksheet with these columns and rows:

  • Cell A4: a label such as Employee; cells B4 through NC4: calendar dates for the year.
  • Cells A5 downward: employee names, one person per row.
  • The date-and-employee grid below each date: a short code such as PTO, SICK, or HOL for the relevant absence.
  • A separate summary area or worksheet: optional totals, request status, notes, and coverage information.

A 366-day date area accommodates leap years; in a non-leap year, the final date cell can be left unused or hidden. For a small team, a single year-wide grid is convenient to scan. For a large team, or where each request needs its own approval record, a request log may be easier to maintain than a very wide calendar.

Build the date grid

Start a blank workbook and enter the year’s first date in B4, such as January 1, 2026. Format the date row as a readable date rather than a serial number: select the cells, then use Home > Number Format and choose a date format. A compact custom display such as d-mmm can help keep a wide calendar legible, while the underlying cell values remain real Excel dates.

With B4 holding the first date and C4 the next date, the formula below assumes those cells are in the date row. Enter it in C4, then fill it right through NC4. Each result advances one day. Confirm that the last displayed date matches the intended year; do not rely on the unused final cell being blank when checking a leap year.

=B4+1

To fill across, select C4 through NC4 and use Home > Fill > Right, or drag the fill handle from C4 to the desired last date. On Windows, Ctrl+R fills right in a selected range; on Mac, use Control+R in Excel. If you prefer not to have a 366-column calendar, make separate worksheets for months or use a request-log layout instead.

Enter employee names in A5 and below, then keep the row order stable. Freeze the identifying columns and date headings so they remain visible while scrolling: select B5, then choose View > Freeze Panes > Freeze Panes. Selecting B5 freezes the rows above it and the column to its left. On Excel for Mac, the Freeze Panes command is also on the View tab, though the ribbon layout can vary slightly by window size. To make names and dates easier to read, widen column A and adjust the date columns together.

Choose clear leave codes and request rules

Use a small, documented set of codes. For example, PTO might mean approved paid time off, PEND a request awaiting a decision, SICK an absence recorded under your workplace’s process, and HOL a public holiday that applies to the team. The workbook should not imply that a request is approved merely because it has been entered. Distinguish requested, approved, and declined time with a code or a separate status field.

Put a short legend at the top of the worksheet or on a Read Me tab. Define whether a code means a full day or whether partial-day absences are recorded another way. If half-days matter, codes such as PTO-AM and PTO-PM can be clearer than a vague note. Agree on how to record workdays, weekends, public holidays, and changes to a request, and nominate someone to keep the schedule current.

Keep sensitive details out of a broadly shared calendar. A team generally needs to know availability, not a diagnosis or private explanation for an absence. Follow the organization’s own privacy, record-retention, leave, and approval procedures; an Excel calendar is a coordination aid, not a substitute for an official HR or payroll record.

Add useful totals

A simple total can count how many cells in an employee’s row contain a particular code. Suppose names are in A5:A30, dates are in B4:NC4, and leave codes are entered in B5:NC30. Put a label such as PTO days in ND4. In ND5, use the formula below and copy it down for the other employees. For the example, if row 5 contains PTO on January 5 and January 6 and no other PTO codes, the result is 2.

=COUNTIF(B5:NC5,"PTO")

This counts marked calendar cells; it does not calculate a legal entitlement, remaining balance, or payroll quantity. It also counts a half-day code only if the formula is written to include that code. If the schedule uses PTO-AM and PTO-PM, either count each code separately or maintain a carefully defined day-equivalent calculation in a different summary. Avoid presenting a count as a balance unless the workbook also has reliable, authorized entitlement and usage data.

For a request log, use one row per request with fields such as employee, start date, end date, leave type, status, and approver. That structure is often better for tracking the decision history, while a calendar grid is better for seeing overlap. You can maintain both by treating the log as the record of requests and the calendar as the at-a-glance coverage view, but assign responsibility for updating them so they do not drift apart.

Make overlaps and weekends visible

Color can make a large grid much easier to scan, but use text codes as well so the meaning is not communicated by color alone. To highlight approved PTO, select the calendar-entry range B5:NC30 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the top-left cell of the selected range at B5, enter the formula below, choose a fill color, and confirm. The relative reference adjusts for each cell in the selected range.

=B5="PTO"

Repeat with a different rule for PEND or another code if useful. If you apply multiple rules to the same cells, review their order and formatting so that one rule does not obscure another. A code should remain readable against its fill color.

To shade weekend dates across the date row and employee grid, select B4:NC30 and add another formula-based conditional formatting rule. This formula assumes actual Excel dates in row 4 and the selection’s top-left cell is B4. With the second argument set to 2, WEEKDAY treats Monday as 1 and Sunday as 7, so values above 5 are Saturday or Sunday.

=WEEKDAY(B$4,2)>5

Consider highlighting a date column when several people are approved off at once, but set a threshold that reflects actual staffing needs rather than assuming every overlap is a problem. A department with ten people may manage three absences comfortably; a small team may need a coverage check for even one. Excel’s conditional formatting can flag a pattern, but a manager still needs to interpret coverage requirements and exceptions.

Choose between a calendar and a request log

The annual grid and the request log solve related but different problems. A grid gives a visual answer to “Who is away this week?” A log gives a structured answer to “Which requests are pending, who approved them, and what dates were recorded?” Choose the simplest structure that meets the team’s real workflow.

  • Use a year-wide grid when a small or medium team mainly needs a shared availability overview.
  • Use monthly tabs when a full-year grid is too wide to use comfortably or people print the schedule regularly.
  • Use a request log when requests have several statuses, approvers, partial days, or a history that must be retained.
  • Use an HR or scheduling system when access controls, balance calculations, audit history, integrations, or formal approval workflows exceed what a shared workbook should manage.

An Excel table can help organize a log: select its headers and data, then choose Home > Format as Table and confirm whether the table has headers. Tables make it easier to filter records by employee or status. A calendar matrix is less suited to filtering because dates are columns rather than request records. Excel for the web supports many common workbook and table features, but some advanced formatting or workflow features can differ from the desktop app; verify the specific tools your organization relies on.

Common setup problems and safeguards

One frequent mistake is typing dates as text. If dates do not sort or advance as expected, select a date cell and check that Excel recognizes it as a date value; changing a text cell’s display format alone may not convert its contents. Another issue is inconsistent codes, such as PTO, P.T.O., and Paid Leave being used for the same thing. Counts and conditional formatting rules match the text you specify, so inconsistent entries can make summaries misleading.

Other practical checks before sharing the workbook include:

  • Confirm the displayed year and the first and last date, including February 29 in a leap year.
  • Check that employee names are unique or use an employee identifier if names could be confused.
  • Protect formulas from accidental edits where appropriate, while remembering that worksheet protection is not a substitute for secure access control.
  • Store the file in an approved shared location and agree who can edit it; simultaneous changes and copies saved in different places can create conflicting versions.
  • Test a few sample entries and totals, then remove sample data before the schedule is used for real decisions.

Do not treat a spreadsheet password or hidden worksheet as strong privacy protection. Limit access through the organization’s approved storage and sharing controls, and share only the information coworkers need. Keep a backup or version history where available, especially before making structural changes or clearing a year’s entries.

Printing and maintaining the schedule

A full-year sheet with daily columns is usually too wide for ordinary portrait printing. If a paper copy is useful, print a month or a selected date range rather than forcing the entire year onto one page, which can make names and codes unreadably small. Use Page Layout to set orientation, margins, and print area, then check File > Print before printing. Page setup options vary somewhat between Excel desktop versions and printers, so use the preview to confirm the result.

At the end of the year, retain or delete the workbook according to the organization’s retention policy, not by guesswork. For a new year, copy a clean template rather than carrying old approvals into new dates. Update the year, confirm weekday alignment and holidays, clear prior entries, and test the formulas and formatting again. If the workbook is shared through OneDrive or SharePoint, make sure users open the current shared file rather than a locally saved older copy.

Frequently Asked Questions

Where can I get an employee vacation schedule template for Excel?
Excel may offer templates through its start screen or File > New, but available templates can vary by account and version. You can also build the simple date-grid or request-log layout described above in a blank workbook. Before using a downloaded template, check its formulas, code definitions, and privacy settings.

How do I track vacation days for multiple employees in Excel?
Place each employee on a separate row and each date in its own column, then enter a consistent code in the cells for approved time off. Use a COUNTIF total for each person if you need a count of marked days. For pending requests and approvals, add a separate status field or use a request log rather than treating every entry as approved.

Can Excel automatically prevent overlapping vacation requests?
Excel can highlight overlapping entries or dates with several people off, but a basic template does not know your staffing rules or approve requests automatically. Conditional formatting can draw attention to a threshold; a manager or the organization’s formal scheduling process must decide whether the coverage is acceptable.

Should I use a calendar or a request log?
Use a calendar when the priority is seeing team availability across dates. Use a log when you need request status, approver, notes, or a record of each submission. Some teams use both, with clear ownership for keeping the overview aligned with the request records.

Can the same vacation schedule work in Excel for Mac or the web?
Most basic tasks, including entering dates, using COUNTIF, and applying conditional formatting, are available in Excel for Mac and Excel for the web. Ribbon placement and some advanced options may differ. The instructions here use Microsoft 365 for Windows, so check the corresponding View or Home commands in your version when the interface is arranged differently.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.