Conditional Formatting in Excel 2003

Example of Excel Conditional Formatting Using Cell References

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

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

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.

Example of Excel Conditional Formatting Using a Formula

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.

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:

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

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.