Conditional formatting helps users focus on important aspects of a spreadsheet, highlight errors and identify important patterns in data.

Conditional formats can apply basic font and cell formatting such as number format, font colour and other font attributes, cell borders and cell fill colour. In addition, there is a range of graphical conditional formats that helps with visualising data by using icon sets, colour scales, or data bars.

The chosen conditional format is applied to a cell based on a condition you set or a condition that Excel generates by comparing the values of cells in a range (e.g. in a list of staff salaries, a conditional format could be applied to any salaries greater than a certain amount). The graphical conditional formats would be applied to the column of salaries and would, by default, be based on an analysis of the highest and lowest values in the list but this can be overridden if required.

Conditional formats can be applied to highlight certain cells or can be used in much more complicated and imaginative ways to show values graphically or automate the formatting of a spreadsheet.

 Applying conditional formatting

You can find the conditional formatting options in the Styles group of the Home ribbon tab. The conditional formatting dropdown includes two sets of basic rules: Highlight Cells Rules and Top/Bottom Rules; three graphical forms of conditional formatting: Data Bars, Colour Scales and Icon Sets; and at the bottom of the list the more detailed options to create a New Rule from scratch, Clear Rules and Manage Rules.

Graphical formatting

As mentioned earlier, graphical conditional formats help make the information behind your data more obvious. This can be useful when you want to show a pattern within your data.

In the diagram below you can see that the total column has been duplicated and different types of graphical formatting applied to each column. Column G is using data bars, H a colour scale and I an icon set.

You can also apply multiple conditional formats to the same selection of cells using the Manage Rules option to manage individual or multiple conditional formats. Here all three formats have been applied to one column and then the conditional formatting, Manage Rules option was selected:

You can see each of our three rules and choose to Edit Rule or Delete Rule.

Although in many cases the default rules will achieve what you want, Edit Rules gives you much more control over the conditions that trigger the conditional format and to the formats themselves. As well as the formatting of each element, you can also set the criteria, choosing the value or cell reference to use as well as the type of value, for example, Number or Percent.

Advanced conditional formatting

So far, the conditional formatting that we have used has been based on fixed values or on simple references to a value held in a worksheet cell. It is also possible to base conditional formatting on Excel formulae which is useful when requiring formatting to be more specific and when none of the built-in options match your requirements.

From the conditional formatting dropdown list, select New Rule and from the Select a Rule Type: list choose Use a formula to determine which cells to format. You can then type in a formula which will determine your formatting criteria.

Alan Gurney is AAT Comment’s Excel tips writer.