Section 14.1. Basic Data Grouping | Excel 2010: The Missing Manual

14.1. Basic Data Grouping

The starting point for simplifying your worksheets is learning how to group data. Grouping data lets you tie related columns or rows into a single unit. Once you've put columns or rows into a group, you can quickly collapse the group, temporarily hiding it and leaving more space for the rest of your data. At its simplest, grouping is just a quick way to help you easily view what you want to see, when you want to see it. It's most handy when you want to show all the key summary information but none of the numbers that you need to crunch in order to arrive at these results.

Note: Excel's grouping settings also affect how your worksheet prints outcollapsed rows or columns won't appear in your printout.

14.1.1. Creating a Group

To see how grouping can simplify complex worksheets, check out the single table of sales report data shown in Figure 14-1. In this example, the information fits easily into the viewable area of the Excel window, but in a real-world company, you could easily end up needing to extend the table in both directions.

Figure 14-1. This worksheet shows the products sold at a retail store, with the numbers broken down in two ways: by quarter, and by the type of merchandise (regular priced, on-sale , or clearance). The data is subtotaled so you can tell how much the company sold in a particular category over the whole year (cells B7:D7), how much it sold in a quarter in all categories ( cells E3:E6), and how much it sold in total ( cell E7).

For example, you might widen the table by dividing the sales into more categories (representing different types of promotions, different discounts from regular prices, or different departments in the store). Or, you might lengthen the table by adding sales quarters from more than one year, or by tracking monthly, weekly, or even daily sales totals. In either case, the table would become fairly unwieldy, forcing you to scroll up and down the worksheet, and from side to side. By grouping columns (or rows) together, you can pop them in and out of view with a single mouse click.

Note: You may remember from Chapter 6 that you can also use hiding to temporarily remove rows and columns from sight. But grouping is a much nicer approachit makes it easier to pop data out of and back into view without sending you hunting through the menu. It also makes it more obvious to the person reading the worksheet that there's more data tucked out of sight. Hiding is more commonly used to remove gunk that no one ever needs to see. Technically, grouping uses Excel's hiding ability behind the scenes to make grouped cells disappear temporarily.

Here are the steps you'd need to follow in order to group several columns together using the sales report example:

Select the columns you want to group.

In most cases, you'll want to group the detail columns that provide fine-grained information (like columns B, C, and D). You shouldn't include in your group any subtotals (like column E) that summarize your information, because you're likely to want to see those subtotals.

To make your selection, drag over the column headers so that you've selected the entire column. This way, Excel can tell right away that you want to group columns. In the sales report example, you would choose columns B, C, and D.
Choose Data Group and Outline Group.

If Excel isn't sure which groups of cells you want to group, it shows a Group dialog box that lets you specify whether you're grouping columns or rows. (For example, if you selected cells B3:D3, Excel would consider it an ambiguous selection because the selection could represent an attempt to group the columns or rows.) If the Group dialog box appears, make your selection and then click OK.

Up To Speed Comparing Grouping and Lists

Overall, grouping isn't as powerful as Excel's data lists (covered in Chapter 13). Whereas lists include features for sorting, filtering, and searching, groups simply try to make it easier to work with data tables that use one or more levels of subtotals.

Of course, there's no reason you can't use both grouping and data lists. For example, you might group one or more columns in a big data list so that you can see just the columns you want (all the columns that track address components , for instance).

This approach works well if you're grouping multiple columns, but it doesn't work as well if you're grouping rows.

The problem here is that Office 2003 data lists grow dynamically. That means if you add a new row at the bottom of a list (or a new column on either side), the list automatically expands to incorporate the new information. Groups don't have the same behavior. As a result, if your data list grows, some of it might slip out beyond an edge of the group.

When you group your columns, the worksheet doesn't change, although a new margin area appears at the top of the worksheet, as shown in Figure 14-2. This margin allows you to collapse and expand your groups. To collapse a group, click the minus sign (-), which will then change into a plus sign (+). To expand a collapsed group, click the plus sign (+).

Figure 14-2. Top : Here, columns B, C, and D have been grouped together.
Bottom : By clicking the minus icon in the worksheet's upper margin, you can quickly collapse this group, hiding these columns from the spreadsheet. When you've hidden columns, Excel still uses them in calculations (like those in column E).

To remove a group after you've established it, expand the group, select all the columns, and then choose Data Group and Outline Ungroup. If you select only some of the columns, Excel removes the selected columns from the group, but leaves the rest of the group intactas long as at least two grouped columns remain . (If you remove a column from the middle of a group, you'll actually be left with two groups, one with the columns on the left, and one with the columns on the right.)

Note: You can also ungroup a set of columns while the group is collapsed. Just select a range of columns that includes the hidden columns, and then use the Data

