avatar

Excel conditional formatting, the highlight of my weekends

by Simon Hurst on 03.11.2010 11:37
This is a fairly simple tip involving conditional formatting and a date function. I was using Excel to keep track of the progress of a project I was working on. This involved entering a list of dates day by day:



I wanted to highlight the Saturdays and Sundays using conditional formatting. Although conditional formatting is most commonly used to change the format of cells depending directly on the value of those cells, it is possible to use a formula rather than a value comparison. In Excel 2007 or 2010 conditional formatting is found in the Styles group of the Home ribbon and in earlier versions on the Format menu. In 2007 and 2010 we need to ignore all the built-in conditional formats and choose New Rule, 'Use a formula to determine which cells to format'. In earlier versions we simply need to choose the 'Formula is' option for the condition.

We want to create a formula that will identify Saturdays and Sundays. We can use the WEEKDAY() function to do this:

=WEEKDAY(A1,2)

The first argument, A1, refers to a cell containing our first date, and the second argument defines which day is used as the first day of the week - 2 uses Monday as day 1.

Selecting all the cells from A1 down and building this into the conditional format to identify Saturdays and Sundays gives us:



Leaving the A1 as relative allows us to apply the format to all selected cells in one go.

We have used the Format button to set a 'Fill' colour for all cells for which the formula evaluates to TRUE.

This should automatically highlight the weekends in our column of dates:



Versions - amendment


Following on from Paul Booth's excellent points re conditional formatting in the different Excel versions, this earlier post on the subject might also be helpful.