| Feedback | [2] Balanced scorecard | [3] Icons and shading |

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:

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.

condtional formatting
Range Rule Purpose of formatting
B7:B10 Cell Value = $D$12 Does the name equal the name in D12?
D7:D10 Cell Value between $E$4-2 and $E$4 Is the waiting time close to the target (i.e. 12 to 14)?
D7:D10 Cell Value greater than $E$4 Is the waiting time more than the target?

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.

[E11] =MATCH(1, E7:E10, 0) search for a rank value of 1 in the range E7:D10
[D12] =OFFSET(B6, E11, 0) return a value 3 rows and 0 columns offset from B6

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

file: conditional-formatting.htm 2016 Page last modified Jan14 CMIDX S3 P2a Y