You can build a practical employee leave tracker in Excel with three sheets: a Lists sheet for standardized choices and holidays, a Leave Log for one request per row, and an optional Summary sheet for totals and balances. This setup uses drop-down menus, automatic working-day calculations, approval statuses, and conditional formatting without requiring specialist HR software.
The instructions broadly apply to Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and other supported editions, although menu names and placement can vary between Windows, Mac, and web versions.
What you need before starting
- Excel desktop or Excel for the web
- A list of employees
- Leave types, such as Vacation, Sick Leave, Personal Leave, and Unpaid Leave
- Status choices, such as Pending, Approved, Rejected, and Cancelled
- Public-holiday dates for the relevant location
- Annual leave entitlements, if you want to calculate balances
Decide your leave policy first. In particular, establish whether dates are inclusive, whether weekends and public holidays count, whether half-days are allowed, and whether employees follow different work schedules.
Choose the right leave-tracker layout
The most dependable starting point is a structured leave log rather than a manually colored calendar. Every request gets one row, which makes the data easier to filter, validate, summarize, and expand.
#1 Best Overall
- Efficient Weekly Planning - Utilize the 52 Weeks Undated Planner to articulate and prioritize weekly goals and to-do lists. Assign specific tasks to each week for optimal efficiency while allowing flexibility without guilt if a week is missed.
- Elegant and Compact Design - Enjoy a thick cover with gold coil, offering a romantic and gentle aesthetic. The weekly planner notebook's perfect size at 6.1'' x 8.2'' ensures easy portability, making it convenient for daily use.
- Cultivate Healthy Life Habits - Undated weekly planners, weekly goals, To Do list, and habit tracker together for daily affairs. Track healthy habits for each week and use the checkbox as a visual reminder.
- Premium Paper Quality - Experience a smooth writing surface on thick, 100gsm paper that prevents bleed-through. The planner ensures a high-quality feel and enhances the overall writing experience.
- Versatile Usage - Ideal for managing daily affairs, cultivating healthy life habits, and maintaining overall progress. A quick glance provides a comprehensive overview of chores, making it the perfect companion for effective time planning.
| Layout | Best for | Main trade-off |
|---|---|---|
| Basic leave log | Small teams and simple record-keeping | Less visual than a calendar |
| Calendar view | Quickly seeing who is absent | Harder to maintain and summarize by itself |
| Leave-balance tracker | Monitoring entitlement and usage | Requires accurate policy and entitlement data |
You can combine all three, but make the leave log your source of truth.
Create the Lists sheet
Add a worksheet named Lists. Create columns for employees, leave types, statuses, and holidays. For example:
Employees Leave Types Statuses
Alex Morgan Vacation Pending
Jordan Lee Sick Leave Approved
Taylor Smith Personal Leave Rejected
Unpaid Leave CancelledPut holiday dates in a separate column:
Holidays
1/1/2027
5/31/2027
7/5/2027
9/6/2027
11/25/2027
12/24/2027Enter holidays as real Excel dates, not text that merely looks like a date. Text-formatted dates can cause date functions such as NETWORKDAYS to return errors. See Microsoft’s NETWORKDAYS documentation for the relevant date requirements.
For a maintainable workbook, convert each list into an Excel Table. When a drop-down uses a table-based list, adding or removing items can update the list more reliably than using a fixed range. Microsoft explains this approach in its guide to creating drop-down lists.
Name the holiday range
- Select the holiday dates on the Lists sheet.
- Go to Formulas > Define Name.
- Enter
Holidaysas the name. - Confirm the selected range.
A named range keeps formulas readable and lets you maintain holidays separately from the leave records.
Build the Leave Log table
Create a worksheet named Leave Log and enter these headers:
Employee | Leave Type | Start Date | End Date | Leave Days | Status | NotesEach leave request should occupy one row. Useful optional columns include Employee ID, Department, Manager, Request Date, Approval Date, Half-Day or Fraction, Attachment Reference, Remaining Balance, Return-to-Work Date, and Work Location.
- Select the headers and several blank rows.
- Choose Insert > Table, or press Ctrl+T on Windows.
- Confirm My table has headers.
- Click inside the table, open Table Design, and rename it
LeaveLog.
The table gives you filters and helps formulas, formatting, and new records extend as the tracker grows.
Add drop-down menus
Use data validation so that “Vacation,” “vacation,” and “Annual leave” do not become separate categories in your summaries.
Rank #2
- [STAY ORGANIZED ALL YEAR] July 2026 - June 2027 professional day planner with 12 months of monthly and weekly pages for easy academic planning and scheduling; 2 additional monthly pages (May 2026 - June 2026) are included
- [MONTHLY LAYOUTS] Monthly layouts contain previous and next month reference calendars for long-term planning, and a notes section for important projects; Major holidays listed, elapsed and remaining days noted
- [WEEKLY LAYOUTS] Weekly view pages offer ample lined writing space for more detailed planning, allowing you to keep track of your appointments, reminders, ideas and to-do lists every day of the week
- [YEARLY OVERVIEW] Yearly calendar planner includes a convenient list of holidays, reference calendars, contacts pages and extra notes pages to accommodate your scheduling needs
- [BUILT TO LAST] Designed with a flexible cover and premium pages that endure daily use while maintaining a sleek, professional look. Printed on quality FSC-certified paper with convenient laminated tabs that are durable enough to handle daily use throughout the school year
- Select the cells in the Employee column.
- Choose Data > Data Validation.
- Set Allow to List.
- Select the employee list on the Lists sheet as the source.
- Make sure In-cell dropdown is enabled.
- Repeat the process for Leave Type and Status.
For a small fixed list, you can type a source such as Vacation,Sick Leave,Personal Leave,Unpaid Leave. A range or Excel Table is easier to maintain over time. Set the error alert to Stop when you want Excel to prevent entries outside the approved list. Warning and Information allow the user to continue after displaying a message. Microsoft’s guides cover validation settings and alerts.
If Data Validation is unavailable
Worksheet protection or workbook sharing can prevent changes to validation settings. Check whether the sheet is protected or the file is being used in a restricted shared state. Make the structural changes in an editable copy, then protect the finished workbook.
Format the date columns
Select Start Date and End Date, then apply a date format such as m/d/yyyy. Formatting changes how a value appears; it does not convert text into a genuine date. If a date is stored as text, re-enter it as a date or convert it before relying on formulas.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Calculate leave days automatically
For a standard Monday-through-Friday schedule, enter this formula in the Leave Days table column:
=IF(OR([@[Start Date]]="",[@[End Date]]=""),"",NETWORKDAYS([@[Start Date]],[@[End Date]],Holidays))NETWORKDAYS counts whole working days inclusively between the start and end dates and excludes the holidays supplied in the named range. For example, Monday through Wednesday normally returns three working days, not two. It does not know your organization’s leave policy; the weekend pattern and holiday list must match your rules.
If you have not created the named range, use a direct range instead:
=IF(OR([@[Start Date]]="",[@[End Date]]=""),"",NETWORKDAYS([@[Start Date]],[@[End Date]],Lists!$D$2:$D$30))When weekends should count
For a seven-day operation or a policy based on calendar days, use:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=IF(OR([@[Start Date]]="",[@[End Date]]=""),"",[@[End Date]]-[@[Start Date]]+1)This counts every date inclusively, including weekends and listed holidays.
When the weekend is not Saturday and Sunday
Use NETWORKDAYS.INTL for a nonstandard weekend:
=IF(OR([@[Start Date]]="",[@[End Date]]=""),"",NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],"0000110",Holidays))The seven-character weekend string starts on Monday. A 1 marks a nonworking day and a 0 marks a working day. In this example, Friday and Saturday are weekends. Replace the string with the schedule your organization actually uses. Microsoft documents NETWORKDAYS and NETWORKDAYS.INTL.
Rank #3
- Undated Planner with Simple Layout: Come with 12 months of monthly and weekly pages, providing a fresh start for an entire year at any time! This planner features a simplified layout for ease of use, offering spacious writing space to plan your schedule freely.
- Monthly Calendar & Weekly Planner: Each monthly spread with large date box helps you easily mark appointments, agenda, important dates, bills due, etc. Weekly two-page spreads provide generous lined writing space for more detailed planning, helping you keep track of daily tasks and develop habits or skills.
- Additional Planner Features: This calendar planner starts with Yearly Goals and Mind Map pages for goal setting and thoughts organization. It also includes holiday lists to keep on top of your special dates, contact page and extra notes pages to jot down your thoughts.
- Trusted Quality for Full Year Use: Adopted 100GSM thick paper for easy writing and preventing ink bleeding. Measuring 5.4" x 8.4", perfect size to fit in your purse or backpacks and take anywhere. Our cute planner also features an inner pocket, pen loop, and ribbon bookmarks.
- Organize Your Day & Keep Focus: How tricky it can be when a thousand things buzzing around your head! This planner journal is definitely a life saver, helping you stay focused on your tasks throughout the week. Use this notebook to simplify your life and organize your day for maximum efficiency.
Validate the date range
To prevent an end date before a start date, select the end-date cells and choose Data > Data Validation. Set Allow to Custom. If the first data row is row 2 and Start Date is column C while End Date is column D, use:
=OR(D2="",D2>=C2)This permits a temporarily blank end date but rejects an earlier date. Use a Stop alert with a message such as: “End date must be the same as or later than the start date.”
Free tools Windows power users keep installed
One-click scans. No signup required.
Highlight approved and pending leave
To format complete rows, select the relevant Leave Log table range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
For a table beginning on row 2, add these rules:
| Purpose | Formula | Suggested format |
|---|---|---|
| Approved | =$F2="Approved" |
Green |
| Pending | =$F2="Pending" |
Yellow |
| Rejected | =$F2="Rejected" |
Red or gray |
| Invalid date range | =AND($C2<>"",$D2<>"",$D2<$C2) |
Red warning |
Adjust the column letters if your layout differs. Formula-based conditional-formatting rules must evaluate to TRUE or FALSE. More detail is available in Microsoft’s conditional-formatting guide.
Add remaining leave balances
If you track entitlement, create a separate worksheet named Balances with this table:
Employee | Leave Type | Annual Entitlement | Used | RemainingIn the Used column, count only approved requests:
=SUMIFS(LeaveLog[Leave Days],LeaveLog[Employee],[@Employee],LeaveLog[Leave Type],[@[Leave Type]],LeaveLog[Status],"Approved")In Remaining, use:
=[@[Annual Entitlement]]-[@Used]Pending requests can be displayed separately but should not normally reduce the official balance unless your policy says they should. Balance accuracy depends on correct entitlements, approved-status filtering, holiday rules, half-day treatment, carryover rules, and avoiding duplicate entries.
Handle half-days
Add a Fraction column containing values such as 1 or 0.5, then use:
=IF(OR([@[Start Date]]="",[@[End Date]]=""),"",NETWORKDAYS([@[Start Date]],[@[End Date]],Holidays)*[@Fraction])This works when the same fraction applies to the entire request. A half-day on the first day followed by full days needs a more detailed design.
Create a practical summary
Add a Summary sheet for information managers need regularly:
Rank #4
- 52 PAGES UNDATED WEEKLY PLANNER - This weekly planner features 52 undated pages, measuring 11 x 8.5 inches (A4) in a horizontal layout. It provides ample space for year-round planning, allowing you to schedule at your own pace without wasting pages or skipping dates.
- THOUGHTFUL FEATURES FOR PLANNING - Our weekly to do list notepad is designed with a top priority, a low priority, and a follow-up section, allowing you to prioritize and stay organized. It also has to do list part, notes part, which can help you track important daily events and develop daily habits.
- SPIRAL BOUND WEEKLY PLANNER - The weekly planner is spiral-bound for easy page turning and the option to tear off used pages for new plans. It features a transparent cover that protects your pages from dirt and damage.
- 100 GSM THICK PAPER - Our desk calendar planner is crafted with premium 100 GSM FSC-certified wood-based paper, paired with sturdy cardboard backing to resist ink bleeding and ensure a smooth writing experience. Durable, eco-conscious, and designed for daily use.
- VERSATILE USAGE - The weekly to-do list notepad is designed to meet all your planning needs and help you stay organized. It's perfect for work, home and school, including habit tracker, event organization, work schedules, travel plans, and more.
- Approved days by employee
- Leave used by type
- Remaining entitlement
- Pending request count
- Current-year leave
- Employees absent today
- Monthly leave totals
Examples:
Approved leave for the employee named in A2:
=SUMIFS(LeaveLog[Leave Days],LeaveLog[Employee],A2,LeaveLog[Status],"Approved")Pending request count:
=COUNTIF(LeaveLog[Status],"Pending")Approved Vacation days:
=SUMIFS(LeaveLog[Leave Days],LeaveLog[Leave Type],"Vacation",LeaveLog[Status],"Approved")Approved leave beginning in the year in B1:
=SUMIFS(LeaveLog[Leave Days],LeaveLog[Employee],A2,LeaveLog[Status],"Approved",LeaveLog[Start Date],">="&DATE(B1,1,1),LeaveLog[Start Date],"<"&DATE(B1+1,1,1))That last formula filters by start date only. A leave period that crosses December and January can therefore be assigned incorrectly for year-by-year accounting. Split cross-year requests into separate rows or calculate the working-day overlap with the selected year.
Recommended Free Tools
For a more flexible report, select the LeaveLog table and choose Insert > PivotTable. Use Employee or Leave Type as rows, Leave Days as values, and Status as a filter.
Add an optional calendar view
A calendar is useful for seeing absences, but it should be a view built from the log rather than the only place where data is stored. Create a grid with employees down column A and dates across row 1. In B2, a simple approved-leave test is:
=IF(COUNTIFS(LeaveLog[Employee],$A2,LeaveLog[Start Date],"<="&B$1,LeaveLog[End Date],">="&B$1,LeaveLog[Status],"Approved")>0,"L","")You can replace L with codes such as V for Vacation, S for Sick Leave, or P for Personal Leave. This formula only confirms that approved leave exists; it does not identify which leave type applies when requests overlap.
If you prefer a ready-made calendar, Microsoft provides Excel calendar templates that can include vacation planners. Templates are faster to start with, but check their assumptions about weekends, holidays, and balances before relying on them.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Check for overlapping leave
Duplicate or overlapping approved rows can inflate totals. Add a helper column with this formula:
=COUNTIFS(LeaveLog[Employee],[@Employee],LeaveLog[Start Date],"<="&[@[End Date]],LeaveLog[End Date],">="&[@[Start Date]],LeaveLog[Status],"Approved")>1A result of TRUE indicates that another approved row overlaps the current employee’s leave, because the current row is included in the count. Review those rows before approving or reporting them.
Protect and share the tracker
- Unlock only the cells users should edit.
- Protect formula cells and, if appropriate, the Lists sheet.
- Hide the Lists sheet if users do not need to edit it.
- Save a backup before making structural changes.
- Keep one controlled master copy instead of circulating conflicting attachments.
- Test the workbook with a non-owner user.
Configure validation before protecting the worksheet, and unlock validated input cells before protection. Worksheet protection is not the same as security: it does not replace controlled access, backups, version history, or a formal HR system.
Saving the file in OneDrive or SharePoint may support collaboration, but Excel desktop and Excel for the web can differ in feature behavior. Some validation scenarios may need to be created in desktop Excel before being used through web-based Excel.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteBest Value
- THE ORIGINAL SPIRAL BIBLE THE TRUE BIBLE IN A YEAR EDITION: This is the original edition of Spiral Bible and the trusted Bible in a Year resource that set the standard for all others. Designed to lay flat for comfortable reading and note taking, it helps you stay engaged from Genesis to Revelation. Unlike traditional chronological or book-by-book approaches, this study illuminates 52 essential themes of the Christian faith, from Creation to Praise, helping you build a comprehensive biblical worldview while developing practical faith.
- BUILT FOR DAILY READING WITH A 52 WEEK SPIRITUAL GROWTH PLAN: This beautifully structured Bible in a Year guides you through Scripture with intentional weekly themes, daily passages, and meaningful insights that deepen your understanding of Gods Word. Each day encourages focus, consistency, and reflection, making it easier than ever to stay committed. If you have searched for a daily Bible reading plan that is easy to follow, this layout provides structure and encouragement for true spiritual transformation.
- PRACTICAL DESIGN FOR NOTE TAKING AND REFLECTION: This Bible in a Year edition is thoughtfully created to support a peaceful rhythm of daily time with God. The spacious 8.5 in by 11 in size sits beautifully on a desk or bookshelf and slips easily into a backpack so it is always within reach. A durable hardcover and copper coil protect your pages, while 50 lb premium paper, a clear 10 pt font, and features full page sections giving you room to reflect, journal, and pray. With guided daily readings and weekly themes, this edition gently leads you through Scripture at a steady and meaningful pace.
- WEEKLY DEVOTIONALS SCRIPTURE PASSAGES AND REFLECTION QUESTIONS: Each week features a thoughtful devotional, seven curated Scripture readings, and three reflection questions that help connect biblical truth with everyday life. A weekly blessing invites you to apply Gods Word with joy and purpose. This structured journey strengthens your understanding of major biblical themes from Creation to Praise, helping you build a confident and complete biblical worldview. It is a powerful resource for anyone seeking deeper guided Bible study.
- A BEAUTIFUL CHRISTIAN GIFT AND TRUSTED AUTHENTIC EDITION: Give the gift of spiritual growth with a Bible created to be used, loved, and carried daily. Spiral Bible is the original spiral bound Bible created for real study not an imitation. Its notebook style format, durable construction, and guided design make it perfect for birthdays, holidays, baptisms, graduations, or church groups. For anyone seeking a Christian gift that inspires devotion, consistency, and deeper engagement, this authentic edition stands above the rest.
Troubleshooting common problems
The drop-down does not appear
Check that the validation type is List, In-cell dropdown is enabled, and the source range contains values. If the command is unavailable, check worksheet protection or sharing restrictions.
The holiday is not excluded
Confirm that the holiday cells contain genuine Excel dates, that the named range is spelled exactly Holidays, and that the holiday falls inside the referenced range.
Weekends are counted incorrectly
The formula may be using calendar-day subtraction or the wrong weekend pattern. Use NETWORKDAYS for Monday-Friday schedules, NETWORKDAYS.INTL for custom weekends, or inclusive subtraction when calendar days should count.
The formula returns #VALUE!
Check for text-formatted dates, invalid date arguments, broken named ranges, inconsistent references, or references to unavailable workbooks. Microsoft documents common NETWORKDAYS errors and broader Excel #VALUE! causes.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →The formula does not fill into new rows
Confirm that the source range is an Excel Table and that the formula is in a table column. If necessary, click into the first blank table row and re-enter the formula.
Invalid values appear despite validation
Copying and pasting can bypass the behavior users see when typing directly into validated cells. Review pasted data, use conditional formatting to flag invalid values, and limit editing access where practical.
When Excel is the wrong tool
Excel is a sensible choice when one person manages a small team, the rules are simple, approvals happen manually, and the main need is visibility. It becomes a poor fit when you need multiple approvers, employee self-service, complex accrual or carryover rules, payroll integration, a formal audit history, role-based permissions, or high-volume concurrent editing.
At that point, compare the effort of maintaining the workbook with dedicated leave-management or HR software. Microsoft’s Excel product page and template library are useful starting points for spreadsheet-based setups. A dedicated HR platform may be more appropriate when time-off records must connect to employee records, payroll, approvals, or compliance controls.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
