Conditional Formatting in Excel

Version of Excel
This page describes conditional formatting in current versions of Excel (2007 and later).

If you have Excel 2003, see the separate page on Conditional Formatting in Excel 2003.

Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on either:

The interface and functionality of conditional formatting changed significantly in version 2007 of Excel. One of the major improvements was the ability to handle more than 3 conditions. There are also several new types of formatting, which are discussed below.


Accessing the Conditional Formatting Menu


Conditional Formatting Main Menu Excel 2010

Before entering the Conditional Formatting menu, you need to first select the cell(s) that you wish to apply the formatting to.

Next, select the Conditional Formatting option on the Styles tab of the main Home toolbar. This will cause the drop-down menu (shown on the rightabove) to appear.

This menu allows you to select the type of Excel Conditional Formatting that you want to apply to your cell(s). This can be either:

In addition, there is the New rule... option, that allows you to specify further rules, including rules that depend on the result of a formula.

These different conditional formatting types are described in turn below.

Index:
Go To Highlight Cells Rules
Go To Top/Bottom Rules
Go To Data Bars/Color Scales/Icon Sets
Go To Conditional Formatting Using Formulas

Highlight Cells Rules

Conditional Formatting Highlight Cells Rules Menu 2010

The Highlight Cells Rules option allows you to select a simple condition to be applied to each cell in the selected range.

For each cell in which the condition evaluates to TRUE, a selected format type is applied.

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 box appears, which allows you to input a value or a cell reference, to compare the current 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. This is explained further by the following example:

Example of Excel Conditional Formatting Using Cell References

 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 it is greater than the corresponding figure for the previous month.

In this example, you 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 cells, B3-B6 and C2-D6 have also been selected. It is important to be aware of the current active cell when using references to define Excel Conditional Formatting, as explained below.


