ExcelFunctions.net Logo

Excel Conditional Formatting

Home » Basic-Excel » Excel-Conditional-Formatting



Search this site:
Custom Search

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 value returned by a function (which may rely on the contents of the current cell and/or other cells in the current worksheet)

This feature is particularly useful when you have a series of cells containing formulas, and you want to highlight when a formula value changes to be within a specific range.

This page describes Conditional Formatting in both Excel 2003 and Excel 2007

Conditional Formatting in Excel 2003

Basics

In order to define Excel Conditional Formatting, first select the cell(s) to be formatted and then, in Excel 2003, select the Format drop-down menu from the top of your Excel window. From this menu, select the option Conditional Formatting ...

Within this option, is a series of boxes, to assist you with defining your conditions. The first box is a drop-down menu, from which you can select either:

Cell Value Is (for a condition that depends on the value of a cell)
OR
Formula Is (for a condition that depends on a formula)
Conditional Formatting Main Menu Excel 2003
After defining your condition, click on the Format... button to define the formats that should be used if your condition evaluates to TRUE.

Within Excel 2003, you can set up to 3 different conditions, followed by 3 different formats. It is important to understand that Excel tests the conditions in the order that they are input, so if the first condition is satisfied, the formatting linked to this condition will be applied to the cell, whether or not the further conditions are satisfied. A lack of appreciation for this can result in unexpected results in your formatting.


Excel Conditional Formatting Based on a Cell's Value

In order to define a condition that depends on one or more cells' values, select the Cell Value Is option from the first drop-down menu in the 'Conditional Formatting ...' menu.

The second drop-down menu then provides you with a selection of logical tests such as 'between', 'equal to', 'greater than', etc.
Conditional Formatting Cell Value Is Options Excel 2003
Select the condition that you want to apply to determine the formatting of your cell(s). The appropriate number of boxes will then be displayed on the right side of the Excel Conditional Formatting selection menu. i.e. if you select either of the logical tests 'between' or 'not between', two boxes will be displayed, for the upper and lower comparison values. For all other logical tests, just one box will be displayed.

The values for the logical tests can be entered as numbers or as cell references. If you enter numbers, this is straightforward - the same condition is applied to all of the selected cells. However, if you use of cell references, the rules of absolute / relative references apply. This is explained further by the following example:

Example of Excel Conditional Formatting Using Cell References

  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 80 80
5 80 20 40 10
6 50 90 10 50
Imagine you are recording monthly results in the example spreadsheet on the right, and you want to apply Excel Conditional Formatting to your cells, so that a monthly figure is highlighted in red 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 80 80
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 example on the right, cell B2 is the primary selected cell, although cells, B3-B6 and C2-D6 have also been selected. It is important to be aware of the primary selected cell when using references to define Excel Conditional Formatting - as we will see shortly!


From the Insert drop-down menu, select the Conditional Formatting ... option and within this,
  1. Ensure that the default value of 'Cell Value Is' is selected in the first drop-down menu

  2. Select the logical test 'greater than' from the second drop-down menu

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

    (Warning, if you use this method to select the cell A2, this will automatically insert the $ symbols into your cell reference - i.e. $A$2. This defines A2 to be an absolute reference, which will give a different result to that required in this example - see below for more details)

  4. Click on the Format... button and define the format for the cells that satisfy your specified conditions. (Note that there are some formatting options, such as font name and font size that cannot be altered through Excel Conditional Formatting).
These selections are shown in the following image of the 'Conditional Formatting ...' menu:
Conditional Formatting Relative Ref Example
  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 80 80
5 80 20 40 10
6 50 90 10 50

The resulting spreadsheet is shown on the right. As required, all cells containing values that are greater than the corresponding previous monthly values have been highlighted by formatting the text in bold and coloured red.


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 80 80
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 right.

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 the reference in the Excel Conditional Formatting formula to adjust for each of the selected cells. i.e. we wish for cell B2 to be compared to cell A2, cell C2 to be compared to cell B2, etc and for cell B3 to be compared to cell A3, etc.

