Group and Ungroup Rows and Columns in Excel

Outlining and grouping rows or columns in Excel makes it easy to organize and declutter spreadsheets that have a ton of data on display. Say you have several rows with the sales figures by month and the last row is a summation of the data, rather than display all months you can group the data (months) and only display the row with the totals. This can be extremely useful when you are working with a spreadsheet that has multiple years of data, think of it, if you had 7 years of data by month – that would amount to 84 rows of months and 7 with totals …instead of dizzying someone with all that data you can group it and display the totals per year and show off your skills with Excel.

If you have any comments or questions please post them below.

Note: I used Excel 2007 when creating this tutorial.

Grouping Rows

I will show you the two methods that can be used to add comments to cells in Excel, one involves the right-click menu and the other the Office Ribbon menu.

Use Comments in Excel Spreadsheets

1. Select a set of rows that you wish to group together.

Note: You can do the same for columns.

Use Comments in Excel Spreadsheets

2. Click on the Data tab located in the Ribbon

3. Click on the Group button located in the Outline Group

4. Click on Group… from the menu.


Use Comments in Excel Spreadsheets

You will notice that Excel created a new margin with a set of dots next to each row, you will also notice that it created a small box with a minus sign indicating that the group is expanded.

a. Click on the minus sign (-) to collapse the group.

Use Comments in Excel Spreadsheets

a. When you clicked on the minus sign (), it collapsed the group and changed it to a plus sign (+).

b. Notice how the margin has a 1 and 2. This indicates that there is a single level grouping. If you create additional groups within the one we just created, the margin would show a 1, 2, and 3. This helps in knowing the levels of grouping in a particular sheet. You can have up to 8 levels of grouping.

Ungrouping Rows

Use Comments in Excel Spreadsheets

1. Make sure you select the groups that you wish to ungroup, do this by selecting the rows or columns that are grouped.

2. Click on the Data tab located in the Ribbon.

3. Click on the Ungroup button located in the Outline Group

4. Click Ungroup… from the menu.

Use Comments in Excel Spreadsheets

a. You can Expand a group by clicking on the Show Detail button located in the Outline Group.

b. You can Collapse a group by clicking on the Hide Detail button located in the Outline Group.