How to Group in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office
How to Group in Excel
Get a handle on your data
By Jody Emlyn Muelaner Jody Emlyn Muelaner Writer University of Bath Dr. Jody Muelander is a former freelance contributor to Lifewire who's writing has appeared in peer-reviewed journals and aerospace industry reports. lifewire's editorial guidelines Updated on July 15, 2020 Tweet Share Email Tweet Share Email In This Article
Expand Jump to a Section Grouping in Excel Group Rows Manually Group Columns Manually Group Columns and Rows Automatically Multi-Level Group Hierarchies Create Multi-Level Hierarchy Automatically Expand and Collapse Groups Grouping rows and columns in Excel lets you collapse and expand sections of a worksheet. This can make large and complex datasets much easier to understand. Views become compact and organized. This article shows you step-by-step how to group and view your data. Instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Microsoft 365, Excel Online and Excel for Mac. 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. This option isn’t available in Excel Online, if you’re using Excel Online, you'll need to create groups manually. 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. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Limit Rows and Columns in an Excel Worksheet How to Combine the ROUND and SUM Functions in Excel How to Hide and Unhide Columns, Rows, and Cells in Excel How to Freeze Column and Row Headings in Excel How to Make a Schedule in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function How to Create a Report in Excel How to Merge and Unmerge Cells in Excel How to Use the Excel INDEX Function How to Group Tabs in Chrome Relative, Absolute, and Mixed Cell References in Excel and Sheets How to Print Labels from Excel Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Embed Excel Files in Word Documents Splitting the Screen in Excel How to Create a Histogram in Excel for Windows or Mac Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies