Excel Conditional Formatting Highlight Cells Rules

The Excel Conditional Formatting Highlight Cells Rules allow you to apply formatting to highlight cells that satisfy one or more specific conditions.

These conditions can relate to numeric values (e.g. greater than, between), dates (e.g. a date occurring...) or text values (e.g. text containing...). You can also use the Highlight Cells rules to highlight cells containing duplicate values.

Conditional Formatting Highlight Cells Rules Menu 2010

The Highlight Cells conditional formatting option is listed in the Excel Conditional Formatting menu, which is generally located in the 'Styles' group of the Home tab on the Excel ribbon (see rightabove).

When you select any of the first six Highlight Cells Rules ('Greater Than', 'Less Than, Between', 'Equal To', 'Text that Contains' or 'A Date Occurring'), a dialog box appears, which allows you to input a value or a cell reference, to compare each cell's value to.

If you enter a value to compare with, this is straightforward - the same condition is applied to all of the selected cells. However, if you insert a cell reference, the rules of absolute / relative references apply. These two cases are illustrated in the following examples:

Example 1 - Excel Conditional Formatting Using Absolute Values

 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

Imagine you are recording monthly results in the above example spreadsheet on the right, and you want to apply Excel Conditional Formatting to your cells, so that a monthly figure is highlighted if its value is greater than 60.

To do this:

  • Select the cells to be formatted (i.e. cells A2-D6).
  • Click on the Excel Conditional Formatting option from the Home tab of the Excel ribbon.
  • Conditional Formatting Greater Than Example in Excel 2010
    From within the 'Conditional Formatting' drop-down 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 value 60 into the first input field (entitled 'Format cells that are GREATER THAN:').
  • Select a pre-defined format from the drop-down list on the right of the dialog box and click OK.

These selections are shown in the following image of the Conditional Formatting 'Greater Than' dialog box:

Conditional Formatting Greater Than Dialog Box Showing Value Condition

 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

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


Example 2 - Excel Conditional Formatting Using Cell References

 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

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).


 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

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:

Conditional Formatting Greater Than Example in Excel 2010
  • 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 Select Cells Symbol or

      Select Cells Symbol 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:

Conditional Formatting Greater Than Dialog Box Showing Relative Reference

 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

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.


Common Error

 ABCD
1Month 1Month 2Month 3Month 4
220605020
360302030
430307070
580204010
650901050

If you use the mouse to select a cell for your condition, Excel automatically inserts the $ symbol (i.e. $A$2 in the above example). This is an absolute reference, which tells Excel not to adjust the reference for different cells in the selected range (i.e. to compare every cell in the selected range to the value in cell A2). This results in the spreadsheet formatting shown on the rightabove.

Clearly, this formatting is incorrect, in that it does not compare the value in each cell with the previous month's value.

For the required result, we need to change the selection of $A$2 to the relative reference, A2, by removing the $ signs.


Continue to How to Use the Excel Conditional Formatting Top/Bottom Rules  >>