How to Automatically Highlight Weekends and Holidays in Microsoft Excel Using Conditional Formatting

Project managers, financial analysts, and shift coordinators frequently build schedules, timesheets, and Gantt charts in Microsoft Excel. When entering daily project timelines across months or quarters, identifying non-working days manually is prone to human error. If weekends and official public holidays are not visually distinguished from working days, scheduling errors and inaccurate resource allocations inevitably occur.

Microsoft Excel features conditional formatting rules driven by native date formulas that dynamically highlight Saturdays, Sundays, and custom holiday dates. As your project dates shift or calendar templates update, Excel automatically shades the non-working columns or rows in your designated accent colour without requiring manual formatting changes.

Highlighting Saturdays and Sundays with the WEEKDAY Function

The WEEKDAY function evaluates any given date serial and returns an integer representing the day of the week.

1. Open your spreadsheet in Microsoft Excel.
2. Highlight the data range you want to format (for example, row 4 containing calendar dates across columns B4:AF4, or the full grid range B4:AF30).
3. Click the Home tab in the Excel ribbon.
4. Click Conditional Formatting in the Styles group, then choose New Rule.
5. In the New Formatting Rule dialogue, select Use a formula to determine which cells to format.
6. Under Format values where this formula is true, enter the WEEKDAY evaluation formula:

   =WEEKDAY(B$4, 2) > 5

   • B$4: References the first date cell in your range. Locking the row coordinate ($4) ensures that every cell in the column inspects the header date while allowing the column to adjust horizontally across the sheet.
   • 2: Return type parameter where Monday = 1, Saturday = 6, and Sunday = 7.
   • > 5: Evaluates to TRUE for both Saturday (6) and Sunday (7).
7. Click the Format button.
8. Go to the Fill tab, choose a subtle shading colour (such as light grey or soft blue), and click OK.
9. Click OK again to apply the rule. All weekend columns are now shaded automatically.

Adding Custom Public and Company Holidays with the MATCH Function

Standard weekend highlighting does not account for regional public holidays or scheduled office closures. You can integrate a dedicated holiday list into your formatting logic.

1. Create a dedicated reference range on your sheet (or on a separate tab named Holidays) listing all non-working holiday dates in a column (for example, Holidays!$A$2:$A$25).
2. Select your schedule grid (B4:AF30).
3. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
4. Enter the ISNUMBER(MATCH(...)) formula:

   =ISNUMBER(MATCH(B$4, Holidays!$A$2:$A$25, 0))

5. Click Format, select a distinct fill colour (such as light orange or soft yellow) or text font style, and click OK.
6. Click OK to save the rule. Any date in your header row matching an entry in your holiday table is highlighted immediately.

Combining Weekends and Holidays into a Single Formula

If you prefer applying a single uniform shading style to both weekends and statutory holidays simultaneously, combine the criteria using the OR function:

=OR(WEEKDAY(B$4, 2) > 5, ISNUMBER(MATCH(B$4, Holidays!$A$2:$A$25, 0)))

Managing Rule Precedence in Conditional Formatting

When multiple conditional formatting rules are active across the same calendar cells:

• Click Home > Conditional Formatting > Manage Rules.
• Ensure your Holiday rule is positioned at the top of the list if you want holiday colours to take priority over weekend shading.
• Check the Stop If True box next to high-priority rules to prevent lower-tier rules from overriding font colours or fill styles.