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. Conditional Formatting on the Ribbon

  5. Point to Highlight Cell Rules or Top/Bottom Rules
  6. A menu appears with several formatting rules to choose from

  7. Select a conditional formatting rule
  8. A dialogue box appears asking you to set the values for the condition and the formatting you wish to apply
  9. 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

    Format cells greater than 2000

  10. Click Ok
  11. The formatting is applied to all cells in the range that met the condition.

    Conditional formatting applied

    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. Conditional formatting data bars
    6. Select a data bar, 2 or 3 color scale or icon set from the menu that appears
    7. The conditional formatting is applied to the selected cells.

      Data bars applied to a range

Follow us on

Facebook  Twitter  You Tube