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. Conditional formatting

  5. Complete the fields for the condition to be tested and formatting to be applied
  6. 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.

    New conditional formatting rule

  7. Click Ok
  8. 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. Add condition
  5. Complete the fields for the condition
  6. 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
  4. The Delete Conditional Format dialogue box is displayed

    Delete conditional Format dialogue box

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

Follow us on

Facebook  Twitter  You Tube