How to Highlight Weekends in Excel

To highlight weekends in Excel, use a conditional formatting rule with the formula =WEEKDAY(A2,2)>5. It colours every Saturday and Sunday, and the colours move by themselves when dates change. This guide shows the rule step by step, then how to colour whole rows, calendars laid out across columns, holidays, and how to stop blank cells lighting up.

How does the weekend formula work?#

The rule uses WEEKDAY, which returns a number for the day of the week. Its second argument, the return type, sets how the days are numbered. Return type 2 is the easiest for weekends:

So WEEKDAY(A2,2)>5 is TRUE only for Saturday and Sunday. Conditional formatting applies your colour wherever the formula returns TRUE. The article on how the WEEKDAY function works explains the other return types.

How to highlight weekend dates step by step#

Here is the full process for a column of dates in A2:A20:

1. Select A2:A20, starting from A2.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. In the box, type =WEEKDAY(A2,2)>5.
5. Click Format, choose a fill colour on the Fill tab, and click OK twice.

Every Saturday and Sunday in the list is now coloured. Change a date and the colour updates.

The formula is written for the first cell of the selection, A2. Excel then adjusts it for each other cell, just like filling a formula down. If you select from A1 but type A2 in the rule, every colour lands one row off. This is the most common mistake with formula rules. The guide to conditional formatting basics covers how these rules work in general.

How to stop blank cells being highlighted#

Add a check that the cell isn't empty. Excel treats a blank cell as 0, and day 0 of Excel's calendar is a Saturday. So the basic rule colours every empty cell too, which looks wrong when your list has space for more dates.

Use this rule instead:

=AND(A2<>"",WEEKDAY(A2,2)>5)

AND needs both tests to be TRUE: the cell has something in it, and the day is a weekend. Empty cells now stay uncoloured.

How to highlight the whole row for weekend dates#

Select the whole table and lock the date column with a dollar sign. For a shift table in A2:D20 with dates in column B:

=AND($B2<>"",WEEKDAY($B2,2)>5)

The $ before B keeps every cell looking at column B, while the row number still changes from row to row. So C5 and D5 both check B5. Without the $, column C would check the cell next to it and the colours would be wrong. The guide to highlighting a whole row based on one cell explains this pattern in detail.

How to highlight weekends in a calendar across columns#

When dates run across a row, lock the row instead of the column. Say a monthly timesheet has dates in B1:AF1 and staff names down column A. Select B1:AF10 and use:

=WEEKDAY(B$1,2)>5

Now every cell checks the date at the top of its column, so whole weekend columns are shaded from top to bottom. This makes rotas and attendance sheets much easier to read.

To build the dates themselves, type the first day of the month in B1 and fill right, or use =B1+1 in C1 and copy it across. For a new month, change only B1. If you want the dates to stop at the end of the month, EOMONTH helps; see EDATE and EOMONTH explained.

How to highlight holidays as well#

Put your holiday dates in a list, for example H2:H10, and add a COUNTIF test with OR:

=AND($A2<>"",OR(WEEKDAY($A2,2)>5,COUNTIF($H$2:$H$10,$A2)>0))

COUNTIF checks whether the date appears in the holiday list. If it does, or if the day is a weekend, the cell is coloured. Lock the holiday range fully with $H$2:$H$10 so it doesn't move.

You can also use a second rule with a different colour for holidays, so people can tell them apart from normal weekends.

What if your weekend isn't Saturday and Sunday?#

Change the test to match your days off. With WEEKDAY's default return type, Sunday is 1 and Saturday is 7, and Friday is 6. For a Friday and Saturday weekend:

=OR(WEEKDAY(A2)=6,WEEKDAY(A2)=7)

For a single day off, such as Sunday only, use =WEEKDAY(A2)=1.

The same tests work in normal cells too. For example, =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") labels each date, and =SUMPRODUCT(--(WEEKDAY(B2:B8,2)>5)) counts weekend dates in a range. Once weekends are clear, you can colour other date problems the same way, such as overdue and upcoming dates.

How to edit or remove a weekend rule#

Open Home > Conditional Formatting > Manage Rules to see every rule on the sheet. Change Show formatting rules for to This Worksheet, or you'll only see rules for the cells you selected.

From here you can:

• Edit Rule to fix the formula or change the colour.
• Change Applies to when you add more rows, for example from =$A$2:$D$20 to =$A$2:$D$200.
• Delete Rule to remove it completely.

If a rule seems to do nothing, check its Applies to range first. Rules that point at the wrong cells are a common cause of missing colours, especially after rows have been copied or moved.

Practise it#

Formula-based conditional formatting is a skill that separates confident Excel users from beginners in many job tests. Practise conditional formatting in a live spreadsheet, then practise WEEKDAY, EDATE and EOMONTH to get comfortable with the date functions behind the rules. Each lesson checks your work instantly. Or try the exercise on this page first.