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.

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:

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:

- Hide future dates with Conditional Formatting
- Highlight an Entire Row in Excel Based on One Cell Value
- The Easiest Way to Hide Zeros in Excel
- Create a Search Box in Excel without VBA

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

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 .