Conditional Formatting
Formatting
Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on either:
- the value of the current cell
- the value of another cell in the current worksheet
- the value returned by a function (which may rely on the contents of the current cell and/or other cells in the current worksheet)
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.
Accessing the Conditional Formatting Menu
In order to define Conditional Formatting in Excel 2007, 2010 or 2013, 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:
- Highlight Cells Rules - Apply formatting to cells that satisfy a specific condition (eg. greater than, equal to, etc)
- Top/Bottom Rules - Apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (eg. above average, within top 10%, etc)
- Data Bars / Color Scales / Icon Sets - Apply formatting to all cells in the range, depending on their value in relation to one another.
These three types are described in turn below.
Highlight Cells Rules
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:
Example of Excel Conditional Formatting Using Cell References
| 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:
- 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
| 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.
Top/Bottom Rules
The Top / Bottom Rules of Excel Conditional Formatting are new to Excel 2007, but are straightforward to use. The conditions are applied to a range of cells, and formatting is applied only to cells that satisfy a statistical criteria, such as those cell with 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).
Data Bars, Color Scales & Icon Sets
The Data Bars, Color Scales and Icon Sets in Excel Conditional Formatting are also new to Excel 2007. As well as being useful for highlighting patterns in data, these formatting options can also look 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 applied to a selected range of cells with a single click of a button.
The 'More Rules...' Option
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.
Excel Conditional Formatting Using Formulae
In order to define more complex conditions that depend on the results of a formula, you can either:
Select the New Rule ... option, from the Conditional Formatting menu
OR
Select the More Rules ... option from sub-menu for any of the pre-defined Conditional Formatting types
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.
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.
Using Multiple Conditions
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. 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.