Thread: Highlight data in excel

Every body 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 tne concept one step further by automating the process. It can apply special fonnats so that the appearance ofthe worksheet changes according to the information stored in it. For example, if tracking a bank balance it can turn the cells red ifthe account goes overdrawn or green ifthere'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 hqs made significant changes to how this latest version works, you'll fmd 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 fonnatting 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 fonnatting is to use a practical example. The most common use for the technique is to make the text in a cell change colour when its value drops below a certain figure. To do this, select the cell then choose Conditional Fonnat 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.



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 fonnat has been applied to one cell it's easy to copy it to others. Choose the formatted cell, click the Fonnat 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.

Amore 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 A 10, then click on the 10 to select this entire row and then choose Conditional Formatting from the format menu. When the dialogue box you want the formatting to change when A10 is below zero, type in =$A10<0. Next click the Fonnat button and choose the formatting - we clicked the

Patterns tab and chose a red background - before clicking OK twice. The fom1Ula needs some explanation. Like all formulas it begins with an equals sign, which serves merely to tell Excel that it's a formula. The dollar sign tells Excel that you want to specifY a particular cell, and that that cell should not be automatically changed, and is followed by the cell we want to use, A10. After that, it explains what condition excel should look'for: <0, or less than zero.