Most of us probably associate outlines with textual information rather than numerical data. In Excel, however, you can use outlines (and the accompanying Subtotal tool) to break data down into logical subdivisions that you can quickly view or hide, depending on how you want to review the information. This setup provides an easy way to see results for parts of a whole, such as sales totals for certain regions.

The process starts with organizing your data so Excel can recognize category divisions. The trick is putting a label in the first column for each row and ensuring there are no blank cells. You also should place labels at the top of each column (such as “State” and “City” and “Sales Volume,” in our example) to let Excel create the outline later. If you’ve already entered a lot of data without grouping it into logical sections (such as by state and city office), you can use the Sort tool to put everything in order. To sort a block of cells, look in the Data tab’s Sort & Filter section. To sort the data, highlight the block, click Sort and use the Sort By drop-down list to indicate the sort terms.

Subtotal Tools

With the data in the right places, it’s time to insert Sub-total cells. You can do this manually by inserting a row and typing in a formula that adds up all the relevant figures for a subset of the data (such as all the sales for a particular state, in our example). Or you can use Excel’s built-in Subtotal tool, which is located on the far-right side of the Data tab in the Outline section. (The tool actually inserts the SUBTOTAL function, which you can study in the Formula bar after you’ve set it up.)

In addition to speed and convenience, inserting a subtotal command has the extra benefit of formatting your data into a collapsible outline view that lets you easily hide or reveal the details as needed. The Subtotal field also automatically recalculates itself whenever data in the workbook changes (assuming your workbook is using the default setting that tells it to automatically recalculate).

To insert the subtotal field, click a cell in the row where you want the subtotal line to appear. Click the Subtotal button on the far-right side of the Data tab. The dialog box that appears is intelligent enough to present you with choices based on the information in your cells. If you’ve organized your data like our example, the dialog box will suggest inserting a subtotal line at each point where the state changes in the list. If you’ve organized your data properly with column headings, the Subtotal dialog box also offers options for inserting the subtotal lines after changes in the City or the Sales Volume.

The next drop-down box lets you choose which function applies to the subtotal line. The Sum function is the typical choice, but you also can choose to have this line do something like average the values in the list or find the maximum (Max function) or minimum (Min) value in the list.

When you place a check mark next to the Sales Volume option and click OK to insert the subtotal line, you’ll see how smart this feature really is. It inserts subtotal lines with labels such as “Kansas Total” and “Iowa Total.” It also drops in a “Grand Total” line at the bottom of the list.

Outline Tools

When the subtotal lines appear, you’ll also see that the worksheet has picked up a new pane to the left. It contains several black bars with minus signs on them. These are outline controls that let you quickly collapse and expand your data to show views at multiple levels. You’ll notice, for instance, that a bar spans all the entries for a state. Click the minus sign to collapse the state info and see only the state total. Click the minus sign for the longest bar to hide everything except the Grand Total.

The numbered buttons at the top of this pane quickly hide and reveal all the data at a certain level of the outline. Click 1 to see only the Grand Total, click 2 to see the state totals, and click 3 to see everything