The previous section described how to apply conditional formatting to cells. In the dialog box that appears, you can choose between the following types of formatting rules:
Format all cells based on their values
This type of conditional formatting rule actually does not utilize a condition at all. It rather reformats all of the selected cells – based on the values they contain.
For example, if you choose a 2-color scale from red to green, the lowest value will be highlighted in red color, and the highest value in green color. The colors for the values in-between will be calculated automatically. The result is a color gradient like e.g. the following:
There are several sub-types available for this type of formatting rule. They can be selected using the Format style control. This control contains the following entries:
▪2-color scale
| As described in the example above. |
▪3-color scale
| Same as the 2-color scale, with an additional option to specify the color of the medium value. |
▪Data bars
| Displays a bar in the background of each cell, representing the relative size of the value – similar to a bar chart: |
 |
▪Icons sets
| Displays an icon in each cell, indicating the size of the respective value – for example a red signal light for the lower third of the values, a yellow one for the medium third and a green light for the upper third: |
 |
Format only cells that contain ...
This type of rule reformats only those cells within the current selection that meet the specified condition.
Proceed as follows: First, specify the desired condition, using the controls and input boxes in the dialog box.
Then, click on the Format button and specify the formatting options to be applied for all cells that meet the condition.
An example can be found in the previous section – where we created a formatting rule that paints the cell content in red color if it is larger than 1000.
Format only upper and lower values
This type of rule reformats only those cells that contain the highest or lowest values within the current selection.
First, specify which values to reformat – for example, the top 3 values or the top 10% of the values.
Then, click on the Format button and specify the formatting options to be applied for the corresponding cells.
Format values above or below average
This type of rule reformats only those cells that contain values above or below the average of the current selection.
First, specify which values to reformat – for example, all values above the average.
Then, click on the Format button and specify the formatting options to be applied for the corresponding cells.
Format unique or double values
This type of rule reformats all unique values (or double values) within the currently selected cells.
First, specify which values to reformat:
▪all unique values (values that occur just once)
▪or all duplicate values (values that occur twice or more often)
Then, click on the Format button and specify the formatting options to be applied for the corresponding cells.
Use a formula to determine which cells to format
This type of rule reformats only those cells within the selection where the specified formula returns TRUE.
First, enter the desired formula in the dialog box. Note that only formulas that return a logical value (i.e. TRUE or FALSE) are allowed. See also notes below.
Then, click on the Format button and specify the formatting options to be applied for the corresponding cells.
Some notes:
▪Creating suitable formulas
| You can enter any kind of formula. You only have to ensure that the result of the formula is a logical value (i.e. TRUE or FALSE). |
| Examples: |
| If you enter the formula "SUM($A$1:$C$3) > 42", the conditional format will be applied if the sum of the cells A1:C3 is greater than 42. |
| If you enter the formula "ISEVEN(ROW())", the conditional format will be applied if the current cell is located in a row with an even row number. |
▪Using absolute and relative cell addresses
| Notice that not only absolute cell references (as above) can be used, but also relative cell references: These are treated as follows: |
| Absolute cell addresses like $A$1 always refer to the cell A1. |
| Relative cell addresses like A1 refer to the cell in the upper left corner of the selection. That means: |
| If you have not selected a cell range, A1 refers to the current cell. |
| If you have selected a cell range, A1 refers to the cell in the upper left corner of that range, A2 to the cell below it, etc. |