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 is the ability to handle more than 3 conditions. There are also several new types of formatting.
In order to define Conditional Formatting in Excel 2007 / 2010, 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:
These three types are described in turn below.
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.
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 insert a cell reference, the rules of absolute / relative references apply. This is explained further by the following 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 |
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 explained below.
Select the Excel Conditional Formatting drop-down menu from the Styles tab of the main Home toolbar. Within this menu:
These selections are shown in the following image of the 'GREATER THAN' option 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 | 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 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 & Excel 2010, 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).
The Data Bars, Color Scales and Icon Sets in Excel Conditional Formatting are also new to Excel 2007 & Excel 2010. As well as being useful for highlighting patterns in data, these formatting options can also look extremely 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 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 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.
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.
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 formula evaluates to TRUE or a non-zero number, the condition is considered to be TRUE and the conditional formatting is applied to the cell.
If the formula 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.
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 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. However, cell references used in the inputted formula may or may not be adjusted for 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:
Note that, in this formula, the reference to the range $B2:$F2 uses:
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 are highlighted.
One big advantage of Conditional Formatting in Excel 2007 & 2010, 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 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. 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:
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.