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:
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.