Step-by-Step Guide: How to Create Dynamic Summaries by Grouping/Collapsing Excel Rows

Quick Links

Key Takeaways

Select the rows you want to group and go to the Data tab. In the Outline drop-down menu, select “Group.” Use the plus (+) and minus (-) or number buttons that appear to collapse and expand each group.

Viewing only the data you want to see can be tedious when you’re working with a large spreadsheet. Luckily, Excel offers various tools you can use to simplify your data. For example, you can easily group rows in Excel and then collapse or expand your data as needed.

How to Group Rows in Excel

To group by rows in Excel, start by selecting the adjacent rows you want to use. You can do this by dragging your cursor through the row headers or by selecting the first row, holding Shift, and then selecting the last row in the range.

Go to the Data tab and select the Outline drop-down arrow. Pick “Group” in the menu.

You’ll then see your selected rows grouped together, ready to be collapsed and expanded when you need them to be.

How to Collapse a Grouped Row

Note the buttons on the left side of your grouped rows. You’ll use these buttons to collapse and expand your group.

To collapse the group, click the minus (-) sign or button 1. To expand the group again, click the plus (+) sign or button 2.

How to Use Subgroups, Additional Groups, and Subtotals

You can create more than one group in a sheet. You can also create subgroups or use the subtotal feature to both group and add calculations .

To create a subgroup, expand the parent group and then follow the same steps as above to select and group rows.

When you create the subgroup, you’ll see another level of plus (+) and minus (-) buttons and an additional button labeled 3.

You would then use the second set of plus (+) and minus (-) buttons along with buttons 1, 2, and 3 to collapse or expand the entire group and subgroup.

To create another group in the sheet, follow the same steps as earlier.

Keep in mind that you can’t create two groups adjacent to one another or Excel will put them into the same group. Be sure to leave a row between groups or use the above subgroup option.

To create groups and subtotal them at the same time, select all rows you want in the group. Go to the Data tab and pick “Subtotal” in the Outline drop-down menu.

Use the options in the Subtotal window to choose the grouping, function for the calculation , and which column to use for the figures. Mark any additional options you want and click “OK.”

You’ll then see your rows grouped and calculated as you instructed. Plus, you’ll have an overall total at the bottom for whatever function you selected. For example, you may see Grand Total, Grand Average, or similar.

How to Ungroup Rows in Excel

If you want to remove the grouping you’ve created, you can do so for both groups and subgroups.

Select the group by dragging through the rows . Go to the Data tab, select the Outline drop-down arrow, and pick “Ungroup.”

You’ll then see the group return to its original view.

You can also use the Ungroup option to remove only certain rows from a group. For example, if you have rows 2 through 10 in a group but want to remove rows 8 through 10, you can do so.

Select the rows you want to remove from the group. Then, select Outline > Ungroup on the Data tab.

Those rows are then removed from the group while the rest of the rows remain in it.

You can then collapse and expand your remaining group without affecting the rows you removed.

If you want to group rows in Excel for easier viewing, try the grouping feature in your spreadsheet.

For more, check out how to insert multiple rows or how to move rows in Excel .

Win11 Tips

Step-by-Step Guide: How to Create Dynamic Summaries by Grouping/Collapsing Excel Rows