Excel Conditional Formatting Using Formulas
Related Pages:
Conditional Formatting Highlight Cells RulesConditional Formatting Top/Bottom Rules
Data Bars/Color Scales/Icon Sets
Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on the values of the data in the cell to be formatted or the data in other cells of your spreadsheet.
Excel provides several simple pre-defined Conditional Formatting rules (the Highlight Cells Rules, Top/Bottom Rules and Data Bars/Color Scales/Icon Sets). However, if the rules that you want to use are more complex, you can also use Excel formulas to define your own formatting conditions.
To enter an Excel Conditional Formatting formula, select the New Rule... option from the Excel Conditional Formatting menu. (which is generally located in the 'Styles' group of the Home tab on the Excel ribbon (see rightabove).
This opens up the 'New Formatting Rule' dialog box shown below:
Within the 'New Formatting Rule' dialog box select the option, 'Use a formula to determine which cells to format'. Excel then presents you with the options for creating a formula (shown in the above dialog box).
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, the conditional formatting is applied to the cell.
(Note that any number other than 0 is treated as the logical value TRUE and the numeric value 0 or any non-numeric values are treated as the logical value FALSE).
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
| 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 employees, as shown in the above spreadsheet on the right, 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 example spreadsheet on the rightabove, 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 menu will apply to cell A2.
However, depending on whether relative or absolute references are used in the formula, the cell references may or may not be adjusted for the other selected cells that the conditional formatting is to be applied to.
For the example spreadsheet, the formula to highlight rows that total 100 or more is:
=SUM($B2:$F2)>100
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 across the entire range of formatted cells.
- Relative References for the row number 2. This ensures that the formula's row number increases to 3, 4, 5 & 6 when it is evaluated for the formatting of rows 3-6.
| 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 formatted in red.