MeadInKent
| Feedback | [1] Conditional Formatting | [3] Icons and shading |

[Back to Part One]

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

conditional formatting

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.

example

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)

example

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.

example

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


excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: conditional-formatting2.htm meadinkent.co.uk 2016 Last modified Jan14 CMIDX S3 P2B Y