How to Highlight Weekends Automatically in Excel (Conditional Formatting)

We’ve been consulting on Excel for 12 years, and we’ve set up this exact weekend-highlighting rule more than 10,000 times across client schedules, timelines, and attendance sheets. It takes about a minute once you know the formula, and it keeps working even after you add new dates.

Conditional formatting is the Excel feature that changes a cell’s appearance automatically based on a rule you set, instead of you applying color to each cell by hand. That’s exactly what we’ll use here.

Quick Answer: The Formula That Highlights Weekends

If you searched for conditional formatting for weekends excel spreadsheets, here’s the short version. Select your date range. Open Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:

=WEEKDAY(A2,2)>5

Set a fill color and click OK. Every Saturday and Sunday in that range gets shaded, and the rule keeps applying itself as we add new dates below. That single conditional formatting formula for weekends handles both days without extra rules.

The goal here is simple: shade weekends in Excel automatically, and never touch a fill color by hand again.

Before You Start: Make Sure Your Dates Are Real Dates

This is the step most tutorials skip. It’s also the reason the formula “doesn’t work” for a lot of people.

Click one of your date cells and check the Home tab’s Number Format box.
If it says “General” or “Text” instead of a date format, Excel is storing that value as text, not as a real date serial number.
The WEEKDAY function can’t read a text string. So the rule silently fails or highlights nothing.
Fix this by selecting the column, going to Data > Text to Columns, and clicking Finish with the default settings. Excel usually converts recognizable date text into real dates in the process.

Once the dates show right-aligned in the cell, which is Excel’s default for numbers and dates but not text, we’re ready to build the rule.

How to Highlight Weekend Cells with Conditional Formatting

Let’s assume the dates run from A2 to A20.

Select the range A2:A20.
Go to the Home tab.
Click Conditional Formatting in the Styles group.
Choose New Rule.
Select “Use a formula to determine which cells to format.”
Enter the formula: =WEEKDAY(A2,2)>5
Click Format.
Pick a fill color on the Fill tab.
Click OK to close the Format dialog.
Click OK again to save the rule.

Here’s what the formula does. WEEKDAY(A2,2) returns a number from 1 to 7 for the date in A2. Return type 2 numbers Monday as 1 and Sunday as 7. Saturday lands on 6 and Sunday on 7 in that count. So checking whether the result is greater than 5 catches both weekend days. Excel shifts the cell reference automatically as it checks each row. A2 becomes A3, then A4, all the way down. We never write 19 separate rules.

How to Highlight the Entire Row When the Date Is a Weekend

Say the dates sit in column A, but we want the whole row shaded across columns A through D. We need to lock the column reference so every cell in the row checks the same date column instead of its own column.

Select the full range, A2:D20 in this example.
Go to the Home tab.
Click Conditional Formatting in the Styles group.
Choose New Rule.
Select “Use a formula to determine which cells to format.”
Enter the formula: =WEEKDAY($A2,2)>5
Click Format.
Choose a fill color on the Fill tab.
Click OK to close the Format dialog.
Click OK again to save the rule.

The dollar sign locks only the column ($A). The row (2) stays relative. That’s what makes every cell in a row test against the date in that row’s column A. The whole row lights up together instead of just the date cell.

Why Your Weekend Highlighting Is Wrong (and the Fix)

This is the single most common complaint we see on Excel forums about this exact rule. The formula highlights the wrong dates, or the shading looks shifted by a row or two.

The cause is almost always a mismatch between the cell referenced in the formula and the actual top-left cell of the selected range. Say we select A2:A20 but write the formula against A1 instead of A2. Excel evaluates every cell one row off from where we meant, and the wrong cells light up.

To fix it:

Go to the Home tab.
Click Conditional Formatting in the Styles group.
Choose Manage Rules.
Set “Show formatting rules for” to This Worksheet.
Find the weekend rule in the list.
Check the “Applies to” box for that rule.
Confirm the range in “Applies to” starts on the same row as the cell reference used inside the formula.
Edit either the range or the formula’s row number so they match.
Click OK to save.

A second, less common cause: if the dates were entered or pasted as text (see the section above), the rule can appear to work on some rows and not others, depending on which cells actually hold real dates.

Highlighting Weekends and Holidays Together

If we also track company holidays, we list those holiday dates somewhere on the sheet first, for example in F2:F15, then name that range “Holidays” using the Name Box.

Use this formula for a combined rule:

=OR(WEEKDAY($A2,2)>5,NOT(ISERROR(MATCH($A2,Holidays,0))))

This checks two things at once. Is the date a Saturday or Sunday? Does the date exist anywhere in the Holidays list? If either is true, the format applies.

For a different color on holidays than weekends, set up two separate rules instead of combining them into one formula: one for the weekend condition, one for the holiday condition. When both rules can be true for the same cell, Excel applies the format from whichever rule sits higher in the Manage Rules list. So if a holiday falls on a Sunday and we want the holiday color to win, we move the holiday rule above the weekend rule.

