[Ultimate Guide] How to Group Rows and Columns in Excel (2021)

How to group in

This tutorial is all about grouping rows and columns in Excel and a step-by-step guide to group rows and columns. Groups can be used to temporarily hide or unhide a part of data for ease in analysis and reporting.

If you wish to hide a selective portion from a database, then Group tool in excel can be useful for you.

Steps to group rows and columns from scratch

Let us get started with the ultimate guide to grouping rows and columns from scratch with supporting images.

Create/ Open a database

First, you need to have a set of data ready that you want to group. We have an existing database available here as an example-

List of Flowers (Sample Database) Group Rows and Columns in Excel
List of Flowers (Sample Database)

Here, our goal is to temporarily hide three entries from this list above i.e., Daffodils, Dandelions and Water Lily.

Steps to group rows in Excel

To achieve this, we need to group the rows of the entries. Here’s how-

Group Settings Group Rows and Columns in Excel
Group Settings
  1. Select the rows (Row numbers- 5, 6 & 7) you want to group.
  2. Go to the Data tab.
  3. Under the Outline section, select Group.
  4. A dialogue box pops up where you can select to group the selected rows or columns. Select Rows.
  5. Click OK. Your rows are successfully grouped.

This is how grouped rows look like-

Grouped Rows
Grouped Rows

You can observe that the grouped rows and marked by a grey line and dots. You can also notice a minus sign at the end of the line which is meant to hide the grouped entries from the data.

6. Click minus to hide the entries and plus to unhide them again.

7. Your entries have now been hidden temporarily.

Steps to add new rows to an existing group

You can also add new adjacent row to the existing group as follows-

Selected Ungrouped Row for Grouping
Selected Ungrouped Row for Grouping
  1. Select the adjacent ungrouped row below or above a grouped row.
  2. Go to Data tab, under the Outlines section, press Group.
  3. Select Rows in the Group window.
  4. Hit OK.

The formerly ungrouped row now gets added with the grouped rows. Here is the result-

New row in existing group
New row in existing group

Steps to group columns in Excel

The settings are the same for grouping columns together. You must simply select Columns instead of Rows in Group settings. Here is an example-

Selected Columns in a Database for Grouping
Selected Columns in a Database for Grouping
  1. Select the cells of columns (Age, Dept., Income 2019 & Income 2020) to be grouped.
  2. Go to Data tab, under the Outlines section, press Group.
  3. Select Columns in the Group window.
  4. Hit OK.

You can now only view income data of 2021 for every employee by conveniently hiding other metrics temporarily. Here is an illustration of the end result-

Grouped Columns in Database
Grouped Columns in Database

Conclusion

This article was all about the grouping rows and columns in Microsoft Excel to hide or unhide a group of data for proper readability and convenience in reporting or comparing. If you have any questions on grouping, feel free to post a comment and we will be back with an answer for all your queries!