How to Add Holidays to an Excel Calendar (Step-by-Step)

Create a Holidays Reference Table on a Separate Worksheet

Right-click any sheet tab at the bottom of your workbook and select Insert → Worksheet. Rename the new sheet to "Holidays". Set up two columns:

Enter all the holidays you want to track. Your completed table should look like this:

A complete copy-paste-ready list of all 2026 US federal holidays is provided in the reference table below.

Flag Holiday Dates Using COUNTIF

This formula checks whether each date in your calendar grid appears in the Holidays list. It's useful as a helper value for other calculations (like counting working days) or as the basis for conditional formatting.

In a helper row below your calendar date cells (or in a separate helper column), enter this formula:

Where A7 is the cell containing your calendar date. Copy this formula across all date cells in your calendar grid.

How it works:

• Holidays!$B:$B — searches the entire Date column of the Holidays sheet
• A7 — the calendar date cell being checked
• Returns 1 if the date is found in the holiday list (i.e., it's a holiday)
• Returns 0 if the date is not found (i.e., it's a regular day)

Display Holiday Names Automatically

To show the holiday name below or next to each date, you need a lookup formula. There are two options depending on your Excel version:

Place whichever formula you choose in a row below your date numbers in the calendar grid. Copy it across all date cells.

How the INDEX/MATCH version works:

• MATCH(A7, Holidays!$B:$B, 0) — finds the row number where the calendar date matches a holiday date (the 0 means exact match)
• INDEX(Holidays!$A:$A, ...) — returns the holiday name from column A at that row number
• IFERROR(..., "") — if no match is found (regular day), display blank instead of a #N/A error

For more on these lookup functions, see our full VLOOKUP & XLOOKUP guide.

Select Your Calendar Date Range

Click and drag to select all the cells containing dates in your calendar grid. For a standard monthly calendar, this is typically a 7-column by 5- or 6-row block (e.g., A7:G12). Make sure you're selecting only the date cells, not headers or labels.

Open Conditional Formatting — New Rule

Enter the COUNTIF Formula

In the formula field, enter:

Replace A7 with the first cell in your selection (the top-left cell of the range you highlighted in Step 1). Excel adjusts the reference automatically for every other cell in the selection.

How it works: For each cell in your selection, Excel checks if that cell's value appears anywhere in the Holidays date column. If COUNTIF returns 1 or more (the date exists in the holiday list), the condition is TRUE and the formatting applies to that cell.

Choose Your Highlight Format and Apply

Click the Format button in the rule dialog and configure your holiday appearance:

• Fill tab: Choose a background color — light red (#FCE8EA), gold (#FEF9E7), or light blue (#E3F0FF) all work well
• Font tab (optional): Make the text bold and/or change the font color to a darker shade matching the fill
• Border tab (optional): Add a colored border for extra visual distinction

Click OK twice — once to close the Format Cells dialog and once to apply the rule.

Add a "Type" Column to Your Holidays Table

On your Holidays worksheet, add a third column:

Create Separate Conditional Formatting Rules

Instead of one rule, create separate rules for each type, each with its own color:

Rule 1 — Federal holidays (red fill):

Rule 2 — Company holidays (blue fill):

Rule 3 — School closures (green fill):

COUNTIFS works like COUNTIF but checks multiple conditions — the date must match AND the type must match. Apply a different fill color for each rule, and your calendar will display a color-coded legend of holiday types at a glance.

Date Format Mismatch (Most Common Problem)

Your calendar dates and holiday dates must both be real Excel date values, not text strings that look like dates. Here's how to check:

• Click on a date cell in your Holiday table. If the formula bar shows a number like 46023 (Excel's internal date serial number), it's a real date.
• If the formula bar shows 1/1/2026 as text (left-aligned in the cell, possibly with a green triangle in the corner), it's stored as text and COUNTIF won't match it.

Fix: Select the text-date cells, go to Data → Text to Columns → Finish. This forces Excel to re-evaluate the cells and convert text to dates. Alternatively, use =DATEVALUE("1/1/2026") to create a proper date.

Wrong Cell Reference in Conditional Formatting

The formula in your conditional formatting rule should reference the first cell of your selected range. If you selected A7:G12, the formula should use A7 (not A1, not $A$7). Excel auto-adjusts the reference for all other cells in the range.

Fix: Go to Home → Conditional Formatting → Manage Rules, click your rule, click Edit Rule, and verify the cell reference matches the first cell of the "Applies to" range.

Absolute vs. Relative Reference Confusion

In the COUNTIF formula for conditional formatting, the references work like this:

If you use $A$7 (fully absolute) for the calendar cell, every cell in your selection checks only the value in A7 — meaning every cell gets the same result.