Excel Conditional Formatting Highlight Cells Rules

Example 2 - Excel Conditional Formatting Using Cell References

For this example, we will again, use the spreadsheet of monthly results (shown on the rightabove).

In this case, we want to apply Excel Conditional Formatting, so that a monthly figure is highlighted if it is greater than the corresponding figure for the previous month.

For this, we need to apply formatting to the values in columns B-D (but not column A, as there is no previous month's data to compare the values in column A to).

Before entering the 'Conditional Formatting' menu, you need to select the cells to be formatted (i.e. cells B2-D6).

In the above example on the right, cell B2 is the active cell, although all cells in the range B2-D6 have been selected.

It is important to be aware of the current active cell when using references to define Conditional Formatting. This is explained below.

Select the Excel Conditional Formatting drop-down menu from the Home tab of the Excel ribbon. Within this menu:

• Select the Highlight Cells Rules option and from the secondary menu that appears, select the Greater Than ... option (see rightabove).

• The Conditional Formatting 'Greater Than' dialog box should then pop up (see below). Within this dialog box, enter the cell reference A2 in the first input field (entitled 'Format cells that are GREATER THAN:').

  This can be entered by either:

  • Typing =A2 directly into the input field. (Note: the '=' sign is necessary. If it is omitted, Excel will interpret the condition to be a comparison with the literal text string "A2")
  or
  • Using the mouse to click on the or

    symbol (depending on your version of Excel), then clicking on cell A2.

    (Note: if you use this method to select the cell A2, Excel will automatically insert an absolute reference - i.e. $A$2. You will need to remove the $ signs for this particular example)

• Select a pre-defined format from the drop-down list on the right of the box and click OK.

These selections are shown in the following image of the Conditional Formatting 'GREATER THAN' dialog box:

The resulting spreadsheet is shown on the rightabove. As required, all cells containing values that are greater than the corresponding previous monthly values have been highlighted.