Outlining Your Data

Large worksheets and long lists of data can quickly become unmanageable, but there's a Microsoft Excel tool that can help you get control. Outlining lets you group data in a worksheet into different levels and hide or display the levels as you choose. You can take a high-level view of the final totals of your calculations or drill down to view the data in more detail. We'll take a look at how to create an Excel outline, and we'll also explain how to use the Subtotals command to produce outlines automatically for some worksheets.

To understand outlining, consider a monthly budget with expenses listed down the left side of the worksheet. The months of the year appear across the top, with each group of three months followed by a quarterly subtotal. The final column contains the totals for the year.

This worksheet contains at least 18 columns of data and totals, so it will occupy multiple screens horizontally. By using an outline, you can collapse and hide everything except the area you're working on, which lets you focus your attention on the figures and their results.

To outline the worksheet, select the columns for January to March and choose Data | Group and Outline | Group. This groups the three columns so they can be displayed or hidden, using the expand and collapse indicators that appear above the worksheet.

Repeat this process with the months April to June, July to September, and October to December. When you do this, + and keys appear above the groups so you can expand and collapse each one individually. Click on the level buttons above the top left corner of the worksheet to show or hide all grouped data in one step.

You can group the groups to create a higher level of summarization. To do this, display and select all the columns from January to the fourth-quarter summary, and again choose Data | Group and Outline | Group. You'll now see three level buttons above the top left corner of the worksheet, instead of the two that there were there before.

Click on button 1—the highest level—to summarize the worksheet to show only the annual total. Click on 2 to show the four quarter subtotals and the annual total, and click on 3 to expand the worksheet to show all the data. The + and buttons let you show or hide the individual groups. You could, for example, display data for January to March and the quarter and annual totals while you study January's figures. This makes the worksheet more manageable both for editing and for analysis.

Outlining can be even easier. If your worksheet is arranged in a hierarchical format with subtotal formulas, there's a good chance Excel can do the job for you automatically. Just choose Data | Group and Outline | Auto Outline and see what happens. If this doesn't give you what you need, you can create the outline manually.

Depending on the data you're working with, you can take advantage of the outlining behavior of the Subtotals command to create subtotals and an outline in one step. This works well on the kind of list where in one or more columns there are repeated entries that you want to group together.

For example, a worksheet recording the daily sales details for a number of stores for a given month will include multiple entries for each date—one for each store open on a certain day. A particular store name will appear multiple times—one for each day the store is open. This worksheet could be grouped by date or by store.

Start by sorting the data on the column you want to focus on. To total the month's results for each store, for instance, sort into store order. Then select the table and choose Data | Subtotals. In the Subtotal dialog choose the column to group by—in this case, the one containing the store name. Then choose the function to calculate—in our case it would be Sum. From the Add subtotal to drop-down list choose the field to sum, which in our case would be the day's sales. Enable the Summary below data checkbox and click on OK.

Excel groups and outlines the data automatically and adds subtotals for each group and an overall total. You can collapse and expand the data exactly as if you had created the outline manually. If you sort the data again, the subtotals and outlining disappear, but, as you can see, repeating the process is very simple.