Formatting Issues

How to Use Different Excel Colors for Holidays and Weekends

Rana Garcia Sep 27, 2026 875 views

Question details

The user wants to apply conditional formatting in Excel to highlight weekends in green and specific holidays in red within a date range, ensuring holiday colors take precedence.

How to Auto-Format Holidays and Weekends with Different Colors in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic schedule, tracker, or calendar where non-working days need distinct visual highlights to avoid scheduling conflicts.
Observed behavior: Needs to set up specific conditional formatting rules where the holiday condition overrides the weekend condition for dates that overlap.

Before you start

Ensure you have a continuous list of dates in your main schedule range and a separate reference column (e.g., F2:F6) listing the exact dates of your specific holidays.

Solution 1Recommended

Apply and Manage Custom Conditional Formatting Formulas

Use custom formulas in the Conditional Formatting tool to detect weekends and match dates against a holiday list, managing rule priority to display the correct colors.

By applying custom formulas, you can instruct Excel to dynamically evaluate whether a date falls on a weekend or matches a pre-defined holiday list. Controlling the order of these rules ensures accuracy.

Select the target date range

Click and drag to select the data range you want to format in your spreadsheet, such as A2:E. Ensure the active cell is the top-left cell of your selection (e.g., A2).

Create the weekend formatting rule

Go to Home > Conditional Formatting > New Rule. Select 'Use a formula to determine which cells to format'. Enter the formula =WEEKDAY($A2,2)>5. Click Format, choose a green fill color, and click OK.

Create the holiday formatting rule

Click Conditional Formatting > New Rule again. Use the formula =ISNUMBER(MATCH($A2,$F$2:$F$6,0)), assuming your holiday dates are stored in cells F2 through F6. Click Format, select a red fill color, and click OK.

Prioritize the holiday rule

Navigate to Home > Conditional Formatting > Manage Rules. Locate the holiday rule you just created. Select it and click the 'Move Up' arrow until it sits at the very top of the list. Click Apply and OK.

Apply and Manage Custom Conditional Formatting Formulas

Rule Precedence: Placing the holiday rule at the top of the rules list ensures that if a designated holiday falls on a weekend, it will correctly be colored red instead of green.

Efficient Spreadsheet Management

Easily Highlight Weekends and Holidays in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, allowing you to seamlessly manage schedules, distinguish weekends, and highlight holidays exactly as you would in Microsoft Excel.

1. Open your schedule: Launch WPS Spreadsheet and open your tracker or calendar document.
2. Access Conditional Formatting: Highlight your target cells, navigate to the Home tab, and select Conditional Formatting > New Rule.
3. Input formulas: Enter the WEEKDAY and MATCH formulas exactly as you would in Excel to designate weekend and holiday colors.
4. Adjust priorities: Open Manage Rules from the Conditional Formatting menu and move the holiday rule to the top of the hierarchy.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules.Lightweight and runs smoothly on Windows, Mac, and Linux systems.Intuitive Rule Manager interface for easily adjusting priority layers.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my holiday formatting not overriding the weekend formatting?

In the Conditional Formatting Rules Manager, rules are applied in order from top to bottom. If the weekend rule is listed above the holiday rule, the weekend color applies first. Move the holiday rule to the top using the 'Move Up' arrow in the rule manager.

How does the WEEKDAY formula work for identifying weekends?

The formula =WEEKDAY($A2,2) evaluates the date and returns a number from 1 (Monday) to 7 (Sunday). The '>5' condition ensures that only Saturday (which is 6) and Sunday (which is 7) trigger the formatting rule.

Can I highlight the entire row instead of just the date cell?

Yes. By using a mixed reference (absolute column and relative row) like '$A2' in your formula, and applying the rule to the entire row range (e.g., A2:E), the entire row will be colored based on the date condition met in column A.

Excel & Data Problems