An effective way to that little bit of extra pizz-azz to the presentation of your Excel spreadsheet is conditional formatting. This allows you to emphasis important values and make them stand out visually from the rest of your spreadsheet, as well as making this characteristic part of the overall design of the spreadsheet, rather than just doing manual formatting cell-by-cell after the event. You can define and attach a rule to an entire range, such as a row or column, and thus format whatever values might occupy that range in a standardised way if they meet your chosen criteria.
For example, if you have values that you want to measure against a relevant benchmark or threshold, you can then choose a format such as font, bold, italic, font colour, cell colour, border (or any combination thereof) and those values will accept that format whether they occupy that range currently or in the future. You need simply to highlight (select) the range in question, and then go to the Home tab and select Conditional Formatting. If you intend to measure the values against a pre-defined benchmark, the best option is to select Highlight Cell Rules from this menu. The sub-menu for this option allows you to select a comparison (i.e. Greater Than, Less Than, Between etc). For example, if your threshold is 4000, you can then ascribe a format to values that are below this number via the menu on the right. See the example below:
This will give cells that contain numbers that are less than 4000 a dark red font colour set against a light red background.
Should you wish to modify this rule, with either or both a different criteria or format, you can do so via the Conditional Formatting Rules Manager, which can be found by selecting Manage Rules… from the foot of the Conditional Formatting menu:
You can modify the rule(s) you have defined via the Edit Rule… button at the top of this dialog box. Note that you can also delete rules via this dialog and create new rules as well, as an alternative method to doing so via the Conditional formatting menu.
You can create as many different rules for the same range as you wish, and also produce some more elaborate formatting effects by making full use of the variations available on the Conditional Formatting menu. More on this in upcoming posts…