Microsoft Excel 2007: Outlining Worksheets

With an outline in place, you can click the + and - markers to display or hide parts of the worksheet data

When you have a lot of data in an Excel worksheet it often becomes necessary to summarize the information to get to the crux of what it is telling you. There are a number of ways to do this in Excel, one of which is to use Excel's built-in outlining function.

This function allows you to shrink data down to more manageable quantities so that you can see the bottom-line result rather than all the items and pieces that go into making it. In this article we'll illustrate and walk through the process of using the Outline tools in Excel 2007 to summarize worksheet data.

Excel contains an automatic outline tool that works well in most cases, but if your data doesn't lend itself to automatic outlining you can also create an outline manually. Let's look first at some data that can be outlined automatically. This worksheet contains some basic personal spending data and it has totals for income and expenses in columns D and I of the worksheet.

You can outline a worksheet manually as shown here where columns B and C are grouped so they can be hidden

To outline this, click somewhere inside the range containing the data and next click the Data tab. From the Group dropdown list select Auto Outline. If Excel cannot create an outline for your data, you will see an error in which case you'll need to outline it manually as shown below.

In this instance, however, Excel can create an auto-outline, and you will see the outline symbols appearing above the worksheet. Use the plus (+) and minus (-) symbols to expand and contract the worksheet data below. You can minimize the worksheet to display only income and expenses totals.

If you decide that you no longer want the worksheet to be outlined, turn the outlining off by clicking in a cell inside the outlined area and, from the Data tab, selecting Ungroup > Clear Outline.

» Creating an Outline Manually

If it’s not possible to outline a worksheet using the outline tool you can manually create an outline for your data. To do this, let’s take a worksheet containing an inventory list like the one shown in the illustration on the left. If we want to be able to hide columns B and C and just see columns A and D, we can add an outline group that spans columns B and C.

The Subtotal dialog lets you subtotal your data depending on the values in certain columns of your worksheet

To do this, select columns B and C and from the Data tab select Group > Group. This groups these two columns and lets you display or hide them as desired, so you can now see all the data or just the Item number and the number of those items in stock.

» Using Subtotals

It is also possible to create an outlined worksheet automatically when you use the Subtotals command to subtotal data in your worksheet. Let's go back to the inventory worksheet and remove the current outline.

To add subtotals for the data, click in a cell in the data area and choose the Data tab and click Subtotal. When the Subtotal dialog appears, select the column name by which you want to subtotal your data — the data needs to be sorted in order by this column so like values are adjacent to each other. In our case we'll subtotal by the Color column.

As we want to sum the number of items for each color, choose Sum from the Use Function list and then, because we need a sum of the Number in Stock for each color, we'll select the Number in Stock checkbox.

| Next Page »