This is done by changing the selection of $A$2 to the relative reference, A2, by removing the $ signs in the reference.


Excel Conditional Formatting Using Formulae

In order to define more complex conditions that depend on the results of a formulae, within Excel 2003, select the Formula Is option from the first drop-down menu in the 'Conditional Formatting ...' menu.

You are then provided with a single box, in which you can insert your formula.
Conditional Formatting Formula Options Excel 2003

The formula that you use for your condition can be built up using any of Excel's built-in functions or operators. If the function evaluates to TRUE or a non-zero number, the condition is considered to be TRUE and your formatting is applied to the cell. If your function evaluates to FALSE, 0 or a non-number, the condition is treated as being FALSE and so the formatting is not applied to the cell(s)

When entering into the 'Conditional Formatting ...' menu, your formula must always be preceded by the = sign. This is shown in the example below.

Example of Excel Conditional Formatting Using a Formula

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

  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 right, cell A2 is the primary selected cell, although all cells in rows 2 - 6 are selected.

Because A2 is the primary selected cell, the formula entered into the 'Conditional Formatting ...' menu will apply to the cell A2. Any cell references used in this formula may or may not be adjusted when applied to the other selected cells, depending on whether relative or absolute references are used in the formula.

The formula to highlight rows in the above spreadsheet that total 100 or more is shown below:
Conditional Formatting Formula in Excel 2003
Note that, in the above formula, the reference to the range $B2:$F2 uses:
  • Absolute references for the columns B-F - this ensures that the column references remain constant when applied to the formatting of cells in ALL columns

  • 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 example spreadsheet on the right.

As required, the rows that have totals exceeding 100 (representing sales of over $100,000) are highlighted.


Using Multiple Conditions

In Excel 2003, you can specify up to 3 conditions, each of which has a different format specification. To specify a second or third condition, click on the Add >> button in the 'Conditional Formatting ...' menu. This adds a further set of options that can be used to specifiy your second or third condition.
Conditional Formatting with 2 Conditions in Excel 2003
Common Error

When more than one condition is used for Excel Conditional Formatting, it is important to understand that the conditions are tested in order. If the first condition is satisfied, then the remaining conditions are ignored.

For example, 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 Example in Excel 2003
This example could be made to work as required by positioning the condition "Cell Value Is greater than 10" first and placing the condition "Cell Value Is greater than 5" second.


Conditional Formatting in Excel 2007

Excel Conditional Formatting has been enhanced significantly in Microsoft Excel 2007. One major benefit is that more than 3 conditions can be used in Excel 2007. There are also several new types of formatting. However, the new interface in Excel 2007 can, at first sight, appear to be more complicated than in older versions of Excel.

Basics

Conditional Formatting Main Menu Excel 2007
In order to define Conditional Formatting, first select the cell(s) to be formatted and then select the Conditional Formatting option on the Styles tab of the main Home toolbar. This will cause the drop-down menu (shown on the right) 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:
  • Highlight Cells Rules - Apply formatting to cells that satisfy a specific condition (eg. greater than, equal to, etc

  • Top/Bottom Rules - Apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (eg. above average, within top 10%, etc)

  • Data Bars / Color Scales / Icon Sets - Apply formatting to all cells in the range, depending on their value in relation to one another

Conditional Formatting Highlight Cells Rules Menu 2007

Highlight Cells Rules

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 be TRUE, a selected format type is applied.

Therefore, in this type of Excel Conditional Formatting, each cell in the selected range is evaluated independently of the other cells in the range.

If you select one of the Highlight Cells Rules, 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 use of cell references, the rules of absolute / relative references apply. This is explained further by the following example:
Example of Excel Conditional Formatting Using Cell References

  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 80 80
5 80 20 40 10
6 50 90 10 50
Imagine you are recording monthly results in the 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).


  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 80 80
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 example on the right, cell B2 is the primary selected cell, although cells, B3-B6 and C2-D6 have also been selected. It is important to be aware of the primary selected cell when using references to define Excel Conditional Formatting - as we will see shortly!

