Home	Excel	Access
| Feedback | Part Two of Conditional Formatting |

Conditional formatting of cells in Excel

Excel allows you to format ranges of cells with font colours, background shading, coloured borders and text effects such as bold or italic. These effects allow you to make worksheets much more attractive and readable. They can also draw the attention of the user to important areas such as totals.

Would you like to learn more?

Step by Step Excel 2003 (with CD)

Often you may wish to highlight cells or ranges containing particular important values. For example you could set the font colour as red for any values of overdue items. The fluid nature of spreadsheets means that as the data changes, so will the important cells. Rather than manually editing and changing the cell formatting in an attempt at highlighting any current exceptional values, Excel allows you to apply Conditional Formatting which will automatically change according to cells contents.

I have included 3 examples of using this tool for slightly different purposes:

• Highlighting important exceptional values in a table
• Drawing attention to cells containing errors (or hiding them)
• Creating a balanced scorecard with traffic lights to indicate performance

To use this tool, select the required range (which could be an entire row, column or worksheet), and the menu option Format | Conditional Formatting. A range can contain more than one condition in order to apply different formats in different circumstances.

(1)    Drawing the users attention to exceptional values

In this first example changes the user can enter a value (in cell $E$4) to declare a maximum permitted waiting time. Excel will then format any cells (in bold) which are close to or (in red) any cells which exceed the defined value.

After defining the condition, click on the Format... button and then choose which effects are to be added if the condition is true. A second condition for the same range of cells is set by clicking the Add >> button.

The (descending) rank values in column E identify the 3rd doctor as having the lowest number of patients on her list. Another conditional format for the range B7:B10 applies a blue border to the cell which is equal to the value in cell D$12$.

Note that when comparing a range of cells against the value in a single cell it is important to use absolute values (declared using the $ dollar signs).  Otherwise the reference would change for each cell so that D7 checked E4, D8 checked E5 and so on.

Continued on page 2 >>