Excel Data Analysis For Dummies. Paul McFedries

the rule you want to modify.If you don’t see the rule, click the Show Formatting Rules For drop-down list and then select This Worksheet. The list that appears displays every conditional-formatting rule that you’ve applied in the current worksheet.

      4 Choose Edit Rule.The Edit Formatting Rule dialog box appears.

      5 Make your changes to the rule.

      6 Click OK.Excel returns you to the Conditional Formatting Rules Manager dialog box.

      7 Select OK.Excel updates the conditional formatting.

      

If you have multiple conditional-formatting rules applied to a range, the visualization is affected by the order in which Excel applies the rules. Specifically, if a cell already has a conditional format applied, Excel does not overwrite that format with a new one. For example, suppose that you have two conditional-formatting rules applied to a list of student grades: one for grades over 90 and one for grades over 80. If you apply the over-80 conditional format first, Excel will never apply the over-90 format because those values are already covered by the over-80 format. The solution is to change the order of the rule. In the Conditional Formatting Rules Manager dialog box, select the rule that you want to modify and then click the Move Up and Move Down button to set the order you want. If you want Excel to stop processing the rest of the rules after it has applied a particular rule, select that rule’s Stop If True check box.

      Removing conditional-formatting rules

Conditional-formatting rules are useful critters, but they don’t work in all scenarios. For example, if your data is essentially random, conditional-formatting rules won’t magically produce patterns in that data. You might also find that conditional formatting isn’t helpful for certain collections of data or certain types of data. Or you might find conditional formatting useful for getting a handle on your data set but then prefer to remove the formatting.

      Similarly, although the data-visualization aspect of conditional-formatting rules is part of the appeal of this Excel feature, as with all things visual, you can overdo it. That is, you might end up with a worksheet that has multiple conditional-formatting rules and therefore some unattractive and confusing combinations of highlighted cells, data bars, color scales, and icon sets.

      If, for whatever reason, you find that a range’s conditional formatting isn’t helpful or is no longer required, you can remove the conditional formatting from that range by following these steps:

      1 Select a cell in the range that includes the conditional-formatting rule you want to trash.You can select a single cell, multiple cells, or the entire range.

      2 Choose Home ⇒ Conditional Formatting ⇒ Manage Rules.The Conditional Formatting Rules Manager dialog box appears.

      3 Select the rule you want to remove.If you don’t see the rule, use the Show Formatting Rules For list to select This Worksheet, which tells Excel to display every conditional-formatting rule that you’ve applied in the current worksheet.

      4 Choose Delete Rule.Excel removes the rule from the range.

      5 Click OK.

      

If you have multiple rules defined and want to remove them all, click the Home tab, choose Conditional Formatting, choose Clear Rules, and then select either Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

Summarizing Data with Subtotals

      Although you can use formulas and worksheet functions to summarize your data in various ways — including sums, averages, counts, maximums, and minimums — if you’re in a hurry, or if you just need a quick summary of your data, you can get Excel to do the work for you. The secret here is a feature called automatic subtotals, which are formulas that Excel adds to a worksheet automatically.

Excel sets up automatic subtotals based on data groupings in a selected field. For example, if you ask for subtotals based on the Customer field, Excel runs down the Customer column and creates a new subtotal each time the name changes. To get useful summaries, you should sort the range on the field containing the data groupings you’re interested in.

      Follow these steps to summarize your data with subtotals:

      1 Select a cell within the range you want to subtotal.

      2 Choose Data ⇒ Subtotal.If you don’t see the Subtotal command, choose Outline ⇒ Subtotal. The Subtotal dialog box appears.

      3 In the At Each Change In list, select the column you want to use to group the subtotals.

      4 In the Use Function list, select Sum.

      5 In the Add Subtotal To list, select the check box for the column you want to summarize.In Figure 1-8, for example, each change in the Customer field displays the sum of that customer’s Total cells.

      6 Click OK.Excel calculates the subtotals and adds them into the range. Note, too, that Excel also adds outline symbols to the range. I talk about outlining in a bit more detail in the next section.

      FIGURE 1-8: Use the Subtotal dialog box to apply subtotals to a range.

Figure 1-9 shows some subtotals applied to a range.

      FIGURE 1-9: Some subtotals applied to the Total column for each customer.

      

Note that in the phrase, automatic subtotals, the word subtotals is misleading because it implies that you can summarize your data only with totals. Not even close! Using “subtotals,” you can also count the values (all the values or just the numeric values), calculate the average of the values, determine the maximum or minimum value, and calculate the product of the values. For statistical analysis, you can also calculate the standard deviation and variance, both of a sample and of a population. To change the summary calculation, follow Steps 1 to 3, open the Use Function drop-down list, and then select the function you want to use for the summary.

Grouping Related Data

      To help you analyze a worksheet, you might be able to control what parts of the worksheet are displayed by grouping the data based on the worksheet formulas and data. Grouping the data creates a worksheet outline, which works similarly to the outline feature in Microsoft Word. In a worksheet outline, you can collapse sections of the sheet to display only summary cells (such as quarterly or regional totals), or expand hidden sections to show the underlying detail. Note that when you add subtotals to a range, as I describe in the preceding section, Excel automatically groups the data and displays the outline tools.

Not all worksheets can be grouped, so you need to make sure that your worksheet is a candidate for outlining:

       The worksheet must contain formulas that reference cells or ranges directly adjacent to the formula cell. Worksheets with SUM functions that subtotal cells above or to the left are particularly good candidates for outlining.

       There must be a consistent pattern to the direction of the formula references. For example, a worksheet with formulas