Using Conditional Formatting to Flag Entries

You can apply conditional formatting to a cell to make Excel display a value in a unique way if the value falls within a certain range. For example, you can apply a conditional format telling Excel that if this value falls below 0, it should shade the cell red and place a big, thick border around it to alert you. To apply conditional formatting, follow these steps:

1. Select the cell(s) that contains the formula(s) or value(s) you want to format.

2. Open the Format menu and choose Conditional Formatting . The Conditional Formatting dialog box appears, as shown in Figure 7.9. In steps 35, you use the dialog box to create a condition that triggers Excel to apply the formatting; for example, you might construct a condition such as Cell Value Is between 100,000 and 150,000 .

   Figure 7.9. Have Excel apply formatting to a cell based on the value in that cell.

   

3. Open the drop-down list (on the far left), and choose the desired type of conditional formatting:

   Cell Value Is to base the conditional format on a value inside the cell. The value can be the result of a formula.

   Formula Is to base the conditional format on a TRUE or FALSE entry in the cell. Rather than produce a value as a result, some formulas and functions generate the result TRUE or FALSE.

4. Open the operator list, and choose the desired operator, such as between or greater than .

5. In the text box(es) on the right, type the value or values that trigger Excel to apply the format.

6. If you need to specify another condition, click the Add>> button and repeat steps 35 to specify another condition.

7. Click the Format button. A streamlined version of the Format Cells dialog box appears, providing you with options for specifying font, border, and shading formats.

8. Use the Format Cells dialog box to select the desired formatting that Excel will apply to the cells if the specified condition is met.

9. Click OK to return to the Conditional Formatting dialog box.

10. Click OK to apply the conditional formatting and return to your worksheet.