outlining for success

Outlines are a good way to organize and summarize information by importance or pertinence. You’re probably already aware that you can make an outline in Word. But did you know that you can also outline your data in Excel?

Excel outlines allow you to quickly show and hide different levels of detail. With a click of the mouse, you can toggle between showing detail data, subtotals or totals. In this article, we’ll look at how to set up your data for outlining, how to outline automatically or manually, and even how to apply formatting styles to your outlines.

Preparing the worksheet

First you need to make sure your data is organized in a range so that it can be correctly outlined. Label the rows (or columns if you are outlining the columns) so that you can identify them, and make sure you have summary rows above or below the detail rows (unless you’re outlining columns, in which case you’ll want the detail columns totaled in a column to the left or right). The summary rows should contain formulas, such as SUM, to total the detail rows.

Adjusting outline settings

If you want a formatting style to be automatically applied to the outline when it is created, select Data > Group and Outline > Settings from the menu and click the Automatic styles check box. Click OK. If you decide not to do this, you can always add a style after you’ve created the outline by clicking the Automatic styles check box and then clicking the Apply Styles button.

View the Settings dialog box

If the summary rows are above the detail rows, or the summary columns are to the left of the detail columns, you also need to change the outline direction settings so Excel knows how your worksheet is organized. Click Data > Group and Outline > Settings from the menu. Click either the “Summary rows below detail” check box or the “Summary columns to the right of detail” check box to clear it.

Outlining automatically vs. manually

Excel can automatically outline your data for you, or you can do it manually. Automatic outlining only works if you have used formulas in your summary cells that reference the detail data cells.

For example, if cell A1 contains the value 2, cell A2 contains the value 3, and cell A3 contains the formula =SUM(A1:A2), then you’re set to use automatic outlining. On the other hand, if cell A3 simply contains the value 5, with no formula calculating it, Excel can’t recognize the worksheet’s structure, so you’ll need to manually outline the data.

To outline automatically, select the cell range you want to outline, or click any cell to outline the whole worksheet. Select Data > Group and Outline > Auto Outline from the menu. Voila! The data is outlined. If it doesn’t seem to have worked right, try rearranging labels, data, or totals until it works, or outline manually instead.

(continued on right...)

  quick jump

Jump to other stories through these links:

  ...continued

To outline manually, select a range of rows or columns that contain detail data—not the summary or total rows or columns. Select Data > Group and Outline > Group from the menu. Select Rows or Columns and click OK. Continue grouping ranges of data until you’ve created all the outline levels you desire.

Working with outlines

Once you’ve created an outline, outline symbols appear alongside the worksheet. You can either click a numbered outline symbol to display an entire outline level, or you can click the individual plus and minus symbols in each level to show or hide groups of detail.

Removing an outline

You can remove an outline by selecting Data > Group and Outline > Clear Outline from the menu.

  screen shots

The following screen shot(s) illustrate this article.  Click on one for a larger view.

View data organized in a range

View the settings dialog box

  schedule a class on Excel

Request an on-site Excel class from Applied Office sometime next year.  Sessions are just $95/hr and your employees will have the benefit of customized classes that they don't have to drive to.

Learn more here.

  quick reference card

Get the Quick Reference Card on Microsoft Excel!  Download it for free and print it on your own printer.  You might even want to laminate it.