Conditional Formatting in Excel 2003

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.


Accessing the Excel 2003 Conditional Formatting Menu

In order to define Conditional Formatting in Excel 2003:

Conditional Formatting Main Menu 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)

OR

Formula Is (for a condition that depends on a formula).


These two options are discussed individually below.


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' dialog box.

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

Example of Excel Conditional Formatting Using Cell References

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

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



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

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:

  • Ensure that the first drop-down menu displays 'Cell Value Is';
  • From the second drop-down menu, select the logical test 'greater than';
  • 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")
    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).
  • Click on the Format... button and define the format for the cells that satisfy your specified conditions.

These selections are shown in the 'Conditional Formatting' dialog box below:

Conditional Formatting Relative Ref Example

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

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

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

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.


Excel Conditional Formatting Using Formulas

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.

Conditional Formatting Formula Options Excel 2003

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.

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


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

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:

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



Using Multiple Conditions

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.

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

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.