Excel tip: Grouping worksheet sections

Microsoft Excel consultant Bill Jelen, aka MrExcel, explains the value of the Grouping feature.

In 2012, I was a judge for the ModelOff world financial modeling championships in New York City. Sixteen finalists...

from around the world competed for cash prizes. Since I had access to 16 of the fastest Excellers in the world, I spent some time asking them for their best tips.

One finalist made frequent use of a Microsoft Excel feature called Grouping. All of his worksheets used an outline structure where you could quickly expand or collapse various sections of the worksheet.

Consider the typical income statement in Figure 1.

Figure 1: Excel income statement
Figure 1

You can simplify the display of the worksheet by creating an outline.

If you've ever tried to use the Group icon on the Data tab in the ribbon, you might have experienced difficulty getting the feature to work. This is because the Excel team at Microsoft expects the heading for each section to be at the bottom, which does not often happen in the real world.

Luckily, there is a setting to reverse how Grouping works:

  1. Go to the Data tab in the ribbon.
  2. Look for the Outline group, which has icons for Group, Ungroup and Subtotal.
  3. In the lower right corner of the Outline group is a dialog launcher, as shown in Figure 2. Click this tiny symbol to open the Settings dialog box.

Figure 2: dialog launcher
Figure 2

The first checkbox in Settings is Summary rows below detail. This is the default setting in a new workbook. Turn the setting off and click OK.

Figure 3: summary row below detail
Figure 3

Now that you've changed the default grouping setting, use these next steps to create a collapsible section of the report. In the figure below, rows 8 through 18 contain the detail for the heading in row 7. Leave the summary row alone, but select rows 8 through 18.

Figure 4: operating expenses
Figure 4

With rows 8 through 18 selected, click the Group icon on the Data tab.

Figure 5: Excel Group
Figure 5

Excel adds a minus sign to the left of row 7.

Figure 6: operating expenses
Figure 6

Repeat by grouping rows 4 and 5, and row 21. You will now have a minus sign next to each section.

Figure 7: net income
Figure 7

To collapse all the sections, click the #1 button at the top of the worksheet.

Figure 8: number 1 button
Figure 8

It is possible to create additional levels of grouping. In Figure 9, rows 13:14 are grouped to create a new sub-level. Note there are now level buttons at the top for 1, 2, and 3.

Figure 9: Excel Grouping
Figure 9

Clicking the #2 level will hide the third level.

Figure 10: collapse report rows
Figure 10

You can now use the +, -, and level buttons to quickly expand or collapse sections of the report.

Bill Jelen, aka MrExcel, has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

Dig Deeper on ERP accounting software