Conditional formatting of cells in Excel - part three

Excel 2010 has introduced some interesting new ways of formatting cells and using colour and icons to emphasize values. Various coloured shapes, arrows and flags can be used as an alternative to merely shading whole cells.

In this example, traffic light indicators will be added to cells S19:S22 according to their value. The cells each contain the formula =N19/M19 (adjusted for each row). Highlight the range of cells and select [Home] Styles | Conditional formatting | Icon Sets | and choose a set of three coloured circles. This will have no apparent effect and no circles will initially appear because no rules have been set. To do this select [Home] Styles | Conditional formatting | Manage rules |.

Excel only offers Greater Than (> or >=) for defining the bandings and so it is necessary to ensure the coloured shapes are in the appropriate order. In this table high values are bad (red) and therefore click the [Reverse icon order] button in order to switch them around.

Values must be specified to define the ranges which will cause each of the colours to appear. In this example green is 0 to 0.95, amber is 0.95 to 0.999 and red is greater than 0.999.

Excel can be a little temperamental when changes are made to the formula or formatting of particular cells within a range. It may be necessary to Clear Formats in a particular cell and then re-enter the conditional formatting rules for that cell only.

It is not possible to set these icon rules based on formula i.e. the icon cannot be determined by the values in other cells. However, there is a tick box option in the conditional formatting rule dialog box to 'Show Icon Only'. This will hide the value in the cell containing the icon.

Other conditional formatting tools

There are two other variants of this tool within the more recent versions of Excel.

Data Bars (col C) - displays a bar in each cell which shows the relative sizes of the values.

Colour Scales (col D) - shades each cell in a range according to their relative position between the highest and lowest value. It is possible to tweak the Excel default settings using the Manage Rules option.

Click here for details about calculations which depend on the colour of cells.

Click here for an example of an application using conditional formatting - a holiday leave planner for an office.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.