Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 18

A conditional format is formatting that Excel applies only to cells that meet the criteria you specify. For example, you can tell Excel to apply the formatting only if a cell’s value is greater or less than some specified amount, between two specified values, or equal to some value. You can also look for cells that contain specified text, dates that occur during a specified time frame, and more.

When you set up your conditional format, you can specify the font, border, and background pattern. This formatting helps to ensure that the cells that meet your criteria stand out from the other cells in the range. Here are the steps to follow:

1 Select the range you want to work with.Select just the data values you want to format. Don’t select any surrounding data.

2 Choose Home ⇒ Conditional Formatting.

3 Choose Highlight Cells Rules and then select the rule you want to use for the condition.You have six rules to play around with:Greater Than: Applies the conditional format to cells that have a value larger than a value that you specify.Less Than: Applies the conditional format to cells that have a value smaller than a value that you specify.Between: Applies the conditional format to cells that have a value that is greater than or equal to a minimum value that you specify and less than or equal to a maximum value that you specify.Equal To: Applies the conditional format to cells that have a value that is the same as a value that you specify.Text that Contains: Applies the conditional format to cells that include the text that you specify.A Date Occurring: Applies the conditional format to cells that have a date value that meets the condition that you specify (such as Yesterday, Last Week, or Next Month).(I cover a seventh rule — Duplicate Values — later in this chapter.) A dialog box appears, the name of which depends on the rule you click in Step 3. For example, Figure 1-1 shows the dialog box for the Greater Than rule.FIGURE 1-1: The Greater Than dialog box and some highlighted values.

4 Type the value to use for the condition.You can also click the up arrow button that appears to the right of the text box and select a worksheet cell that contains the value. Also, depending on the operator, you might need to specify two values.

5 Use the right drop-down list to select the formatting to apply to cells that match your condition.If you’re feeling creative, you can make up your own format by selecting the Custom Format command.

6 Click OK.Excel applies the formatting to cells that meet the condition you specified.

Excel enables you to specify multiple conditional formats for the same range. For example, you can set up one condition for cells that are greater than some value and a separate condition for cells that are less than some other value. You can apply unique formats to each condition. Keep the range selected and follow Steps 2 through 6 to configure the new condition.