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.
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:
- Go to the Data tab in the ribbon.
- Look for the Outline group, which has icons for Group, Ungroup and Subtotal.
- 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.
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.
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.
With rows 8 through 18 selected, click the Group icon on the Data tab.
Excel adds a minus sign to the left of row 7.
Repeat by grouping rows 4 and 5, and row 21. You will now have a minus sign next to each section.
To collapse all the sections, click the #1 button at the top of the worksheet.
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.
Clicking the #2 level will hide the third level.
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.