Excel > Excel 2003 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 the target, a budget being overspent or a date expiring.

You can set up to 3 conditions for a cell.

Apply Conditional Formatting

1. Select the cells in the range you wish to format
2. Click Format > Conditional Formatting
3. The Conditional Formatting dialogue box appears



4. Complete the fields for the condition to be tested and formatting to be applied

The first drop menu enables you to select a test method. A choice between testing the cell value or writing a formula.

The second drop menu enables you to select a test function such as greater than, less than or between.

Enter the value, cell reference, or formula to be tested in the next field.

Click the Format button and select the formatting requirements from the Format cells dialogue box that appears.

The example below will format all cells in the range that have a value greater than 2000 in Orange.



5. Click Ok

The conditional formatting is applied.

Add a new conditional format

1. Select the cells that you wish to create the new conditional format to
2. Click Format > Conditional Formatting
3. Click the Add button in the Conditional Formatting dialogue box
4. Complete the fields for the condition
5. Click Ok

Delete a conditional format

1. Select the cells that you wish to unformat
2. Click Format > Conditional Formatting
3. Click the Delete button

The Delete Conditional Format dialogue box is displayed



4. Select the check boxes for the condition(s) to delete
5. Click Ok
6. Click Ok again

Custom number formatting Vlookup

Guides