Conditional formatting of cells in Excel - part two

The rules may be based on either the cell value of each cell in the range or on a formula which may refer to another cell and will switch on the formatting if the result of the formula is TRUE.

More examples of using Conditional formatting.

(2) Highlighting or hiding errors

A condition can be added to a range of cells which will either highlight errors (to make them easier to spot) or to hide the error messages (such as #N/A or #DIV/0!) from the user.

This example uses a condition based upon a formula rather than the cell value.

Highlight the range G17:I20 and then add a new rule. Use cell references which are appropriate to the formula in the first cell of the range (G17) and ensure that cell references are relative (i.e. without the absolute $ dollar indicators). The formula will automatically change for each cell in the range.

You could use the formatting to change the font colour to white, thereby hiding any error from the user.

(3) Colouring a balanced scorecard with traffic light indicators (method 1)

Balanced scorecards are a popular management reporting tool and will invariably be created in an Excel spreadsheet. They show a series of important figures and ratios which give a quick snapshot of the performance of a business. Typically they incorporate colours to show whether a figure is good (green), borderline (yellow) or bad (red).

Conditional formatting can be used to set the background colors of cells, thereby providing a dynamic indicator.

Formulas have been entered in column P, dividing the Actual by the Target except in cell P21 where the formula is reversed. It depends on whether it is a good or bad thing for the Actual to exceed the Target.

• [P20] =N20/M20
• [P21] =M21/N21

The conditional formatting can be based upon the value within each cell and the values themselves may be hidden by formatting the font colour the same as the background colour.

Alternatively it would be possible to avoid having cell values in P19:P22 and base the formatting rules on formulas which relate to cells M19:N22. Use whichever method is easiest.

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.

Click here for an example of the new Excel 2007/10 conditional formatting using icon sets, data bars and colour scales

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.