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. There are also various new conditional formatting options in Excel 2010 and these are mainly used for emphasising the ranking of values.

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 ribbon option [Home] Styles | Conditional Formatting | New rule | *OR* | Manage rules |. 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 the user can enter a value (in cell E4) 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 New Rule ... button.

The (descending) RANK() values in column E identify the 3rd doctor as having the lowest number of patients and OFFSET() is used to place her name in cell D12. Another conditional format for the range B7:B10 applies a blue border to the cell which is equal to the name in D12.

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