MrExcel.com - Your One Stop for Excel Tips & Solutions

Group Report Sections

September 22, 2022 - by

Group Report Sections

Problem: The grouping feature feels backwards. What if I have report headings above each section and I need to group the data below the heading?

Strategy: I picked up this great trick from Mack Wilk, one of the two-time ModelOff World Financial Modeling finalists. There is an obscure setting that makes grouping work the way you want it to work. Mack uses this trick in his models, with multiple levels of grouping. It creates an uncluttered view of the model.

  • 1. On the Data tab, click the Dialog Launcher in the corner of the Outline group.

Click the Dialog Launcer arrow in the bottom right corner of the Outline group.
Figure 706. Open the dialog launcher.

  • 2. In the Settings dialog, uncheck Summary Rows Below Detail.

Uncheck Summary Rows Below Detail. Leave Summary Columns To The Right of Detail selected.
Figure 707. Uncheck Summary Rows Below Detail.

  • 3. Select the rows underneath the heading for section 1.

How to set up manual grouping. There is a Heading For Section 1 in Row 1 and a Heading for Section 2 in Row 6. That means all of the data in rows 2 through 5 apply to Section 1. Select A2:A5 and then create a group.
Figure 708. Select the rows for Section 1, excluding the heading.

  • 4. Press Shift+Alt+RightArrow to group the selection. Repeat for the rows for each section.

Gotcha: When you mistakenly press Ctrl+Alt+Right arrow, your display may turn sideways (as if you were going to mount your monitor in a portrait fashion). Press Ctrl+Alt+Up arrow to return the monitor to the correct orientation.

You will now have group and outline buttons to collapse all sections. Use one of the + icons to display any section.

After creating groups for Section 1, Section 2, and Section 3, you will have two Group and Outline buttons to the left of column A and then each individual section has a Collapse button (a minus sign). Once you have collapsed a section, the Collapse icon changes to an Expand icon (a plus sign).
Figure 709. You can easily expand or collapse any section.

This article is an excerpt from Power Excel With MrExcel

Title photo by Erik Karits on Unsplash