Friday, June 10, 2005

Using Conditional Formatting in Excel

Conditional formatting can be used to great effect in spreadsheets to compare results to expectations and visually illustrate variances.

For example a spreadsheet of dates (eg birthdates). Let say you had a list of dates in column B and you wanted to see visually in the sheet whether or not a date was before or after today. All dates that have passed so far this year could be light green and all dates including today that have not come yet can be bright yellow.
A conditional format is the way to do this. In fact we need two.
Select the first date (in cell B1 in this example) and then click Format | Conditional Formatting.
In the first condition select Formula and then type this formula =B1< TODAY() and then select patterns and light green.
Now click Add to get the second condition and type this formula =B1>= TODAY() and then select patterns and bright yellow.
Now Copy and Paste Special As Formats over the rest of the cells. They should now all be either light green or bright yellow depending on the date in relation to today.

Note: Conditional Formats override existing formats in a cell if their conditions return True.


Posted by: Jethro | 12:50 AM |