Creating and Using Worksheet Outlines - Using Advanced Excel Features - Microsoft Excel 2016 BIBLE (2016)

Microsoft Excel 2016 BIBLE (2016)

Part IV
Using Advanced Excel Features

Chapter 27
Creating and Using Worksheet Outlines


1. Introducing worksheet outlines

2. Creating an outline

3. Working with outlines

If you use a word processor, you may be familiar with the concept of an outline. Most word processors (including Microsoft Word) have an outline mode that lets you view only the headings and subheadings in your document. You can easily expand a heading to show the text below it. Using an outline makes visualizing the structure of your document easy.

Excel also is capable of using outlines. Understanding this feature can make working with certain types of worksheets much easier for you.

Introducing Worksheet Outlines

You'll find that some worksheets are more suitable for outlines than others. You can use outlines to create summary reports that don't show all the details. If your worksheet uses hierarchical data with subtotals, it's probably a good candidate for an outline.

The best way to understand how worksheet outlining works is to look at an example. Figure 27.1 shows a simple sales summary sheet without an outline. Formulas are used to calculate subtotals by region and by quarter.

Image described by caption and surrounding text.

Figure 27.1 A simple sales summary with subtotals.

Figure 27.2 shows the same worksheet after I created the outline by selecting the rows and using Data image Outline image Group image Auto Outline. Notice that Excel adds a new section to the left of the screen. This section contains outline controls that enable you to determine which level to view. This particular outline has three levels: States, Regions (each region consists of states grouped into categories such as West, East, and Central), and Grand Total (the sum of each region's subtotal).

Image described by caption and surrounding text.

Figure 27.2 The worksheet after creating an outline.

Figure 27.3 depicts the outline after clicking the 2 button, which displays the second level of details. Now the outline shows only the totals for the regions. (The detail rows are hidden.) You can partially expand the outline to show the detail for a particular region by clicking one of the plus-sign buttons. Collapsing the outline to level 1 shows only the headers and the Grand Total row.

Image described by caption and surrounding text.

Figure 27.3 The worksheet after collapsing the outline to the second level.

Excel can create outlines in both directions. In the preceding examples, the outline is a row (vertical) outline. Figure 27.4 shows the same model after a column (horizontal) outline was added by selecting the columns and using Data image Outline image Group image Auto Outline. When both outlines are in effect, Excel also displays outline controls at the top.

Image described by caption and surrounding text.

Figure 27.4 The worksheet after adding a column outline.

If you create both a row outline and a column outline in a worksheet, you can work with each outline independently of the other. For example, you can show the row outline at the second level and the column outline at the first level. Figure 27.5 shows the model with both outlines collapsed at the second level. The result is a high-level summary table that displays regional totals by quarter.

Image described by caption and surrounding text.

Figure 27.5 The worksheet with both outlines collapsed at the second level.

imageYou can find the workbook used in the preceding examples on this book's website at The file is named outline example.xlsx.

Keep in mind the following points about worksheet outlines:

· A worksheet can have only one outline. If you need to create more than one outline, use a different worksheet.

· You can either create an outline manually or have Excel do it for you automatically. If you choose the latter option, you may need to do some preparation to get the worksheet in the proper format. (The next section covers both methods.)

· You can create an outline either for all data on a worksheet or just for a selected data range.

· You can remove an outline with a single command: Data image Outline image Ungroup image Clear Outline. See “Removing an outline,” later in this chapter.

· You can hide the outline symbols (to free screen space) but retain the outline. I show you how in the “Hiding the outline symbols” section, later in this chapter.

· An outline can have up to eight nested levels.

Worksheet outlines can be quite useful. If your main objective is to summarize a large amount of data, though, you may be better off using a pivot table. A pivot table is much more flexible and doesn't require that you create the subtotal formulas; it does the summarizing for you automatically. The ultimate solution depends on your data source. If you're entering data from scratch, the most flexible approach is to enter it in a normalized table format and create a pivot table.

imageI discuss pivot tables (and normalized data) in Chapter 33, “Introducing Pivot Tables,” and Chapter 34, “Analyzing Data with Pivot Tables.”

Creating an Outline

This section describes the two ways to create an outline: automatically and manually. Before you create an outline, you need to ensure that data is appropriate for an outline and that the formulas are set up properly.

Preparing the data

What type of data is appropriate for an outline? Generally, the data should be arranged in a hierarchy, such as a budget that consists of an arrangement similar to the following:

1. Company

1. Division

1. Department

1. Budget Category

1. Budget Item

In this case, each budget item (for example, airfare and hotel expenses) is part of a budget category (for example, travel expenses). Each department has its own budget, and the departments are rolled up into divisions. The divisions make up the company. This type of arrangement is well suited for a row outline.

The data arrangement suitable for an outline is essentially a summary table of your data. In some situations, your data will be “normalized” data — one data point per row. You can easily create a pivot table to summarize such data; a pivot table is much more flexible than an outline.

imageSee Chapter 33 (“Introducing Pivot Tables”) and Chapter 34 (“Analyzing Data with Pivot Tables”) for more information about pivot tables.

After you create such an outline, you can view the information at any level of detail that you want by clicking the outline controls. When you need to create reports for different levels of management, consider using an outline. For example, upper management may want to see only the division totals, division managers may want to see totals by department, and each department manager needs to see the full details for his department.

Keep in mind that using an outline isn't a security feature. The data that's hidden when an outline is collapsed can easily be revealed when the outline is expanded.

You can include time-based information that is rolled up into larger units (such as months and quarters) in a column outline. Column outlines work just like row outlines, however, and the levels don't have to be time based.

Before you create an outline, you need to make sure that all the summary formulas are entered correctly and consistently. In this context, consistently means that the formulas are in the same relative location. Generally, formulas that compute summary formulas (such as subtotals) are entered below the data to which they refer. In some cases, however, the summary formulas are entered above the referenced cells. Excel can handle either method, but you must be consistent throughout the range that you outline. If the summary formulas aren't consistent, automatic outlining won't produce the results that you want.


If your summary formulas aren't consistent (that is, some are above and some are below the data), you still can create an outline, but you must do it manually.

Creating an outline automatically

Excel can create an outline for you automatically in a few seconds, whereas it may take you ten minutes or more to do the same thing manually.


If you've created a table for your data (by choosing Insert image Tables image Table), Excel can't create an outline automatically. You can create an outline from a table, but you must do so manually.

To have Excel create an outline, move the cell pointer anywhere within the range of data that you're outlining. Then, choose Data image Outline image Group image Auto Outline. Excel analyzes the formulas in the range and creates the outline. Depending on the formulas that you have, Excel creates a row outline, a column outline, or both.

If the worksheet already has an outline, Excel asks whether you want to modify the existing outline. Click Yes to force Excel to remove the old outline and create a new one.


Excel automatically creates an outline when you choose Data image Outline image Subtotal, which inserts subtotal formulas automatically.

Creating an outline manually

Usually, letting Excel create the outline is the best approach. It's much faster and less error prone. If the outline that Excel creates isn't what you have in mind, however, you can create one manually.

When Excel creates a row outline, the summary rows must all be below the data or all above the data; they can't be mixed. Similarly, for a column outline, the summary columns must all be to the right of the data or to the left of the data. If your worksheet doesn't meet these requirements, you have two choices:

· Rearrange the worksheet so that it does meet the requirements.

· Create the outline manually.

You also need to create an outline manually if the range doesn't contain formulas. You may have imported a file and want to use an outline to display it better. Because Excel uses the positioning of the formulas to determine how to create the outline, it can't make an outline without formulas.

Creating an outline manually consists of creating groups of rows (for row outlines) or groups of columns (for column outlines). To create a group of rows, follow these steps:

1. Select the rows that you want to include in the group. One way to do this is to click a row number and then drag to select other adjacent rows.


Don't select the row that has the summary formulas. You don't want these rows to be included in the group.

2. Choose Data image Outline image Group image Group. Excel displays outline symbols for the group.

3. Repeat this process for each group that you want to create. When you collapse the outline, Excel hides rows in the group, but the summary row, which isn't in the group, remains in view.


If you select a range of cells (rather than entire rows or columns) before you create a group, Excel displays a dialog box asking what you want to group. It then groups entire rows or columns based on the range that you select.

You can also select groups of groups to create multilevel outlines. When you create multilevel outlines, always start with the innermost groupings and then work your way out. If you realize that you grouped the wrong rows, you can ungroup the group by selecting the rows and choosing Data image Outline image Ungroup image Ungroup.

Here are keyboard shortcuts you can use that speed up grouping and ungrouping:

· Alt+Shift+right arrow: Groups selected rows or columns

· Alt+Shift+left arrow: Ungroups selected rows or columns

Creating outlines manually can be confusing at first, but if you stick with it, you'll become a pro in no time.

Figure 27.6 shows a worksheet with a three-level outline of this book. I had to create it manually because it has no formulas, just text.

Image described by caption and surrounding text.

Figure 27.6 An outline of this book, created manually.

imageThis workbook is available on this book's website at The file is named book outline.xlsx.

Working with Outlines

This section discusses the basic operations that you can perform with a worksheet outline.

Displaying levels

To display various outline levels, click the appropriate outline symbol. These symbols consist of buttons with numbers on them (1, 2, and so on) or a plus sign (+) or minus sign (–). Refer to Figure 27.5, which shows these symbols for a row and column outline.

Clicking the 1 button collapses the outline so that it displays no detail (just the highest summary level of information), clicking the 2 button expands the outline to show one level, and so on. The number of numbered buttons depends on the number of outline levels. Choosing a level number displays the detail for that level, plus any levels with lower numbers. To display all levels (the most detail), click the highest-level number.

You can expand a particular section by clicking its plus-sign button, or you can collapse a particular section by clicking its minus-sign button. In short, you have complete control over the details that Excel exposes or hides in an outline.

If you prefer, you can use the Hide Detail and Show Detail commands on the Data image Outline group to hide and show details, respectively.


If you constantly adjust the outline to show different reports, consider using the Custom Views feature to save a particular view and give it a name. Then you can quickly switch among the named views. Choose View image Workbook Views image Custom Views.

Adding data to an outline

You may need to add additional rows or columns to an outline. In some cases, you may be able to insert new rows or columns without disturbing the outline, and the new rows or columns become part of the outline. In other cases, you'll find that the new row or column is not part of the outline. If you create the outline automatically, choose Data image Outline image Group image Auto Outline. Excel makes you verify that you want to modify the existing outline. If you create the outline manually, you need to make the adjustments manually as well.

Removing an outline

After you no longer need an outline, you can remove it by choosing Data image Outline image Ungroup image Clear Outline. Excel fully expands the outline by displaying all hidden rows and columns, and the outline symbols disappear. Be careful before you remove an outline, however. You can't make it reappear by clicking the Undo button; you must re-create the outline from scratch.

Adjusting the outline symbols

When you create a manual outline, Excel puts the outline symbols below the summary rows. This can be unintuitive because you need to click the symbol in the row below the section that you want to expand.

If you prefer that the outline symbols appear in the same row as the summary row, click the dialog box launcher in the lower right of the Data image Outline group. Excel displays the dialog box shown in Figure 27.7. Remove the check mark from the Summary Rows Below Detail option, and click OK. The outline will now display the outline symbols in a more logical position.

Settings dialog box with 3 checkboxes for Direction—Summary rows below detail, Summary columns to right of detail, and Automatic Styles. The first two options are selected.

Figure 27.7 Use the Settings dialog box to adjust the position of the outline symbols.

Hiding the outline symbols

The outline symbols Excel displays when an outline is present take up quite a bit of space. (The exact amount depends on the number of levels.) If you want to see as much as possible onscreen, you can temporarily hide these symbols without removing the outline. Press Ctrl+8 to toggle the outline symbols on and off. When the outline symbols are hidden, you can't expand or collapse the outline.


When you hide the outline symbols, the outline still is in effect, and the worksheet displays the data at the current outline level. (That is, some rows or columns may be hidden.)

The Custom Views feature, which saves named views of your outline, also saves the status of the outline symbols as part of the view, enabling you to name some views with the outline symbols and other views without them.