Conditional formatting in Excel

Everybody who uses spreadsheets develops their own ways to make them easier to understand: whether it’s something as simple as using bold fonts for labels and headings, or sophisticated schemes involving colour-coding, lines and borders to make the important areas of a worksheet stand out from the background.

Conditional formatting takes the concept one step further by automating the process. It can apply special formats so that the appearance of the worksheet changes according to the information stored in it. For example, if tracking a bank balance it can turn the cells red if the account goes overdrawn or green if there’s money available.

In this feature we’ll explain how to use conditional formatting in Microsoft Excel 2003. The features we’ll describe are also available in Excel 2007, but as Microsoft has made significant changes to how this latest version works, you’ll find that the conditional formatting options are presented in a different way.

Colours and shapes
Before getting started, it’s important to bear in mind what conditional formatting can and cannot do. An obvious use for conditional formatting is to change the colour of text, but it can do so much more: it’s also possible to change the font style by making it bold, italic, underlined or struck out. In addition, borders, colours and patterns can be applied to cells.

On the other hand, there are a few things that can’t be changed this way. Conditional formatting can’t change which font is used, for instance, or the number of decimal places on display. In addition, it can’t change the height or width of a cell. Because of these limitations, it’s generally best to add any conditional formatting that you want on a worksheet only after you’re happy with its basic design and layout.

Back to basics
The easiest way to explain conditional formatting is to use a practical example. The most common use for the technique is to make the text in a cell change col our when its value drops below a certain figure. To do this, select the cell then choose Conditional Format from the Format menu.

When the dialogue box appears, choose “Cell Value Is” in the first option, “less than” in the second” and then type a number in the third. Click the Format button, choose how you would like the text to be modified, then click OK and OK again. For an illustrated step-by-step guide to the process, click the button at the end of this feature.