Conditional formatting in Excel

The same method can be used to change the appearance of any single cell. As well as comparing a value to see if it is less than a designated figure, you can look for values that are greater than, equal to or between specified limits. There are also more complicated options such as looking for when the value is not equal to a certain figure, or not between a pair of figures.

Once a conditional format has been applied to one cell it’s easy to copy it to others. Choose the formatted cell, click the Format Painter icon in the toolbar ­ – it looks like a yellow paintbrush – ­ then select the cells to change. Alternatively you can copy the formatted cell, right-click the target cell, choose Paste Special then choose Formats from the dialogue box that appears.

Secret formulas
The big limitation when using this ‘Cell Value Is’ technique is that it can only format the single cell being used to make the comparison: it can’t, for example, change an entire row to red text if the value of one cell dips below zero.

A more powerful technique that enables you to format rows, columns and blocks of cells is to change the ‘Cell Value Is’ option in the Conditional Formatting dialogue box to ‘Formula Is’. This gives total control over which comparisons are made and which cells gets formatted.

For example, imagine that you want the entire bottom row of a budget spreadsheet to turn red if a single cell in that row – ­ the total ­ – is less than zero. If the last row in the table is row ten, and the cell with the value is A10, then click on the 10 to select this entire row and then choose Conditional Formatting from the format menu.

When the dialogue box appears, choose Formula Is from the first dropdown menu. A new option will appear to the right of this dropdown, allowing you to enter a formula. To tell Excel that you want the formatting to change when A10 is below zero, type in =$A10<0.

Next click the Format button and choose the formatting ­ – we clicked the Patterns tab and chose a red background ­ – before clicking OK twice. The formula needs some explanation. Like all formulas it begins with an equals sign, which serves merely to tell Excel that it’s a formula.