Know How to Group Rows or Columns in Microsoft Excel

Do you need to group Rows or Columns in Excel to organize your data in the spreadsheet? When you are dealing with a large worksheet you have to hide the details and show only the summary as an outline and give an option to the user to expand and see more details.

Excel’s grouping and outlining features let you choose how much detail is displayed on the screen by hiding or showing rows and columns.

Actually your worksheet data has to be structured in a certain way that works with the grouping capability if you want to utilise the group function in Excel.

Grouping Rows or Columns

In Excel, you can group rows and columns together to make it easier to manipulate and work with your data. To group rows or columns together, select the rows or columns that you want to group, then click the Group button on the toolbar

Suppose you have the data set as below and need to group Category Subtotal in Rows and Qtr in columns.

How to group Rows in Excel

Select the Rows you need to group eg. Row 2 to 6. Go to Data menu and select Group. Repeat the same for the other set of Rows 8 to 12, 14 to 18, and 20 to 24.

How to group Columns in Excel

Select the Columns you need to group eg. Columns C to E. Go to Data menu and select Group. Repeat the same for the other set of Columns G to I, K to M, and O to Q.

Ungrouping Rows or Columns

You can also ungroup rows and columns by selecting them and clicking the Ungroup button on the toolbar.

For example: To ungroup rows, Select the rows 2 to 8, Go to Data menu and select Ungroup. Select the Columns C to E and Select Ungroup to ungroup the grouped columns in excel.

How to use Auto-Outline to group rows and columns

Auto outline will help you to quickly groups Rows and Columns based on the summarised rows and column of your data.

In the above example just select any cell in the data set. Go to Data menu and select Auto-Outline in the Group drop down. You can see the Excel automatically grouped rows and columns.

To clear the outline you can use the clear outline in Ungroup drop down item.

Conclusions

Groups and Ungroups will help you to create summary table and the user to interact to see the details of the data. It helps to hide the detailed information in excel and display your report in a compact format.

Top level managers no need to see more granular level of the data. They need to see only the summary reports to take the decisions.

If you have any questions, please leave a comment below and I will get back to you as soon as possible.