Group and Outline

Ungroup command. Even though you've removed the group, the columns remain hidden and trapped out of sight! The only way to expose these columns after you've removed the group is to select a range of columns that includes the hidden columns, and then choose Format

Column

Unhide.

Group and Outline

Group. This time, a margin will appear just to the left of the worksheet, allowing you to quickly hide the grouped rows. You can also have groups of rows and groups of columns, as shown in Figure 14-3.

Figure 14-3. The trick to mastering Excel's grouping abilities is understanding how the grouping bars work. The grouping bars show you the range of cells that are bound together in a single group. The minus sign (-) icon always appears just outside the group (either over the column to the left, or under the last row). That's because this icon must remain visible after you've collapsed the group so that you can expand it.

Note: If you insert a new row between two grouped rows using the Insert

Rows command, Excel automatically places the new row into the group. The same is true if you insert a new column between two grouped columns using Insert

Columns.

Tip: If you decide to use multiple groups, it makes sense to group related rows or columns. For example, you might group all the columns that have address information into one group.

14.1.2. Nesting Groups Within Groups

As you've seen, grouping lets you temporarily shrink the size of a large table by removing specific rows or columns. Grouping becomes even more useful when you create a worksheet that has multiple tables, like the one shown in Figure 14-4.

Figure 14-4. This worksheet includes two separate groups of lists. Currently, the lower group is collapsed, so that only the totals are visible. Thanks to grouping, you can fill a worksheet with dozens of separate tables with detailed information, but you can still see the summary information for them all when you collapse the groups.

Not only can you create separate groups for different tables, you can also put one group inside another. This approach gives you multiple viewing levels. For example, as shown in Figure 14-5, the person using the worksheet can choose to see all the data in each of the four regional tables, just the summary information (listed in the Total line), or to hide a table completely.

Note: The only drawback is that the more levels of grouping you add, the more room Excel needs in the margins at the side of the worksheet in order to display all the grouping lines.

Technically, when you add more than one level of grouping, you're adding collapsible outline views to your worksheet, which let you quickly move back and forth between a bird's-eye view of your data and an up-close glimpse of multiple rows.

14.1.3. Summarizing Your Data

Understanding how to quickly collapse and expand tables is all well and good, but before long, you'll want to add up the totals you've collected in these accordion-style tables. In the sales report shown in Figure 14-5, for example, the perfect way to complete this worksheet would be to add a final table that sums up all the Total rows listed in the last row of each division.

Figure 14-5. Top : All four tables are partially collapsed, and each has its second-level groups hidden so that they show just the Total sales information for each region.
Bottom : Only two tables are visible. The West Division is completely visible because both its levels are expanded. The East Division is partially visible because its second-level group is collapsed. The North and South Division tables aren't visible at all because the first-level group for each table is collapsed.

To create this table, you need to employ just a few more formulas. These formulas will add the separate subtotals (contained in columns B, C, D, and E) to arrive at a final series of grand totals. For example, the following formula would calculate the total sales in all divisions and all quarters for regular merchandise:

 =B7+B15+B23+B31

Note: You could, of course, also calculate any of these subtotals by using the SUM( ) function, which is covered on Section 8.2.1.

GEM IN THE ROUGH
Collapsing and Expanding Multiple Groups at Once

If you create a worksheet with multiple levels of grouping, you can find yourself doing a lot of clicking to expose all the information you're interested in (or to hide all the details you don't care about). Fortunately, Excel includes a helpful shortcut that lets you expand or collapse multiple groups at once. These are the grouping buttons .

Excel provides two sets of grouping buttons, one for row grouping (just above the row numbers), and one for column grouping (just to the left of the column letters ).

Each grouping button is labeled with a number. If you click the number 1, Excel collapses all of the column or row groups. If you click the number 2, Excel collapses all the groups except the top level. If you click the number 3, Excel collapses all the groups except the first two levels, and so on. For example, in the sales worksheet shown in Figure 14-5, you can click 3 to show the whole worksheet, 2 to show just the subtotals, or 1 to hide all the sales tables.

Excel displays a different number of grouping buttons, depending on how many levels of grouping there are in your worksheet. The largest numbered button you'll see is 8, because Excel allows a maximum of seven levels of nested groups.

You'll notice that no matter how you expand or collapse the groups in your worksheet, the result of these formulas is always the same. That's because, whether you're using straight addition (of multiple cells) or the SUM( ) function, Excel takes into account visible and hidden cells. If you're using grouping, however, it might occur to you that it would be handy if you perform a calculation that deals only with the visible cells. That way, you could choose the portions of a worksheet you want to consider, and the formula could recalculate itself automatically to give you the corresponding summary information.

