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

When analyzing worksheet data, looking for items that stand out from the norm is often useful. For example, you might want to know which sales reps sold the most last year, or which departments had the lowest gross margins. To quickly and easily view the extreme values in a range, you can apply a conditional format to the top or bottom values of that range.

You can apply such a format by setting up a top/bottom rule, in which Excel applies a conditional format to those items that are at the top or bottom of a range of values. For the top or bottom values, you can specify a number, such as the top 5 or 10, or a percentage, such as the bottom 20 percent. Here’s how it works:

1 Select the range you want to work with.

2 Choose Home ⇒ Conditional Formatting.

3 Choose Top/Bottom Rules and then select the type of rule you want to create.You have six rules to mess with:Top 10 Items: Applies the conditional format to cells that rank in the top X, where X is a number that you specify (the default is 10).Top 10 %: Applies the conditional format to cells that rank in the top X %, where X is a number that you specify (the default is 10).Bottom 10 Items: Applies the conditional format to cells that rank in the bottom X, where X is a number that you specify (the default is 10).Bottom 10 %: Applies the conditional format to cells that rank in the bottom X %, where X is a number that you specify (the default is 10).Above Average: Applies the conditional format to cells that rank above the average value of the range.Below Average: Applies the conditional format to cells that rank below the average value of the range.A dialog box appears, the name of which depends on the rule you selected in Step 3. For example, Figure 1-3 shows the dialog box for the Top Ten Items rule.

4 Type the value to use for the condition.You can also click the spin buttons that appear to the right of the text box. Note that you don’t need to enter a value for the Above Average and Below Average rules.FIGURE 1-3: The Top 10 Items dialog box with the top 5 values highlighted.

5 In the right drop-down list, select the formatting to apply to cells that match your condition. When you set up your top/bottom rule, select a format that ensures that the cells that meet your criteria will stand out from the other cells in the range. If none of the predefined formats suits your needs, you can always choose Custom Format and then use the Format Cells dialog box to create a suitable formatting combination. Use the Font, Border, and Fill tabs to specify the formatting you want to apply, and then click OK.

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