Subscribe Us

Enter your email address:

Loading

Outline (group) data in a worksheet

Labels: , ,

If you have a list of data that you want to group and summarize, you can create an outline of up to eight levels, one for each group. 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.

Outlined list
An outlined row of sales data grouped by geographical regions and months with several summary and detail rows displayed.
Callout 1  To display rows for a level, click the appropriate one two three outline symbols.
Callout 2  Level 1 contains the total sales for all detail rows.
Callout 3  Level 2 contains total sales for each month in each region.
Callout 4  Level 3 contains detail rows (only detail rows 11 through 13 are currently visible).
Callout 5  To expand or collapse data in your outline, click the plus and minus outline symbols.

Create an outline of rows

  1. Make sure that each column has a label in the first row, contains similar facts in each column, and that the range has no blank rows or columns.
  2. Select a cell in the range.
  3. Sort the columns that form the groups.
  1. Insert summary rows.
To outline data by rows, you must have summary rows that contain formulas that reference cells in each of the detail rows for that group. 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.
Insert your own summary rows    
  • Insert your own summary rows with formulas immediately below or above each group of detail rows.
  1. Specify whether the location of the summary row is below or above the detail rows.
  1. On the Data tab, in the Outline group, click the Outline Dialog Box Launcher.
Outlook Ribbon Image
  1. To specify a summary row above the details row, clear the Summary rows below detail check box. To specify a summary row below the details row, select the Summary rows below detail check box.
  1. Outline the data. Do one of the following:
  1. If necessary, select a cell in the range.
  2. On the Data tab, in the Outline group, click the arrow next to Group, and then click Auto Outline.
Outlook Ribbon Image
 Important    When you manually group outline levels, it's best to have all data displayed to avoid grouping the rows incorrectly.
  1. Outline the outer group.
  1. Select all of the subordinate summary rows, as well as their related detail data.
In the example below, row 6 contains the subtotals for rows 2 through 5, and row 10 contains the subtotals for rows 7 through 9, and row 11 contains the grand totals. To group all of the detail data for row 11, select rows 2 through 10.
A B C
1 Region Month Sales
2 East March $9,647
3 East March $4,101
4 East March $7,115
5 East March $2,957
6 East Mar Total $23,820
7 East April $4,257
8 East April $1,829
9 East April $6,550
10 East Apr Total $12,636
11 East Total $36,456
 Important   Do not include the summary row 11 in the selection.
  1. On the Data tab, in the Outline group, click Group.
Outlook Ribbon Image
The outline symbols appear beside the group on the screen.
  1. Optionally, outline an inner, nested group.
  1. For each inner, nested group, select the detail rows adjacent to the row that contains the summary row.
In the example below, to group rows 2 through 5, which has a summary row 6, select rows 2 through 5. To group rows 7 through 9, which has a summary row 10, select rows 7 through 9.
A B C
1 Region Month Sales
2 East March $9,647
3 East March $4,101
4 East March $7,115
5 East March $2,957
6 East Mar Total $23,820
7 East April $4,257
8 East April $1,829
9 East April $6,550
10 East Apr Total $12,636
11 East Total $36,456
 Important   Do not include the summary row for that group in the selection.
  1. On the Data tab, in the Outline group, click Group.
Outlook Ribbon Image
The outline symbols appear beside the group on the screen.
  1. Continue selecting and grouping inner rows until you have created all of the levels that you want in the outline.
  2. If you want to ungroup rows, select the rows, and then on the Data tab, in the Outline group, click Ungroup.
 Note   You can also ungroup sections of the outline without removing the entire outline. Hold down SHIFT while you click the Plus box or Minus box for the group, and then on the Data tab, in the Outline group, click Ungroup.
 Important   If you ungroup an outline while the detail data is hidden, the detail rows may remain hidden. To display the data, drag across the visible row numbers adjacent to the hidden rows. On the Home tab, in the Cells group, click Format, point to Hide & UnHide, and then click Unhide Rows.

'