14.2. Grouping Timesavers So far, you've learned how to tame an intimidating worksheet and neatly organize it into groups. Why stop there? This section covers a few other tools that, along with the grouping tools you've just mastered, will make your life easier. These include automatic outlining and subtotaling (both of which can create data groups automatically). This section also teaches you how to create a customized grouping toolbar that'll rock your world.  | Figure 14-8. Here, the newly created consolidated data table is at the bottom of the worksheet. In this case, the Sum option was used, which means that the consolidated data shows the totals that are arrived at by adding the values from the separate tables. For example, cell B18 has the consolidated data for regularly priced merchandise sold in the first quarter in both the West and East Divisions ( cells B3 and B11). | |
14.2.1. AutoOutline Adding groups to a large worksheet can be tedious . You can leap over that tedium in a single bound with AutoOutline, a feature that gets Excel to examine your worksheet and then create all the column and row groups you need automatically. Like most automatic features, AutoOutline is a great solution when it works, and it is absolutely no help the rest of the time. Automatic outlining is an all-or-nothing affair, giving you either a grouped worksheet or the unhelpful error message, "Cannot create an outline." You also don't have any ability to configure how many levels of nesting Excel uses, how many groups it creates, and whether it creates column groups, row groups, or both. With a bit of planning, however, you can set up your worksheets so they're AutoOutline-friendly. Here are the key points. Excel makes all its guesses about how to implement AutoOutline based on any formulas it finds that give it clues about the structure of your worksheet. For example, if you have a formula that uses SUM( ) to total the cells in multiple columns in the same row, Excel assumes that it can place these in a single group so that you can collapse the details and leave just the totals visible. If Excel finds a formula that uses the data in the rows above it, the program assumes that a row group is in order. In other words, AutoOutline requires formulas to determine the grouping levels it should add. If you don't use any summary formulas at all, the AutoOutline feature won't work.
Note: If you don't have any formulas at all on your worksheet, you're guaranteed that Excel won't be able to create an outline.
GEM IN THE ROUGH
Formatting Your Worksheet with Outline Styles | | You can use AutoOutline to not only group your data, but also to apply formatting automatically. To do so, select Data  Group and Outline Settings. In the Settings dialog box, turn on the "Apply styles" checkbox, and then click OK. Now, when you outline your worksheet, Excel automatically tags the different summary cells with different styles. For example, Excel will set the column total cells to use the ColLevel_1 style, which simply applies bold formatting. The final summary rows in the report table will use RowLevel_1 style (bold formatting), and the row subtotals for each table will use RowLevel_2 style (italic formatting). Remember, the topmost group is always level 1. The levels increase as you dig more deeply into nested groups. Having Excel automatically bold and italicize certain cells doesn't sound like an impressive feat for a cutting-edge application. However, the real benefit of outline styles is that you can tweak them to suit your needs, either before or after you outline the worksheet. For example, you could decide to shrink the detail rows, or to highlight the column totals with a bright background color . All you need to do is select Format Style, and modify the current row and column styles. For more information about modifying styles, refer to Section 4.3.4. |
To use automatic outlining, select the portion of the worksheet that you want to outline, and choose Data Group and Outline Auto Outline. Generally, its easiest to select the whole worksheet (click the square just outside the top-left corner of the worksheet) to apply grouping to all your data at once. If you don't like the results, you can't undo the outlining operation, but you can remove all the groups from your worksheet by choosing Data Group and Outline Clear Outline. 14.2.2. Automatic Subtotaling AutoOutline is not the only outlining trick that Excel has up its sleeve. The Subtotal tool lets you create groups and subtotals all in one clicka feature that can save you scads of time. To use automatic subtotaling, you need to have a long table of repetitive data (product catalogs, sales transactions, calorie -counting meals on the Atkins plan). You can't use more than one tableinstead, the goal is to get Excel to break your table into summarized subtables for you. Each table gets its own subtotal (for any or all of the columns that you choose) and all the subtotals get added together for your very own Grand Total.
Note: The long list of data you need for the automatic subtotaling feature should look more or less like the data lists you saw in Chapter 13. However, you can't use automatic subtotaling with a data listonly ordinary cells will work. Remember, you can always convert a list to a regular range of cells by choosing Data List Convert to Range.
-
Sort your data so that the rows you want to group are together in the list. Before subtotaling your data, you may need to sort it into the right order. For example, if you have a list of products and you want to subtotal the product information by category, you need to sort the table by category before you begin. This step ensures that all the products in each category are grouped together. For detailed information about sorting, see Section 13.3. -
Once you have the list in the correct order, select the range of cells that contains the list. Usually, the easiest option is to select each of the columns in your list by dragging across the column headers. -
Choose Data Subtotals. The Subtotals dialog box appears. -
In the "At each change in" pop-up menu, choose the field under which you want the subtotal to appear. The menu provides a list with all the column titles in your selection. For example, if you wanted to group together (and create subtotals for) products that have the same Category label (as shown in Figure 14-9), you'd choose Category. Excel will insert a subtotal row each time the category label changes. (In the figure, the Communication products are the first group that will subtotal, the Deception products are next, and so on.) In order for this process to work, the list must be sortedas you did in step 1so that all records with the same category are already next to each other.  | Figure 14-9. This worksheet has a long list of product data that's about to be subtotaled. Excel will create the subtotals for each group of products that has a different label in the Category column. The subtotal row will include the average price of all the products in each group. | | -
In the "Use function" pop-up menu, choose the function you want to use to calculate the subtotal information. This list includes everything supported by the SUBTOTAL( ) function, including averages, counts, and subtotals. Unfortunately, you can't total different columns using different functions. -
The "Add subtotal to" list includes all the column names in your selection. Put a checkmark next to each column you want to generate a subtotal for. To calculate the subtotals, Excel uses the function you chose in step 5. -
If you want to start each group on a new page, turn on the "Page break between groups" checkbox. This option works well if you have large groups, and you want to separate them in a printout. -
If you want to display the summary information at the end of group, choose "Summary below data." Otherwise, the totals will appear at the beginning of the group. For example, if you're dividing products into category groups, choose "Summary below data" to make sure you'll see the listing of products, followed by the subtotal row. -
Click OK to group and subtotal the data. Excel inserts a row in between each group, where it adds the formulas that calculate the subtotals for the group (Figure 14-10). All of the formulas use the SUBTOTAL( ) function. Excel also calculates grand totals for all the subtotals that have been generated.  | Figure 14-10. Here's the product list from Figure 14-9 after subtotaling is applied. Rows 7 and 13 contain the newly calculated subtotals. Excel has also added a grand total at the bottom of the list (not shown in this figure). | |
If you want to remove subtotals, you have two choices. The easiest approach is to choose Data Subtotals to open the Subtotals dialog box, and then click the Remove All button. Alternatively, you can replace the existing subtotals with new subtotals. To do this, choose different options in the Subtotal dialog box, and make sure the "Replace current subtotals" checkbox is turned on. 14.2.3. Your Own Custom Grouping Toolbar As you've seen in the examples so far, you need to head to the Data Group and Outline submenu to access Excel's grouping and outlining features. This takes a few extra mouse-clicks, and it means that useful commands like Show Detail and Hide Detail (to expand and collapse groups) are buried enough that you'll almost never use them.
Note: Show Detail and Hide Detail work the same way as clicking the expand/collapse iconsthey expand or collapse the nearest group. See Table 14-1 for the lowdown.
Of course, there's no reason that you can't build your own custom toolbar that includes all the grouping and outlining features. Just follow these steps: -
Select Tools Customize. The Customize dialog box appears. -
Select the Toolbars tab. You'll see a list of all the Excel toolbars on your computer, with a checkmark next to each one that's currently displayed in the Excel window. -
You can either create a new toolbar for the grouping buttons , or add them to an existing toolbar. If you want to create a new toolbar, click the New button, and then specify a name for your toolbar (like Group). Click OK to create the toolbar. If you want to use an existing toolbar, just make sure it's visible (it should have a checkmark in the list). When you create a new toolbar, it first appears as a floating toolbar (Figure 14-11), but you can drag it to any side of the Excel window to dock it in place.  | Figure 14-11. It's easy to create a custom toolbar that has all the features you need. With this toolbar, you can quickly group or ungroup selected rows or columns (using the group buttons), and you can quickly expand or collapse groups (using the hide/show details buttons). | | -
In the Customize dialog box, click the Commands tab. This tab shows all the commands you can add to any toolbar. The commands are divided into multiple categories (shown on the left side of the window). -
In the Categories list, choose Data. The list of commands related to Excel's data features appears on the right side of the window. -
Scroll down the list until you see the Hide Detail command. This represents the start of about seven commands that work with Excel's grouping features. -
Drag the commands you want to use to the toolbar one at a time. Use Table 14-1 as a reference to decide which commands you want to have easy access to. You can also drag the commands off the toolbar to remove them, or you can drag them into a different position.
Tip: The Show Outline Symbols is a useful feature that lets you hide the grouping lines when you don't need them. It's not available in the Group and Outline submenu, so the only way you can perform this action is to add this button to a toolbar.
Right-clicking a command brings up a slew of additional optionssee Appendix B for more information. -
Once you've perfected your toolbar, click Close. Your toolbar remains visible, and it automatically reappears every time you start Excel. To hide or show your toolbar, just select it from the View Toolbars menu. Table 14-1. Grouping Commands for the Toolbar | Name | Description | | Hide Detail | Collapses the current group. If you aren't currently inside a group, this button has no effect. | | Show Detail | Expands the current group. If you aren't currently positioned in a row or column that has a plus symbol, this button has no effect. | | Group | Groups the currently selected rows or columns. | | Ungroup | Removes the group in the currently selected rows or columns. | | Auto Outline | Performs automatic outlining for the current selection. | | Clear Outline | Removes an automatic outline. | | Show Outline Symbols | Shows the worksheet margins with the plus (+) and minus (-) signs that allow you to collapse and expand groups. If these margins are already visible, this button hides them, giving you more room to see your data. |
|