Good news: Excel gives you the power to build this sort of formula. All you need is the awesome SUBTOTAL( ) function. As you recall from Chapter 13, the SUBTOTAL ( ) function is a useful tool for calculating totals in filtered lists. But it's just as helpful when you apply it to outlines because it ignores collapsed rows and columns.

As explained on Section 13.4.3, the first argument (known as the calculation code) you use with the SUBTOTAL( ) function tells Excel the type of math you want to do (summing, averaging, counting, and so on). If you want to ignore the hidden cells in collapsed groups, you have to use the calculation codes above 100. For example, if you want to perform a sum operation, you'll need to use the code 109. (For a full list of the SUBTOTAL( ) calculation codes, see Section 13.4.3.)

Once you've chosen the calculation code, you need to specify the cells or range of cells you want to add. Here's an example that rewrites the earlier formula to sum only visible cells:

 =SUBTOTAL(109,B7,B15,B23,B31)

Figure 14-6 shows the difference between using the SUBTOTAL( ) function and the SUM( ) function.

Figure 14-6. This worksheet calculates summary information using two different approaches. The formulas in row 37 use the SUM( ) function, while the formulas in row 38 use the SUBTOTAL( ) function. In the SUBTOTAL( ) calculation, Excel doesn't use the subtotals for the completely hidden tables, giving a different result than that provided by the SUM( ) function.

14.1.4. Combining Data from Multiple Tables

Instead of writing your summary formulas by hand, you can generate a summary table automatically that takes advantage of Excel's ability to consolidate data. Consolidation works if you have more than one table with precisely the same layout. For example, you can use consolidation to take the different tables shown in the Figure 14-5 sales report (West Division, East Division, and so on) and calculate summary information. Excel will create a new table that has the same structure, but combines the data from the other tables. You can choose how Excel combines the data, including whether the numbers should be totaled, averaged, multiplied, and so on.

Data consolidation works with any worksheet (with or without grouping); you can even use it to analyze data in identically structured tables from different worksheets.

Note: For best results, you should consolidate data with the exact same layout only. Although you can coax Excel into combining differently sized ranges of data, it's all too easy to confuse yourself about what is and isn't combined. To make life easiest , only consolidate ranges that have numeric data or identical labels (like column or row titles). Leave out the overall table title because there's no way to consolidate it.

To consolidate the sales report data, follow these steps:

Move to the location where you want to insert the summary table.

Excel will insert the summary table, starting at the current cell. Make sure you've scrolled down past all your data, so you don't overwrite important information.

Choose Data Consolidate.

The Consolidate dialog box appears, as shown in Figure 14-7.

Figure 14-7. This worksheet contains two detailed sales tables that are about to be consolidated. Both ranges have been added to the Consolidate dialog box (they're listed in the "All references" list), and the "Top row" and "Left column" boxes have been turned on so that Excel can find and use the headings on either side of the table.

From the Function pop-up menu, choose how you want to combine numbers.

In the sales report, the best choice is Sum to calculate total sales. However, you might want to create separate tables that pick out the best or worst sales using Max and Min.
Click inside the Reference text box. Now, drag to select the first table you want to consolidate in the worksheet.

If Excel's main window isn't already visible, click the icon at the right end of the Reference text box to collapse the Consolidate dialog box. You'll have to click this icon again to restore the window when you're done selecting the cells you want.
Once you've selected the appropriate cells, click the Add button in the Consolidate dialog box.

The range appears in the "All references" list.
Return to step 4 to select the next table you want to consolidate.

Repeat steps 4 and 5 for each table you want to consolidate.
If your selection includes labels (for example, row or column titles), select the "Top row" or "Left column" checkbox to tell Excel where the labels are.

If you don't tell Excel where the labels are, it will ignore these cells and the corresponding cells in the summary table will wind up blank. But if you use these checkboxes to tell Excel where the labels are, Excel simply copies the labels directly to the summary table.

Note: If the labels don't match exactly in the ranges you're using, you may need to clear the "Top row" and "Left column" checkboxes to perform your consolidation. Otherwise, depending on the placement of these labels, Excel might refuse to consolidate your data.
Click OK to generate the summary table (Figure 14-8).

When creating the consolidated data, Excel copies headings and calculates numbers, but doesn't copy any of the source formatting.

The key disadvantage to data consolidation is that it generates a table filled with numbers, rather than formulas. Microsoft's engineers probably designed the consolidation feature this way so that you can consolidate data from multiple files and not worry about losing the information if the source files move or their structure changes.

But this behavior means that if you modify any of the sales figures, the summary table won't update. Instead, you'll need to generate a completely new summary table by using the Data Consolidate command. Fortunately, the second time around should be quite a bit faster, because Excel keeps track of all the ranges you've selected for consolidation, so you won't need to define them again.