Highlight Weekends in Excel with Conditional Formatting


EasyExcel_37_1_Highlight weekends with conditional formatting

The picture to the right shows a table with some sales figures for July. There’s nothing wrong with the table as it is, but I find it very hard to read and make sense of it. There are just a lot of numbers and dates, and you can’t even distinguish between weekdays and weekends. If we could highlight the weekends (or weekdays) it would be a lot easier to read these numbers. In this example I want to highlight the Saturdays and Sundays. Here’s how we’ll do it:

First, we have to think about how we can identify the Saturdays and Sundays. The WEEKDAY function returns a number from 1 to 7. There are several return type options (Sunday through Saturday, Monday through Sunday, Tuesday through Monday etc.), where each day corresponds to a certain number.

EasyExcel_37_2_Highlight weekends with conditional formatting

These are the numbers of the weekdays with return type 2:

  • 1 – Monday
  • 2 – Tuesday
  • 3 – Wednesday
  • 4 – Thursday
  • 5 – Friday
  • 6 – Saturday
  • 7 – Sunday

As we see, every date in our table that returns a number that is larger that 5, (ie. 6 or 7) is a Saturday or Sunday, and should be highlighted. We will use Conditional Formatting, so we have to write a formula that returns a TRUE when the WEEKDAY is larger than 5:

=WEEKDAY($A2,2)>5

Note that I locked the column reference ($A2) to make sure that the Conditional Formatting only looks at the A column.

Select the whole table, Go to the Home ribbon, choose Conditional Formatting and click New Rule. Choose Use a formula to determine which cells to format, and type the formula in the field. Finally, click the Format button and choose font or background colour.

When you have done this a few times, you will be able to write the formula without even having to think, and this whole operation is done in seconds. This is the result:

EasyExcel_37_3_Highlight weekends with conditional formatting

Of course, if you’d rather highlight Monday-Friday, you only have to change the last part of the formula: =WEEKDAY($A2,2)<6.

More on Conditional Formatting:


Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Clip to Evernote

One thought on “Highlight Weekends in Excel with Conditional Formatting

  1. To highlight Weekends using conditional formatting , is quite simple. You just need to enter 1 formula! How to make a dependent drop-down list in Excel . Find number of months between 2 dates in Excel .

Leave a Reply

Your email address will not be published. Required fields are marked *


− 6 = three

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>