Excel Conditional Formatting

Example of Excel Conditional Formatting Using Cell References

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

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 Conditional Formatting ... option from the Format drop-down menu at the top of your spreadsheet. Within this menu:

• Ensure that the default value of 'Cell Value Is' is selected in the first drop-down menu
• Select the logical test 'greater than' from the second drop-down menu
• Enter the cell reference A2 in the comparison value box. This can be entered by either:

• Click on the Format... button and define the format for the cells that satisfy your specified conditions.

These selections are shown in the following image of the 'Conditional Formatting ...' menu:

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

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.

Example of Excel Conditional Formatting Using a Formula

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.

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

As required, the rows that have totals exceeding 100 are highlighted.

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:

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.

Example of Excel Conditional Formatting Using Cell References

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

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:

• Select the Highlight Cells Rules option and from the secondary menu that appears, select the Greater Than ... option
• 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 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)

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

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

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.

Example of Excel Conditional Formatting Using a Formula

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.

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:

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

As required, the rows that have totals exceeding 100 are highlighted.

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.