ExcelFunctions.net

Search Site:

Related Page:

Basic Excel FormattingVersion 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 value of the current cell;
- The value of another cell in the current worksheet;
- The result of a formula (which may rely on the contents of the current cell and/or other cells in the current worksheet).

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.

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 from within the **Styles** group on the **Home** tab of the Excel ribbon. Clicking on this option will cause the Conditional Formatting drop-down menu to be displayed (see rightabove).

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:

- Apply formatting to cells that satisfy a specific condition (e.g. greater than, equal to, Duplicate Values, etc.);__H__ighlight Cells Rules- Apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (e.g. above average, within top 10%, etc.);__T__op/Bottom Rules- Apply formatting to all cells in the range, depending on their value in relation to one another.__D__ata Bars / Color__S__cales /__I__con Sets

In addition, there is the ** New rule...** option, that allows you to specify more complex rules, such as rules that depend on the result of a formula.

These different conditional formatting types are described in turn below.

Conditional Formatting Rules: |

Highlight Cells Rules |

Top/Bottom Rules |

Data Bars/Color Scales/Icon Sets |

Conditional Formatting Using Formulas |

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 specified format 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 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:

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

Imagine you are recording monthly results into 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. - From within the 'Conditional Formatting' drop-down menu, select the
option and from the secondary menu that appears, select the__H__ighlight Cells Rulesoption (see rightabove).__G__reater Than ... - 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:

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

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

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

Once again, imagine you are recording monthly results into the above example spreadsheet on the right.

In this case, you 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__.

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

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

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
option and from the secondary menu that appears, select the__H__ighlight Cells Rulesoption (see rightabove).__G__reater Than ... 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")

- Using the mouse to click on the symbol, then clicking 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)

- Typing

- 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:

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

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

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

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.

The Top / Bottom Rules of Excel Conditional Formatting are new to Excel 2007.

These rules work on 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, (e.g. Top Ten Items, Top Ten %, etc).

A dialog box will then pop up (see rightabove). This allows you to choose the formatting 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 (e.g. Top 5, Bottom 20, etc).

A | B | C | D | |
---|---|---|---|---|

1 | Month 1 | Month 2 | Month 3 | Month 4 |

2 | 20 | 60 | 50 | 20 |

3 | 60 | 30 | 20 | 30 |

4 | 30 | 30 | 70 | 70 |

5 | 80 | 20 | 40 | 10 |

6 | 50 | 90 | 10 | 50 |

The **To p 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.

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

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.

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.

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

- Select the
option, from the__N__ew Rule ...**Conditional Formatting**menu

OR

- Select the
option from sub-menu for any of the pre-defined Conditional Formatting types.__M__ore Rules ...

This causes the 'New Formatting Rule' dialog box to open up.

In order to define a conditional formatting formula, select the option, **'Use a formula to determine which cells to format'**. Excel then presents you with the options shown in the above 'New Formatting Rule' dialog box on the right.

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.

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

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Sales ($000's) | Jan | Feb | Mar | Apr | May |

2 | Ben | 8 | 20 | 16 | 40 | |

3 | Bill | 30 | 25 | 20 | 44 | |

4 | Bob | 15 | 12 | 24 | 30 | |

5 | John | 20 | 19 | 32 | 20 | |

6 | Ken | 40 | 30 | 32 | 25 |

Imagine you are recording monthly sales figures for 5 employees, as shown in the above spreadsheet on the right, 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.

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Sales ($000's) | Jan | Feb | Mar | Apr | May |

2 | Ben | 8 | 20 | 16 | 40 | |

3 | Bill | 30 | 25 | 20 | 44 | |

4 | Bob | 15 | 12 | 24 | 30 | |

5 | John | 20 | 19 | 32 | 20 | |

6 | Ken | 40 | 30 | 32 | 25 |

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.

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.

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Sales ($000's) | Jan | Feb | Mar | Apr | May |

2 | Ben | 8 | 20 | 16 | 40 | |

3 | Bill | 30 | 25 | 20 | 44 | |

4 | Bob | 15 | 12 | 24 | 30 | |

5 | John | 20 | 19 | 32 | 20 | |

6 | Ken | 40 | 30 | 32 | 25 |

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.

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 (see rightabove). 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 (e.g. 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 (which applies the test "Cell Value > 5" __before__ the test "Cell Value > 10") would NOT work, as required:

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