Outline (group) data in a worksheet

If you have a list of data you want to group and summarize, you can create an outline of up to eight levels. Each inner level, represented by a higher number in the outline symbols, displays detail data for the preceding outer level, represented by a lower number in the outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. You can create an outline of rows (as shown in the example below), an outline of columns, or an outline of both rows and columns.

Data with a three-level outline

1. To display rows for a level, select the appropriate

Level 1 contains the total sales for all detail rows.
Level 2 contains total sales for each month in each region.
Level 3 contains detail rows — in this case, rows 17 through 20.

2. To expand or collapse data in your outline, click the

outline symbols, or press ALT+SHIFT+= to expand and ALT+SHIFT+- to collapse.

Create an outline of rows

In the Outline group, click the dialog box launcher

Make sure that each column of the data that you want to outline has a label in the first row (e.g., Region), contains similar facts in each column, and that the range you want to outline has no blank rows or columns.
If you want, your grouped detail rows can have a corresponding summary row—a subtotal. To create these, do one of the following:
- Insert summary rows by using the Subtotal command Use the Subtotal command, which inserts the SUBTOTAL function immediately below or above each group of detail rows and automatically creates the outline for you. For more information about using the Subtotal function, see SUBTOTAL function.
- Insert your own summary rows Insert your own summary rows, with formulas, immediately below or above each group of detail rows. For example, under (or above) the rows of sales data for March and April, use the SUM function to subtotal the sales for those months. The table later in this topic shows you an example of this.
By default, Excel looks for summary rows below the details they summarize, but it's possible to create them above the detail rows. If you created the summary rows below the details, skip to the next step (step 4). If you created your summary rows above your detail rows, go to Data >Outline, and then select the dialog box launcher.

The Settings dialog box for outlines

The Settings dialog box opens.

Click the arrow under Group, and then click Auto Outline

In the Settings dialog box, clear the Summary rows below detail checkbox, and then select OK.

Outline your data. Do one of the following: Outline the data automatically

Select a cell in the range of cells you want to outline.
Go to Data >Outline >Group >Group, and then select Auto Outline.

Outline the data manually

Important: When you group outline levels manually, it's best to have all data displayed to avoid grouping the rows incorrectly.

To outline the outer group (level 1), select all of the rows the outer group contains (i.e., the detail rows and if you added them, their summary rows).

Tip: If you select entire rows instead of just the cells, Excel automatically groups by row - the Group dialog box doesn't even open.

Click Rows and then click OK

Note: If you don't need to create any inner groups, skip to step f, below.

For each inner, nested group, select the detail rows adjacent to the row that contains the summary row.

Click Group on the Data tab

Go to Data >Outline >Group >Group.

In the Group dialog box, select Rows, and then select OK. The outline symbols appear beside the group on the screen.

Tip: If you select entire rows instead of just the cells, Excel automatically groups by row - the Group dialog box doesn't even open.

for the group, and then go to Data > Outline > Ungroup > Ungroup.

Important: If you ungroup an outline while the detail data is hidden, the detail rows could remain hidden. To display the data, drag across the visible row numbers adjacent to the hidden rows. Then go to Home > Cells > Format, and point to Hide & Unhide, and then select Unhide Rows.

Create an outline of columns

Make sure that each row of the data that you want to outline has a label in the first column, contains similar facts in each row, and the range has no blank rows or columns.
Insert your own summary columns with formulas immediately to the right or left of each group of detail columns. The table listed in step 4 below shows you an example.

Note: To outline data by columns, you must have summary columns that contain formulas that reference cells in each of the detail columns for that group.

In the Outline group, click the dialog box launcher

If your summary column is to the left of the detail columns, go to Data >Outline, and then select the dialog box launcher.

The Settings dialog box for outlines

The Settings dialog box opens.

Select a cell in the range.
Go to Data >Outline >Group >Group, and then select Auto Outline.

Outline the data manually

Important: When you manually group outline levels, it's best to have all data displayed to avoid grouping columns incorrectly.

To outline the outer group (level 1), select all of the subordinate summary columns, as well as their related detail data.

Click Group on the Data tab

Go to Data >Outline >Group >Group.

Click Group on the Data tab

Go to Data >Outline >Group.

On the Data tab, click Ungroup

You can also ungroup sections of the outline without removing the entire level. Hold down SHIFT while you select the

for the group, and then go to Data > Outline > Ungroup.

If you ungroup an outline while the detail data is hidden, the detail columns can remain hidden. To display the data, drag across the visible column letters adjacent to the hidden columns. Go to Home > Cells > Format, point to Hide & Unhide, and then select Unhide Columns.

Show or hide outlined data

If you don't see the outline symbols

Show or hide the detail data for a group

outline symbols, select the number of the level that you want. Detail data at lower levels is then hidden. For example, if an outline has four levels, you can hide the fourth level while displaying the rest of the levels by selecting

outline symbols. For example, if there are three levels, select

Customize an outline with styles

For outlined rows, Microsoft Excel uses styles such as RowLevel_1 and RowLevel_2 . For outlined columns, Excel uses styles such as ColLevel_1 and ColLevel_2. These styles use bold, italic, and other text formats to differentiate the summary rows or columns in your data. By changing the way each of these styles is defined, you can apply different text and cell formats to customize the appearance of your outline. You can apply a style to an outline either when you create the outline or after you create it.

Do one or more of the following:

Automatically apply a style to new summary rows or columns

In the Outline group, click the dialog box launcher

Data

Outline

The Settings dialog box for outlines

The Settings dialog box opens.

Apply a style to an existing summary row or column

In the Outline group, click the dialog box launcher

Select the cells to which you want to apply a style.
Go to Data >Outline, and then select the dialog box launcher.

The Settings dialog box for outlines

The Settings dialog box opens.

Settings dialog box with Automatic Styles selected

Select the Automatic styles check box, and then select Apply Styles.

You can also use autoformats to format outlined data.