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

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 that always reference cells above or to the left can be outlined. Excel won’t outline a worksheet with, say, SUM functions where some of the range references are above the formula cell and some are below.

Here are the steps to follow group-related data:

1 Display the worksheet you want to outline.

2 Choose Data ⇒ Group ⇒ Auto Outline.If you don’t see the Group command, choose Outline ⇒ Group. Excel outlines the worksheet data.

As shown in Figure 1-10, Excel uses level bars to indicate the grouped ranges and level numbers to indicate the various levels of the underlying data available in the outline.

FIGURE 1-10: When you group a range, Excel displays its outlining tools.

Here are some ways you can use the outline to control the range display:

 Click the − (collapse) button to hide the range indicated by the level bar.

 Click the + button (expand) for a collapsed range to view it again.

 Click a level number to collapse multiple ranges on the same outline level.

 Click a level number to display multiple collapsed ranges on the same outline level.