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 with some buttons on the left side. 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, click the plus (+) sign or button 2.
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.
RELATED: How to Calculate a Loan Payment, Interest, or Term in Excel
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.
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.
Ungroup Rows in Excel
If you want to remove the grouping you’ve created, you can do so for both groups and subgroups.
RELATED: How to Add and Remove Columns and Rows in Microsoft Excel
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.