Select the Excel Conditional Formatting drop-down menu from the Styles tab of the main Home toolbar. 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
  • Enter the cell reference A2 in the comparison value box. This can be entered by either:
  • typing =A2 (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 symbol, then click on the 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.

These selections are shown in the following image of the 'GREATER THAN' option box:

Conditional Formatting Greater Than Eg with Relative References in Excel 2010

 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.

Top/Bottom Rules

Conditional Formatting Top/Bottom Rules Menu 2010

The Top / Bottom Rules of Excel Conditional Formatting are new to Excel 2007, but are straightforward to use. The conditions are applied to a range of cells, and formatting is applied only to cells that satisfy a statistical criteria, such having a greater than average value, etc.

Therefore, the formatting of any one cell in a selected range is dependent on the values of the other cells in the range.

In order to apply this formatting, select one of the options from the Top/Bottom Rules menu, (ie. Top Ten Items, Top Ten %, etc).

An options box will then appear that allows you to choose a format to apply to cells satisfying the chosen criteria.

If you have selected a 'Top Ten' or 'Bottom Ten' option, there will also be the option of changing the value of '10' to a different value (eg. Top 5, Bottom 20, etc).

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

The Top 10%... formatting option has been applied to cells A2 - D5 of the above spreadsheet on the right.

It can be seen that, of the 20 cells in the range A2 - D5, the top two cells have been formatted in pale red.



Data Bars, Color Scales & Icon Sets

Conditional Formatting Data Bars Menu 2010

The Data Bars, Color Scales and Icon Sets in Excel Conditional Formatting are also new to Excel 2007.

As well as being useful for highlighting patterns in data, these formatting options can also look professional when applied to your spreadsheet.

All three of the options, Data Bars, Color Scales and Icon Sets work on a whole set of data (rather than on individual cells).

Data Bars Color Scales & Icon Sets Examples in Excel 2010

These options evaluate the selected range of cells and apply colour or symbols to the cells, depending on each cell's value relative to the other cells in the range.

The above table on the left shows examples of Data Bars, Color Scales and Icon Sets applied to the values 1 - 10 in a range of spreadsheet cells.


The 'More Rules...' Option

All of the Excel Conditional Formatting types can be refined by selecting the More Rules ... option, at the bottom of any of the 'type' menus. Within this option, the conditions can be 'tweaked', and more formatting colours and styles are available.

Also within this menu, is the option to 'Use a formula to determine which cells to format'. This is discussed further in the following section.


Excel Conditional Formatting Using a Formula

If you want to use a formula to define more complex conditions for Excel Conditional Formatting, you can either:

Conditional Formatting New Formatting Rule Menu in Excel 2010

OR

This causes a New Formatting Rule window to open up.

In order to define a formula for your conditional formatting, select the option, 'Use a formula to determine which cells to format'. Excel then presents you with the dialog box shown on the rightabove.

The formula that you use for your condition can be built up using any of Excel's built-in functions or operators. If the formula evaluates to TRUE, the conditional formatting is applied to the cell and if the formula evaluates to FALSE the formatting is not applied to the cell.

(Note that any number other than 0 is treated as the logical value TRUE and the numeric value 0 or any non-numeric values are treated as the logical value FALSE).

When entering your formula, it must always be preceded by the = sign. This is shown in the example below.


Example of Excel Conditional Formatting Using a Formula

 ABCDEF
1Sales ($000's)JanFebMarAprMay
2Ben8201640 
3Bill30252044 
4Bob15122430 
5John20193220 
6Ken40303225 

Imagine you are recording monthly sales figures for 5 employees, as shown in the aboveadjacent spreadsheet, and you wish to highlight each row that has recorded total sales figures of more than $100,000.

In this example, we wish to apply Excel Conditional Formatting to each entire row, based on the sum of the values in the row.


 ABCDEF
1Sales ($000's)JanFebMarAprMay
2Ben8201640 
3Bill30252044 
4Bob15122430 
5John20193220 
6Ken40303225 

Initially we need to select the rows to be formatted. Note that in the example spreadsheet on the rightabove, cell A2 is the active cell, although all cells in rows 2 - 6 are selected.

Because A2 is the active cell, the formula entered into the Conditional Formatting menu will apply to the cell A2.

However, depending on whether relative or absolute references are used in the formula, the cell references may or may not be adjusted for the other selected cells that the conditional formatting is to be applied to.


Conditional Formatting Formula in Excel 2010

For the example spreadsheet, the formula to highlight rows that total 100 or more is:

=SUM($B2:$F2)>100

Note that, in this formula, the reference to the range $B2:$F2 uses:

  • Absolute References for the columns B-F. This ensures that the column references remain constant.
  • Relative References for the row number, 2. This ensures that the row number increases to 3, 4, 5 & 6 when the function is applied to the formatting of rows 3-6.

 ABCDEF
1Sales ($000's)JanFebMarAprMay
2Ben8201640 
3Bill30252044 
4Bob15122430 
5John20193220 
6Ken40303225 

The results of the above Excel Conditional Formatting definition are shown in the above example spreadsheet on the right.

As required, the rows that have totals exceeding 100 are formatted in red.



Using Multiple Conditions

Conditional Formatting Manage Rules Option  in Excel 2010

One big advantage of Conditional Formatting in recent versions of Excel (2007 and later), is that you can specify more than three conditions, and apply different formatting to each of these. After you have specified your first condition, you can specify further conditions by simply repeating the process for adding a condition.

If you want to view or edit the conditions that have been set so far, select the Manage Rules... option from the Excel Conditional Formatting menu. This shows a list of all rules that have been defined so far.

It is important to understand that the order that the Excel Conditional Formatting rules are listed does make a difference. The condition that is positioned at the top of the list is tested first, and then the next one down, etc.

This ordering process is particularly important when you have conditions that overlap (eg. A1>10, A1>5). This is illustrated in the 'Common Error' example below.


Common Error

When more than one condition is used for Excel Conditional Formatting, it is important to understand that the conditions are tested in the order that they appear in the 'Rules Manager' window.

Therefore, if we wanted to specify that cells having a value greater than 10 be coloured in red and cells having a value greater than 5 be coloured in orange, the following definition would NOT work, as required:

Conditional Formatting with 2 Conditions Error in Excel 2010

This example could be made to work as required by positioning the condition "Cell Value > 10" first and placing the condition "Cell Value > 5" second.