Objective: To make it easier to understand the significance of values in Excel worksheets and to help users focus on key information.
A conditional format is a cell-based format that is automatically applied by Excel depending on a condition; for example setting a red background for any cell that is above a set threshold value.
Conditional formatting was one of the areas that benefitted greatly from the enhancements introduced in Excel 2007. Prior to the 2007 update, each conditional format had to be set up manually and a maximum of three different conditional formats could be applied to any one cell or range of cells.
The 2007 updates fell into two main categories: conditional formatting was made easier to use by allowing default formats to be set up with just a couple of mouse clicks, and the type of formats that could be applied was expanded greatly with the inclusion of graphic formats that could be applied to ranges of cells.
The improvements didn't stop with Excel 2007, Excel 2010 further extended the detailed capabilities of conditional formats, allowing the selection of individual icons, or no icon at all, in the icon sets settings.
In this first part of our look at conditional formatting we will look at the basics of how conditional formatting works before we go on to look at more advanced features and practical uses next time.
Simple conditional formats
At its simplest, a conditional format can apply a chosen format by comparing the value in a cell with one or more threshold values. The conditional formatting dropdown is in the Styles group of the Home ribbon tab. The dropdown displays two types of rules:
- The Highlight Cells Rules option applies a format based on comparing the value in one or more cells with threshold values
- The Top/Bottom Rules option applies a format based on a comparison of the values in all the selected cells
For both of these rules types, Excel displays a series of options such as ‘Greater than…’ or ‘Less than…’ for Highlight Cells Rules and Top or Bottom by number of items or percentage, or Above or Below Average, for Top/Bottom Rules.
Once you have chosen the type of rule to apply, Excel will suggest both a value and a format. Other built-in formats can be selected from the dropdown list which also includes a Custom Format… option. This allows you to select any of the options from the Number, Border and Fill tabs of the Format Cells dialog and the Font Style, Colour and Strikethrough effect from the Font tab.
For Top/Bottom Rules the percentage can be entered as a whole number from 1 to 100 (reasonably obviously) or, for a number of items, from 1 to 1000 (slightly less obviously).
For Highlight Cells Rules the values that can be entered depend on the type of rule. Text that Contains… requires a text value and A Date Occurring… allows you to select from a list of periods such as Yesterday, Last Month, This Month and Next Month. However, you can also use text or date values with the more general rule types: Greater Than…, Less Than…, Between… and Equal To….
Where you are able to enter a value, as well as typing the value in, you can also refer to a cell, making it much easier for a user to set the threshold value or values, or allowing the values to be set automatically using a formula.
In the following example, we have used Between… and set the first value to a cell containing the formula =TODAY() to return today's date and the last value to a cell containing the formula =EOMONTH(TODAY(),0) to give the last date of the current month:
Graphical Conditional Formats
The next group of options in the Conditional Formatting dialog includes three graphical formats that would usually be applied to a range of cells by comparing the values with a set of threshold values:
- Data Bars adds a bar to each cell with the width based on the value in each cell. Excel 2010 added the ability to choose Solid Fill data bars as well as the Gradient Fill that was included in Excel 2007. Other 2010 enhancements included more control over the detail of the bars such as being able to control how negative values are dealt with
- Colour Scales applies a range of colours, based on two or three defined colours, to a range of cells. The result is often known as a heat map and can be particularly useful for highlighting patterns in large blocks of data. For example, a list of order values sorted by date might show 'hot' areas around month ends showing a tendency to try and close large deals by the month end
- Icon Sets adds one of a set of icons at the left-hand side of each cell. The icons used can be based on two, three or four values using three, four or five icons respectively (the 'bottom' icon is always used for values less than the threshold value of the next highest icon):
Multiple conditional formats
As can be seen in the example above, it is possible to apply multiple conditional formats to a cell or range of cells. It is also possible to create any of the different types of rule from scratch. These detailed options are at the bottom of the Conditional Formatting dropdown:
- New Rule… allows you to create any of the types of rules we have discussed so far, and also a rule based on a formula
- Clear Rules clears existing rules from the cells currently selected, the entire worksheet or a Table or PivotTable
- Manage Rules… displays all the rules in use in the selected cells, an entire sheet or a Table or PivotTable. An individual rule can be selected and then deleted or edited. The Edit Rule button displays the detailed options for the selected rule:
Here we have selected our Icon Set rule and clicked Edit Rule to display the Edit Formatting Rule dialog. From Excel 2010 onwards each icon can be chosen individually from the dropdown or you can select No Cell Icon. We have chosen to just show a red spot for all values in the top third.
Having looked at how to set up the various different type of conditional format, next time we will look at some practical examples of how conditional formatting can be used to help your Excel worksheets convey their message quickly and effectively.
EZ guides – where next?
As we embark upon the next set of EZ Guides, it would be very helpful to hear of any particular areas that you would like us to cover. Please add a comment with any suggestions that you may have.
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.