target="_blank" rel="nofollow" href="#ulink_303826d5-4ddd-54fb-a482-ec9e4346c3cb">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.
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.
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.
Consolidating Data from Multiple Worksheets
Companies often distribute similar worksheets to multiple departments to capture budget numbers, inventory values, survey data, and so on. Those worksheets must then be combined into a summary report showing company-wide totals. Combining multiple worksheets into a summary report is called consolidating the data.
Sounds like a lot of work, right? It sure is, if you do it manually, so forget that. Instead, Excel can consolidate your data automatically. You can use the consolidate feature to consolidate the data in either of two ways:
By position: Excel consolidates the data from two or more worksheets, using the same range coordinates on each sheet. Use this method if the worksheets you’re consolidating have an identical layout.
By category: Excel consolidates the data from two or more worksheets by looking for identical row and column labels in each sheet. Reach for this method if the worksheets you’re consolidating have different layouts but common labels.
In both cases, you specify one or more source ranges (the ranges that contain the data you want to consolidate) and a destination range (the range where the consolidated data will appear).
Consolidating by position
Here are the steps to trudge through if you want to consolidate multiple worksheets by position:
1 Create a new worksheet that uses the same layout — including row and column labels — as the sheets you want to consolidate.The identical layout in this new worksheet is your destination range.
2 If necessary, open the workbooks that contain the worksheets you want to consolidate.If the worksheets you want to consolidate are in the current workbook, you can skip this step.
3 In the new worksheet from Step 1, select the upper-left corner of the destination range.
4 Choose Data ⇒ Consolidate.The Consolidate dialog box appears.
5 In the Function list, select the summary function you want to use.
6 In the Reference text box, select one of the ranges you want to consolidate.
7 Click Add.Excel adds the range to the All References list, as shown in Figure 1-11.
8 Repeat Steps 6 and 7 to add all the consolidation ranges.
9 Click OK.Excel consolidates the data from the source ranges and displays the summary in the destination range.
FIGURE 1-11: Consolidate multiple worksheets by adding a range from each one.
