Grouping in Excel lets you collapse or expand sections of a sheet so you can see the ‘big picture’ but still drill-down to how totals were made. Another use for grouping is hiding rows or columns from view.
You can organize and manage data more effectively by grouping together rows or columns. This feature is particularly useful for complex spreadsheets where data needs to be organized structurally or for presentations where data needs to be summarized or hidden. The rows or columns of the group collapse on minimizing and expand on maximizing.
You can hide the details (i.e. rows) for a simpler view but still drill-down with a click to reveal what made up that subtotal. Here we’ve left Australia expanded with the details for other countries collapsed. See the grouping indicators at left.
Instead of scrolling through extensive rows of data, users can collapse sections and concentrate exclusively on relevant information.
Let’s look at a simple example of how to group data in Excel manually:
Grouping Manually
The sample Excel spreadsheet displays details such as country names, products, units sold, prices, gross sales, COGS, and profits. We have the option to either consolidate the countries into one group or categorize the products into specific categories to streamline the data.
Now from the sample spreadsheet, let’s try grouping manually. (Note that we have added the subtotals manually for this example)
To group Country, Select the range (row-wise) to group. From the spreadsheet to group Australia, select the range from 2 until row 9 . Then Navigate to Data |Outline | Group and Click the downward arrow under Group. Click Group.
Clicking Group displays a dialog box titled Group with Rows and Column radio button giving you the option to organize your data either by rows or columns. Since we are grouping the data row-wise, select “rows” option and click Ok.
Keyboard shortcut: Shift + Alt + Right Arrow to group a selection.
Tip: you can skip the rows/columns question by selecting whole rows or columns for grouping.
This will add a bar on the left side of the worksheet extending across the selected rows. Now repeat the manual grouping process for the remaining countries.
Collapse/Expand all groups
Positioned at the top left-hand corner of your grouped data spreadsheet, there are two numbers adjacent to the name box. Inside these boxes, you’ll find the numbers 1 and 2.
When you click on 1 you can see the Group summary displaying each country’s total sales.
Clicking on 2 expands the table and reveals the breakup of each Country group, as shown below.
Excel also provides the option to hide detailed rows by using the collapse rows feature: click on the Minus (-) sign on the outlines to simplify data by collapsing detailed data. When you collapse an outline bar, the Minus (-) sign changes to the Plus (+) sign, allowing you to unhide rows later.
Alternatively, you can also utilize the Show Detail and Hide Detail buttons available in the Outline section of the Data tab. These buttons offer additional control over displaying or hiding detailed data within your worksheet.
Columns too!
Most explanations of Excel grouping focus on rows but grouping can be done for columns too!
Group instead of hiding
Excel grouping is designed for combining details that make up a sub-total, as we’ve shown above. Another use is instead of hide/unhide rows or columns.
For example, use a group to collapse older transactions in a table so you can focus on recent events. The left/top group indicators are clearer than the almost invisible hidden rows/columns.
That’s the basics of grouping. In future articles I’ll look at multiple grouping levels, styles for groups, auto-outlining and more …
