Grouping in Excel
You can create groups by either manually selecting the rows and columns to include, or you can get Excel to automatically detect groups of data. Groups can also be nested inside other groups to create a multi-level hierarchy. Once your data is grouped, you can individually expand and collapse groups, or you can expand and collapse all groups at a given level in the hierarchy.
Groups provide a really useful way to navigate and view large and complex spreadsheets. They make it much easier to focus on the data that’s important. If you need to make sense of complex data you should definitely be using Groups and could also benefit from Power Pivot For Excel.
How to Use Excel to Group Rows Manually
To make Excel group rows, the simplest method is to first select the rows you want to include, then make them into a group.
For the group of rows you want to group, select the first row number and drag down to the last row number to select all the rows in the group. Select the Data tab > Group > Group Rows, or simply select Group, depending on which version of Excel you’re using. A thin line will appear to the left of the row numbers, indicating the extent of the grouped rows. Select the minus (-) to collapse the group. Small boxes containing the numbers one and two also appear at the top of this region, indicating the worksheet now has two levels in its hierarchy: the groups and the individual rows within the groups. The rows have been grouped and can now be collapsed and expanded as required. This makes it much easier to focus on just the relevant data.
How to Manually Group Columns in Excel
To make Excel group columns, the steps are almost the same as doing so for rows.
For the group of columns you want to group, select the first column letter and drag right to the last column letter, thereby selecting all the columns in the group. Select the Data tab > Group > Group Columns, or select Group, depending on which version of Excel you’re using. A thin line will appear above the column letters. This line indicates the extent of the grouped columns. Select the minus (-) to collapse the group. Small boxes containing the numbers one and two also appear at the top of this region, indicating the worksheet now has two levels in its hierarchy for columns, as well as for rows. The rows have been grouped and can now be collapsed and expanded as required.
How to Make Excel Group Columns and Rows Automatically
While you could repeat the above steps to create each group in your document, Excel can automatically detect groups of data and do it for you. Excel creates groups where formulas reference a continuous range of cells. If your worksheet doesn’t contain any formulas, Excel won’t be able to automatically create groups.
Select the Data tab > Group > Auto Outline and Excel will create the groups for you. In this example, Excel correctly identified each of the groups of rows. Because there’s no annual total for each spending category, it has not automatically grouped the columns.
How to Create a Multi-Level Group Hierarchy in Excel
In the previous example, categories of income and expense were grouped together. It would make sense to also group all of the data for each year. You can do this manually by applying the same steps as you used to create the first level of groups.
Select all of the rows to be included. Select the Data tab > Group > Group Rows, or select Group, depending on which version of Excel you are using. Another thin line will appear to the left of the lines representing the existing groups and indicating the extent of the new group of rows. The new group encompasses two of the existing groups and there are now three small numbered boxes at the top of this region, signifying the worksheet now has three levels in its hierarchy. The spreadsheet now contains two levels of groups, with individual rows within the groups.
How to Automatically Create Multi-Level Hierarchy
Excel uses formulas to detect multi-level groups, just as it uses them to detect individual groups. If a formula references more than one of the other formulas which define groups, this indicates these groups are part of a parent group.
Keeping with the cash flow example, if we add a Gross Profit row to each year, which is simply the income minus the expenses, then this allows Excel to detect that each year is a group and the income and expenses are sub-groups within these. Select the Data tab > Group > Auto Outline to automatically create these multi-level groups.
How to Expand and Collapse Groups
The purpose of creating these groups of rows and/or columns is that it allows regions of the spreadsheet to be hidden, providing a clear overview of the entire spreadsheet.
To collapse all of the rows, select the number 1 box at the top of the region to the left of the row numbers. Select the number two box to expand the first level of groups and make the second level of groups visible. The individual rows within the second level groups remain hidden. Select the number three box to expand the second level of groups so the individual rows within these groups also become visible. It’s also possible to expand and collapse individual groups. To do so, select the Plus (+) or Minus (-) that appears to mark a group that’s either collapsed or expanded. In this way, groups at different levels in the hierarchy can be viewed as required.
