|
|
|||
Conditional formatting of cells in Excel - part two |
[Back to Part One] |
The conditions 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.
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. Also, because the formula is required to change to match each cell in the range, the references must be left as relative (i.e. G17) without the absolute $ dollar indicators.
You could use the formatting to change the font colour to white, thereby hiding any error from the user.
Balanced scorecards are a popular management reporting tool and will invariably created on 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.
 |
 |
Formulae 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.
|
The same conditional formatting was applied to the range P19:P22 and is based upon the value within each cell. The values themselves have been hidden by formatting the font colour the same as the background colour.
Alternatively it would have been possible to avoid having any calculations in P19:P22 by using formulae in the condition rules, but in this example the rules would have needed to be modified in cell P21. 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.
| Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about obtaining this file |
| file: conditional-formatting2.htm | © meadinkent.co.uk |
