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.
How to Manually Group Columns in Excel
To make Excel group columns, the steps are almost the same as doing so for rows.
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.
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.