Conditional Formatting Greater Than Example in Excel 2007
Select the Excel Conditional Formatting drop-down menu from the Styles tab of the main Home toolbar. Within this:
  1. Select the Highlight Cells Rules option and from the secondary menu that appears, select the Greater Than ... option

  2. A box will appear for you to enter your comparison value or cell reference. Enter the cell reference A2 into this box, 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

    (Warning, if you use this method to select the cell A2, this will automatically insert the $ symbols into your cell reference - i.e. $A$2. This defines A2 to be an absolute reference, which will give a different result to that required in this example - see below for more details)

  3. 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 2007
  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 80 80
5 80 20 40 10
6 50 90 10 50

The resulting spreadsheet is shown on the right. 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 80 80
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 right.

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 the reference in the Excel Conditional Formatting formula to adjust for each of the selected cells. i.e. we wish for cell B2 to be compared to cell A2, cell C2 to be compared to cell B2, etc and for cell B3 to be compared to cell A3, etc.

This is done by changing the selection of $A$2 to the relative reference, A2, by removing the $ signs in the reference.

Conditional Formatting Top/Bottom Rules Menu 2007

Top/Bottom Rules

The Top / Bottom Rules of Excel Conditional Formatting are new to Excel 2007, but are straightforward to use. The conditions relate to a whole range of cells, and formatting is applied only to cells that satisfy a statistical criteria, such as having the top ten values, having values greater than average, 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)
Conditional Formatting Data Bars Menu 2007

Data Bars, Color Scales & Icon Sets

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 extremely professional when applied to your spreadsheet.
Data Bars Color Scales & Icon Sets Examples in Excel 2007


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 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 colour schemes and symbols used in the formatting are selected from within the Data Bars, Color Scales and Icon Sets menus and are literally applied to your selected range of cells with a single click of a button!

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'. Using formulae to define Excel Conditional Formatting is discussed further in the following section.


Excel Conditional Formatting Using Formulae

In order to define more complex conditions that depend on the results of a formula, you can either:

Conditional Formatting New Formatting Rule Menu in Excel 2007
Select the New Rule ... option, from the Conditional Formatting menu
OR
Select the More Rules ... option from sub-menu for any of the pre-defined Conditional Formatting types

This causes a New Formatting Rule options box 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 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 function evaluates to TRUE or a non-zero number, the condition is considered to be TRUE and your formatting is applied to the cell. If your function evaluates to FALSE, 0 or a non-number, the condition is treated as being FALSE and so the formatting is not applied to the cell(s)

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

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

  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 right, cell A2 is the primary selected cell, although all cells in rows 2 - 6 are selected.

Because A2 is the primary selected cell, the formula entered into the Conditional Formatting menu will apply to the cell A2. Any cell references used in this formula may or may not be adjusted when applied to the other selected cells, depending on whether relative or absolute references are used in the formula.

Conditional Formatting Formula in Excel 2007
The formula to highlight rows in the above spreadsheet 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 when applied to the formatting of cells in ALL columns

  • 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 example spreadsheet on the right.

As required, the rows that have totals exceeding 100 (representing sales of over $100,000) are highlighted.


Conditional Formatting Manage Rules Option  in Excel 2007

Using Multiple Conditions

One big advantage of Conditional Formatting in Excel 2007, is that you can specify more than 3 conditions, each having different format specifications. 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 set so far. Note that you can opt to view either the rules for the current selected range of cells, or the rules for the whole spreadsheet.

It is important to understand that the order that the Excel Conditional Formatting rules are listed is important. 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. If the first condition is satisfied, then the remaining conditions are ignored.

For example, 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 2007
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.






Disclaimer Privacy Policy

Copyright © 2008-2010 ExcelFunctions.net