Excel: Conditional formatting: Considering the fourth option
This article was adapted from MrExcel.com. Visit the MrExcel.com Web site for more tips and information.
Conditional formatting in Excel allows for three conditions. However, by setting up conditions correctly — from least to most restrictive — you can really allow for four conditions, when the default formatting of the cell is considered. For example, say you want to add color to your data like so:
| If cell value is | Fill with |
|---|---|
| Less than or equal to -25 | Red |
| Between 0 and -25 | Yellow |
| Between 0 and 25 | Green |
| Over 25 | Black |
This can be achieved by doing the following:
- Select your range of cells.
- Click Conditional Formatting on the Format menu.
- Under Condition 1, in the first two boxes, choose Cell Value Is and less than or equal to. Then type the value -25 in the third box.
- Now click the Format button, choose a red fill color from the Patterns tab, and then click OK.
- Click Add. Under Condition 2, choose between in the second box, type the values to 0 and -25 in the third and fourth boxes, and then choose a yellow fill format.
- Click Add again. Under Condition 3, choose between in the second box, type the values to 0 and 25 in the third and fourth boxes, and then choose a green fill format.
- Click OK to close the Conditional Formatting dialog box.
- Finally, while your range is still highlighted, choose a black fill from the color palette.
Your range is now pseudo-conditionally formatted to cover all four cases.
Popularity: 9% [?]
