Excel > Excel 2007 Intermediate > Formatting a Worksheet

Conditional Formatting

Conditional formatting applies formatting that depends on the contents of a cell. It allows you to set a condition that if met, will automatically format the cell.

It can be used to great effect to emphasise particular values on a worksheet such as monthly sales meeting a target, a budget being overspent or a date expiring by making the data more visual.

Highlight Cell Rules and Top/Bottom Rules

You can highlight cells in a range that meet specific conditions.

1. Select the range that you wish to format
2. Click the Home tab on the Ribbon
3. Click Conditional Formatting from the Styles group
4. Point to Highlight Cell Rules or Top/Bottom Rules

A menu appears with several formatting rules to choose from

5. Select a conditional formatting rule
6. A dialogue box appears asking you to set the values for the condition and the formatting you wish to apply

Enter the value or a cell reference for the condition in the first field

Then select the list arrow and choose the formatting you wish to apply. Click Custom Format to open the Format Cells dialogue box and a more extensive list of formatting options

The example below shows the Greater Than formatting rule selected for values over 2,000, which will be formatted with a Light Red Fill and Dark Red Text



7. Click Ok

The formatting is applied to all cells in the range that met the condition.



If any of the values in the range changed the conditional formatting would automatically update.

Data Bars, Color Scales and Icon Sets

Data bars, color scales and icon sets display variations in the values of cells in a range.

Data Bars: Coloured bars appear in the cells. The longer the bar the higher the value in that cell.

Color Scales: Cells are shaded with a colour gradient dependent upon the cells value relative to the other cells in that range.

Icon sets: Icons appear in the cells based on the cells value.

1. Select the cells that you wish to format
2. Click the Home tab on the Ribbon
3. Click Conditional Formatting from the Styles group
4. Point to Data Bars, Color scales or Icon sets
5. Select a data bar, 2 or 3 color scale or icon set from the menu that appears

The conditional formatting is applied to the selected cells.



Custom number formatting Managing Conditional Formatting

Email Stumble

Guides