In Excel 2003, Conditional Formatting can be used to alter the formatting of an Excel cell based on either:
This page describes Conditional Formatting in Excel 2003. However, the interface and functionality of conditional formatting changed significantly in version 2007 of Excel, so if you have a more recent version of Excel, see the page on conditional formatting in current versions of Excel.
In order to define Conditional Formatting in Excel 2003:
The first box in the 'Conditional Formatting' dialog 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)
ORFormula Is (for a condition that depends on a formula).
These two options are discussed individually below.
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' dialog box.
The second drop-down menu then provides you with a selection of logical tests such as 'between', 'equal to', 'greater than', etc.
Select the type of condition that you want to apply to determine the formatting of your cell(s) and type in your criteria.
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:
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 above example spreadsheet on the right, and you want use Excel Conditional Formatting to highlight in red, any monthly figure that 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 (cells B2-D6 in this example).
In the above example spreadsheet 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 active cell when using references to define Excel Conditional Formatting - as explained below.
Select the Conditional Formatting ... option from the Format drop-down menu at the top of your spreadsheet. This will cause the 'Conditional Formatting' dialog box to open. Within this dialog box:
Enter the cell reference A2 into the third input 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")Using the mouse to click on the 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).
These selections are shown in the 'Conditional Formatting' dialog box below:
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 aboveon 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 colouring it 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 that is to be entered into the third field of the dialog box, 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 aboveon 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.
In order to define more complex conditions that depend on the results of a formula, select the Formula Is option from the first drop-down menu in the 'Conditional Formatting' dialog box.
You are then provided with a single box, in which you can insert your formula.
The formula that you use to define 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 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 a formula into the 'Conditional Formatting' dialog box, the formula 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 members of a sales team, 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 above example spreadsheet on the right, 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' dialog box 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 shown below:
Note that, in the above 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 above example spreadsheet on the right.
As required, the rows that have totals exceeding 100 are highlighted.
In Excel 2003, you can specify up to 3 different conditions, and specify a different format for each of these. To specify a second or third condition, click on the Add >> button in the 'Conditional Formatting' dialog box. This adds a further set of input fields that can be used to specify further conditions.
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 orange and cells having a value greater than 5 (but less than 10) be coloured in red, the following definition would NOT work, as required for cells having values greater than 10. This is because the conditional formatting ignores the second condition once the first condition is satisfied.
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.