Outlining Worksheets

Many typical spreadsheet models are built in a hierarchical fashion. For example, in a monthly sales worksheet, you might have a column for each month of the year, followed by a totals column, which depends on the numbers in the month columns. You can set up the rows of data hierarchically, with groups of expense categories contributing to category totals. Office Excel 2007 can turn worksheets of this kind into outlines.

Figure 8-67 shows a table of sales figures before outlining, and Figure 8-68 shows the same worksheet after outlining. To accomplish this, we selected cell B3 in the table (any cell would do), clicked the Group menu on the Data tab, and clicked Auto Outline, as shown in Figure 8-67. (To outline a specific range, select the area before choosing Auto Outline.) Figure 8-68 shows how you can change the level of detail displayed after you outline a worksheet.

Figure 8-67: Start with a hierarchical worksheet like this one.

Figure 8-68: Make the worksheet hierarchies collapsible using the Excel outlining features.

On the CD You'll find the Team Sales. xlsx file in the Sample Files section of the companion CD.

The difference between the outlined worksheets in Figure 8-68 and Figure 8-69 is that the columns and rows listing the months and individual team members are hidden in Figure 8-69. Without outlining, you would have to hide each group of columns and rows manually; with outlining, you can collapse the outline to change the level of detail instantly. To restore the worksheet to its original state, click Clear Outline, located on the Ungroup menu on the Data tab, as shown in Figure 8-68.

Figure 8-69: Two clicks transformed the outlined worksheet in Figure 8-68 into this quarterly overview.

Outlining a Worksheet with Nonstandard Layout

The standard outline settings reflect the most common worksheet layout. To change these settings, click the Dialog Box Launcher (the little icon to the right of the group name) in the Outline group on the Data tab to display the Settings dialog box shown in Figure 8-70. If your worksheet layout is not typical, such as a worksheet constructed with rows of SUM formulas (or other types of summarization formulas) in rows above the detail rows or with columns of formulas to the left of detail columns, clear the appropriate Direction check box-Summary Rows Below Detail or Summary Columns To Right Of Detail-before outlining.

Figure 8-70: Use the Settings dialog box to adjust for a nonstandard layout.

When you use nonstandard worksheet layouts, be sure the area you want to outline is consistent to avoid unpredictable and possibly incorrect results; that is, be sure all summary formulas appear in the same direction relative to the detail data. After you select or clear one or both Direction options, click the Create button to create the outline.

Extending the Outline to New Worksheet Areas

At times, you might create an outline and then add more data to your worksheet. You might also want to re-create an outline if you change the organization of a specific worksheet area. To include new columns and rows in your outline, repeat the procedure you followed to create the outline in the first place: Select a cell in the new area, and click Auto Outline.

Inside Out-Just Say No to Automatic Styles

In the Settings dialog box, the Automatic Styles check box and the Apply Styles button apply rudimentary font formats to your outline that help distinguish totals from detail data. Unfortunately, this isn't very effective. To ensure that the outline is formatted the way you want, you should plan to apply formats manually.

Hiding an Outline

When you outline a worksheet, Excel displays symbols above and to the left of the row and column headings (refer to Figure 8-68). These symbols take up screen space, so if you want to suppress them, you can click the Microsoft Office Button, Excel Options; then click the Advanced category, and clear the Show Outline Symbols If An Outline Is Applied check box in the Display Options For This Worksheet area.

Collapsing and Expanding Outline Levels

When you create an outline, the areas above and to the left of your worksheet are marked by one or more brackets that terminate in hide detail symbols, which have minus signs on them. The brackets are called level bars. Each level bar indicates a range of cells that share a common outline level. The hide detail symbols appear above or to the left of each level's summary column or row. If you have hidden the outline symbols or if you prefer to use the Ribbon, you can also use the Show Detail and Hide Detail buttons in the Outline group on the Data tab to collapse and expand your outline.

To collapse an outline level so only the summary cells show, click that level's hide detail symbol. For example, if you no longer need to see the sales numbers for January through November in the outlined worksheet (refer to Figure 8-68), click the hide detail symbols above columns E, I, and M. The worksheet then looks like Figure 8-71.

Figure 8-71: When you click the hide detail symbols (-) above Q1, Q2, and Q3, Excel replaces them with show detail symbols (+).

Show detail symbols with a plus sign on them now replace the hide detail symbols above the Q1, Q2, and Q3 columns (columns E, I, and M). To redisplay the hidden details, click the show detail symbols.

Displaying a Specific Outline Level

To collapse each quarter so that only the quarterly totals and annual totals appear, you can click the hide detail symbols above Q1, Q2, Q3, and Q4. The level symbols-the squares with numerals at the upper-left corner of the worksheet-provide an easier way, however. An outline usually has two sets of level symbols, one for columns and one for rows. The column level symbols appear above the worksheet, and the row level symbols appear to the left of the worksheet.

Inside Out-Collapsing and Expanding with an IntelliMouse Pointing Device

You can use the wheel button on your IntelliMouse pointing device to manipulate an outline without using the detail symbols or level symbols. This is helpful if you prefer to suppress the display of outline symbols to see more of the worksheet on the screen yet you still want to be able to use the outlining feature.

Rest the pointer on the summary row or column you want to expand or collapse; then hold down the Shift key, and rotate the wheel backward (toward your hand) to collapse the outline or forward (away from your hand) to expand it. If you rest the pointer on a cell where a summary row and summary column intersect, the outline collapses or expands in both directions at once.

You can use the level symbols to set an entire worksheet to a specific level of detail. The outlined worksheet shown in Figure 8-68 has three levels each for columns and for rows. By clicking both of the level symbols labeled 2 in the upper-left corner of the worksheet, you can transform the outline shown in Figure 8-68 to the one shown in Figure 8-69.

Inside Out-Selecting Only the Visible Cells

When you collapse part of an outline, Excel hides the columns or rows you don't want to see. In Figure 8-71, for example, the detail columns are hidden for the first three quarters of the year. Ordinarily, if you select a range that includes hidden cells, those hidden cells are implicitly selected. Whatever you do with these cells also happens to the hidden cells, so if you want to copy only the displayed totals, using copy and paste won't work. Here's the solution: On the Home tab, click Find & Select, Go To Special, and select the Visible Cells Only option. This is ideal for copying, charting, or performing calculations on only those cells that occupy a particular level of your outline. This feature works the same way in worksheets that have not been outlined; it excludes any cells in hidden columns or rows from the current selection.

Ungrouping and Grouping Columns and Rows

If the default automatic outline doesn't give you the structure you expect, you can adjust it by ungrouping or grouping particular columns or rows. You can easily change the hierarchy of outlined columns and rows by clicking the Group and Ungroup buttons on the Data tab.

For example, you could select row 8 in the outlined worksheet shown in Figure 8-68 and click Ungroup to change row 8 from level 2 to level 1. The outlining symbol to the left of the row moves to the left under the row level symbol labeled 1. To restore the row to its proper level, click Group.

Note 

You cannot ungroup or group a nonadjacent selection, and you cannot ungroup a selection that's already at the highest hierarchical level. If you want to ungroup a top-level column or row to a higher level so it appears to be separate from the remainder of the outline, you have to group all the other levels of the outline instead.