15.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 tools include automatic outlining and subtotaling (both of which can create data groups automatically).
15.2.1. Auto Outline
Adding groups to a large worksheet can be tedious . You can leap over that tedium in a single bound with Auto Outline, 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, Auto Outline is a great solution when it works, and it's 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 Auto Outline-friendly. Here are the key points. Excel makes all its guesses about how to implement Auto Outline based on any formulas it finds that give it clues about the structure of your worksheet. 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 columns 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 's in order. In other words, Auto Outline requires formulas to determine the grouping levels it should add. If you don't use any summary formulas at all, the Auto Outline feature doesn't work.
Note: If you don't have any formulas at all on your worksheet, you're guaranteed that Excel can't create an outline.
To use automatic outlining, select the portion of the worksheet that you want to outline, and then choose Data Outline Group 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 Outline Group Clear Outline.
15.2.2. Automatic Subtotaling
Auto Outline isn't 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.
To subtotal and outline your data simultaneously , follow these steps:
If you want to remove subtotals, you have two choices. The easiest approach is to choose Data Outline 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.