Using grouping to see the big picture

When we develop more sections of our model, it can start to be difficult to maintain an overview. Grouping can help with this.

The time sheet in our start model already has quite a lot of sections. Although the heading in Columns A and B help us to navigate, it's hard to avoid getting lost in the weeds.

With everything visible we can see all the detail we need, but not the big picture:

With everything grouped, we avoid having to look at the detail and can maintain a better sense of the big picture:

Unfortunately, the native tools within Excel for working with groups don't make our lives as easy as they should. We have therefore added some additional tools into the Productivity Pack to improve your workflow around groups.

Adding grouping

We apply grouping consistently by Column A and Column B headers and have therefore automated this. You will find that as you work on a sheet, adding blocks and moving blocks around, your grouping will start to fall apart. When this happens and you want to reapply grouping using the current heading structure, 1st active the Utilities dialogue:

Then choose option 3 to reapply "level 1 and 2" grouping.

Expanding or collapsing a single group

While working in the model, you may want to expand or collapse a single group. The native Excel shortcuts for this are cumbersome. There are two Productivity pack macros to help you. The keystrokes work according to where you are in a worksheet.

Expand group

In the example below, all of the blocks in the Time sheet are collapsed. This helps me to see the big picture.

If I want to see the detail on one of the blocks, I position myself on the block header:

I use the Ctrl+e keystroke to expand that group.

Collapse group

If I want to collapse the same group, I use Ctrl+shift+e anywhere in the block.

These keystrokes are designed to work on single blocks as part of your model build workflows.

There are also utilities to expand or collapse all groups across the model.

Note that these macros don't select the rows to be grouped or ungrouped.

Expand or collapse all groups on selected sheets.

There are times when you may want to expand or collapse all groups across a selection of sheets. We would often use this when cleaning up a model to send out. When people receive a model with everything grouped, it can help them not get bogged down in overwhelming detail when they first look at it.

Open the Utilities dialogue.

Select the sheets you want to apply the grouping to from the list on the left of the dialogue. Use Alt+a to select all the sheets.

Then select option 6 to collapse all the grouping, or option 7 to expand. This action will be performed on a grouping on all the selected sheets.

Comments

Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Send a log in link Great! Please check your inbox (or spam folder) for a log in link. Something didn't work. Please try again.