Outline (group) data in a worksheet

1. 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.

2. 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.

3. 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 opens.

   

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

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

   Outline the data automatically

   1. Select a cell in the range of cells you want to outline.

   2. Go to Data > Outline > Group > Group, and then select Auto Outline.

      

   Outline the data manually

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

   1. 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).

      1. The first row contains labels, and is not selected. 2. Since this is the outer group, select all the rows with subtotals and details. 3. Don't select the grand total.

   2. Go to Data > Outline > Group > Group. In the Group dialog box, select Rows, and then select OK.

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

      

      The outline symbols appear beside the group on the screen.

   3. Optionally, outline an inner, nested group — the detail rows for a given section of your data.

      : 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.

      1. You can create multiple groups at each inner level. Here, two sections are already grouped at level 2. 2. This section is selected and ready to group. 3. Don't select the summary row for the data you are grouping.

   4. 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.

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

   5. Continue selecting and grouping inner rows until you have created all of the levels that you want in the outline.

   6. If you want to ungroup rows, select the rows, and then go to Data > Outline > Ungroup > Ungroup.

      You can also ungroup sections of the outline without removing the entire level. Hold down SHIFT while you select the or for the group, and then go to Data > Outline > Ungroup > Ungroup.

      : 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.

1. 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.

2. 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.

   : 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.

3. 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 opens.

   

   In the Settings dialog box, clear the Summary columns to right of detail check box, and then select OK.

4. To outline the data, do one of the following:

   Outline the data automatically

   1. Select a cell in the range.

   2. Go to Data > Outline > Group > Group, and then select Auto Outline.

   Outline the data manually

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

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

      1. Column A contains labels. 2. Select all the detail and subtotal columns. Note that if you don't select entire columns, when you select Group (Data > Outline > Group) the Group dialog box opens and asks you to choose Rows or Columns. 3. Don't select the grand total column.

   2. Go to Data > Outline > Group > Group.

      

      The outline symbol appears above the group.

   3. To outline an inner, nested group of detail columns (level 2 or higher), select the detail columns adjacent to the column that contains the summary column.

      1. You can create multiple groups at each inner level. Here, two sections are already grouped at level 2. 2. These columns are selected and ready to group. Note that if you don't select entire columns, when you select Group (Data > Outline > Group) the Group dialog box opens and asks you to choose Rows or Columns. 3. Don't select the summary column for the data you are grouping.

   4. Go to Data > Outline > Group.

      

      The outline symbols appear beside the group on the screen.

5. Continue selecting and grouping inner columns until you have created all of the levels that you want in the outline.

6. If you want to ungroup columns, select the columns, and then go to Data > Outline > Ungroup.

You can also ungroup sections of the outline without removing the entire level. Hold down SHIFT while you select the or 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.

1. If you don't see the outline symbols , , and , go to File > Options > Advanced, and under the Display options for this worksheet section, select the Show outline symbols if an outline is applied check box, and then select OK.

2. Do one or more of the following:

   • Show or hide the detail data for a group    

     To display the detail data within a group, select the button for the group, or press ALT+SHIFT+=.

   • To hide the detail data for a group, select the button for the group, or press ALT+SHIFT+-.

   • Expand or collapse the entire outline to a particular level    

     In the 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  .

   • Show or hide all of the outlined detail data    

     To show all detail data, select the lowest level in the outline symbols. For example, if there are three levels, select  .

   • To hide all detail data, select  .

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    

1. Go to Data > Outline, and then select the dialog box launcher.

   

   The Settings dialog box opens.

   

2. Select the Automatic styles check box.

Apply a style to an existing summary row or column    

1. Select the cells to which you want to apply a style.

2. Go to Data > Outline, and then select the dialog box launcher.

   

   The Settings dialog box opens.

   

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

   

You can also use autoformats to format outlined data.

1. If you don't see the outline symbols , , and , go to File > Options > Advanced, and then under the Display options for this worksheet section, select the Show outline symbols if an outline is applied check box.

2. Use the outline symbols , , and to hide the detail data that you don't want copied.

   For more information, see the section, Show or hide outlined data.

3. Select the range of summary rows.

4. Go to Home > Editing > Find & Select, and then select Go to Special.

   

5. Select Visible cells only.

6. Select OK, and then copy the data.

Hide an outline

• Go to File > Options > Advanced, and then under the Display options for this worksheet section, uncheck the Show outline symbols if an outline is applied check box.

Remove an outline

1. Click the worksheet.

2. Go to Data > Outline > Ungroup, and then select Clear Outline.

   

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

Imagine that you want to create a summary report of your data that only displays totals accompanied by a chart of those totals. In general, you can do the following:

1. Create a summary report

   1. Outline your data.

      For more information, see the sections Create an outline of rows or Create an outline of columns.

   2. Hide the detail by selecting the outline symbols , , and to show only the totals as shown in the following example of a row outline:

      

   3. For more information, see the section, Show or hide outlined data.

2. Chart the summary report

   1. Select the summary data that you want to chart.

      For example, to chart only the Buchanan and Davolio totals, but not the grand totals, select cells A1 through C19 as shown in the above example.

   2. Select Insert > Charts > Recommended Charts, then select the All Charts tab and choose your chart type.

      

      For example, if you chose the Clustered Column option, your chart would look like this:

      

      If you show or hide details in the outlined list of data, the chart is also updated to show or hide the data.