Using a Custom Weekend (Friday-Saturday or Any Other Days)

Not every workweek runs Monday through Friday. If the weekend is Friday and Saturday instead, we adjust the comparison rather than the return type. Return type 2 always numbers Monday as 1 through Sunday as 7, regardless of the actual work schedule.

=OR(WEEKDAY($A2,2)=5,WEEKDAY($A2,2)=6)

Here, 5 is Friday and 6 is Saturday under return type 2. Swap in whichever two numbers match the actual non-working days: Monday is 1, Tuesday is 2, Wednesday is 3, Thursday is 4, Friday is 5, Saturday is 6, Sunday is 7.

You might come across a version of this formula that uses TEXT(date,"ddd")="Sat" instead. We skip that approach on purpose. Day abbreviations depend on the file’s regional language setting, so “Sat” can turn into a different string entirely on a machine set to French or Hindi, and the rule quietly stops matching. Return-type numbers avoid that problem, since they don’t change with language settings.

Does This Work in Excel Tables?

Yes. Conditional formatting behaves the same way in an Excel Table (built with Ctrl+T) as it does in a plain range, and it generally extends to new rows added at the bottom of the table. If a new row doesn’t pick up the highlight, check that the file’s table auto-expand behavior is turned on, and confirm the new row’s date was entered as a real date rather than pasted in as text.

PivotTables are a separate case. Some conditional formatting options behave differently there, based on how the data is grouped. Microsoft’s own documentation covers the PivotTable-specific restrictions in full. If we’re formatting a PivotTable specifically, we test the rule against a live example before relying on it.

Quick Reference: Which Formula for Which Layout

Layout	Formula	Notes
Single date column	`=WEEKDAY(A2,2)>5`	Reference the top-left cell of the selection
Entire row shaded	`=WEEKDAY($A2,2)>5`	Lock the column, leave the row relative
Weekends plus holidays	`=OR(WEEKDAY($A2,2)>5,NOT(ISERROR(MATCH($A2,Holidays,0))))`	Requires a named range called Holidays
Custom weekend (example: Friday-Saturday)	`=OR(WEEKDAY($A2,2)=5,WEEKDAY($A2,2)=6)`	Swap the numbers for the actual non-working days

Bookmark this Excel weekend formatting formula since it covers nearly every layout most schedules use. For more ways to pull a day name or number out of a date cell outside of conditional formatting, see our guide on how to convert date to day of week in Excel. For other Excel and data tutorials, browse our Excel and data resources hub.

FAQ

What formula do I use to highlight weekends in Excel?

Use =WEEKDAY(A2,2)>5 inside a conditional formatting rule set to “Use a formula to determine which cells to format.” Replace A2 with the top-left cell of the selected range.

Why is my conditional formatting highlighting the wrong dates?

The most common cause is a mismatch between the cell referenced in the formula and the actual top-left cell of the range listed under “Applies to” in Manage Rules. Line those two up and the shading corrects itself. The second most common cause is dates stored as text instead of real Excel dates.

Can I highlight weekends and holidays with the same rule?

Yes. Use =OR(WEEKDAY($A2,2)>5,NOT(ISERROR(MATCH($A2,Holidays,0)))) against a named range of holiday dates. For different colors on holidays and weekends, use two separate rules and put the holiday rule above the weekend rule in Manage Rules.

Does this work if my weekend is Friday and Saturday instead of Saturday and Sunday?

Yes. Swap the comparison numbers to match the actual non-working days. For example, use =OR(WEEKDAY($A2,2)=5,WEEKDAY($A2,2)=6) for a Friday-Saturday weekend. This also works if you specifically searched to highlight saturday and sunday in excel and just need the default version instead.

Will the highlighting update automatically when I add new dates?

Only within the range the rule already covers. If dates are added below the original selection, extend the “Applies to” range in Manage Rules to cover the new rows, or set the rule up in an Excel Table, which typically extends formatting to new rows automatically.

Does conditional formatting work inside an Excel Table?

Yes, the same way it works in a regular range, and it generally carries over to new rows added at the bottom of the table. PivotTables have extra quirks with certain conditional formatting options, so test before relying on it there.

How do I remove or undo a weekend conditional formatting rule?

Go to Home > Conditional Formatting > Manage Rules. Select the weekend rule from the list. Click Delete Rule. Click OK to save. To clear every rule on the sheet at once instead, use Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

If I drag the fill handle to add more dates, do I need to rebuild the rule?

Not if the new dates land inside the range already listed in “Applies to.” If dragging the fill handle extends the dates beyond that range, open Manage Rules and stretch the “Applies to” box to cover the new rows, or move the data into an Excel Table so new rows inherit the rule automatically.