Conditional formatting: Considering the fourth option

Try Office 2010 In Excel 2010, conditional formatting has been improved.
Watch a video or try Office 2010!

Applies to
Microsoft Office Excel 2003

Mr. Excel logo 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:

  1. Select your range of cells.
  2. Click Conditional Formatting on the Format menu.
  3. 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.
  4. Now click the Format button, choose a red fill color from the Patterns tab, and then click OK.
  5. 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.
  6. 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.
  7. Click OK to close the Conditional Formatting dialog box.
  8. 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.

 
 
Applies to:
Excel 2003