Group in Excel

What Is Grouping In Excel?

Grouping in Excel allows us to collapse or hide multiple columns or rows to display a more structured and organized dataset. The Group in Excel is commonly known as Outlines. This is because we can use an outline of rows and columns to display just the summary or the complete data of the dataset.

We can select the Group in Excel as follows:

Select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down.

A “Group” window pops up, as shown below.

• If we select the “Rows” option, we get a bar beside the selected rows.

• If we select the “Columns” option, we get the bar above the selected columns.

How To Group Rows In Excel?

We can Group Rows in Excel in two ways,

• Group Rows Automatically [Outline].
• Group Rows Manually [Nested Groups].

Group Rows Automatically [Outline]

We will learn to Group Rows Automatically in Excel.

In the table, the data is as follows:

• Column A contains the Name.
• Column B contains the Annual Salary.
• Column C contains the Taxability.

The steps to Group Rows in Excel Automatically are as follows:

Step 1: Select cell B7. This cell calculates the Total Annual Salary.

Step 2: Select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down > click on the “Auto Outline” option. The Group Rows form, with a bar beside the selected rows, as shown below.

Step 3: Now, click on the “-” sign on the bar, then only the total amount will be visible on the screen, as shown in the below adjoining image.

Step 4: Next, click on the “+” sign on the bar, then all the hidden cells will be visible once again, as shown in the below adjoining image.

Group Rows Manually [Nested Groups]

We will learn to Group Rows Manually using the shortcut key in Excel “Shift+Alt+Right”.

[Shift > “Shift” key, Alt > “Alt” key, and Right > “Right Arrow” key”]

In the table, the data is as follows:

• Column A contains the Regions.
• Column B contains the Items.
• Column C contains the Amount in Stock.

The steps to Group Rows in Excel manually are as follows:

1: To create another group for the East Region, select the cell range, A2:C5.

2: Now, press the shortcut keys Shift+Alt+Right, and immediately the “Group” window pops up, as shown below.

3: Select the “Rows” option > click on “OK”. We will see the bar beside the selected rows.

4: To create another group for the North Region, select the cell range, A7:C10.

5: Now, press the shortcut keys Shift+Alt+Right, and immediately the “Group” window pops up, as shown below.

6: Select the “Rows” option > click on “OK”. We will see the second bar beside the selected rows.

7: We will create a Nested Group[inner group] for the East Region, select the cell range, A2:C3, press the shortcut keys Shift+Alt+Right, and immediately the “Group” window pops up, then select the “Rows” option > click on “OK”. We will see the inner bar beside the selected rows.

8: We will create a Nested Group[inner group] for the North Region, select the cell range, A7:C8, press the shortcut keys Shift+Alt+Right, and immediately the “Group” window pops up, then select the “Rows” option > click on “OK”. We will see the inner bar beside the selected rows.

The output is shown above. The Grouped Rows and the Nested Groups are visible. However, when we click on the “-” symbol on the bar, you can hide the rows and only display the desired data.

How To Collapse Rows In Excel?

We will learn to Collapse Rows in Excel.

 In the table, the data is as follows:

• Column A contains the Name.
• Column B contains the Marks.

The steps to Collapse Rows in Excel are as follows:

Step 1: Select the cell range, A2:B3.

Step 2: Select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down > click on the “Group” option. The “Group” window pops up, as shown below.

Step 3: Select the “Rows” option > click on “OK”. The students whose marks are 90 and above form a group.

Step 4: Click the “-” button to collapse the selected rows.

The output is shown above. The rows with students who scored 90 or above are hidden, i.e., rows 2 and 3. Rows 4 and 5 have moved up the dataset.

How To Expand Rows In Excel?

We will learn to Expand Rows In Excel.

In the table, the data is as follows:

• Column A contains the Item/Sales.
• Columns B, C, and D contain the sales of Jan, Feb, and March.

The steps to Expand Rows In Excel are as follows:

Step 1: Select the cell range, A2:D3.

Step 2: Select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down > click on the “Group” option. The “Group” window pops up, as shown below.

Step 3: Select the “Rows” option > click on “OK”. The selected rows form a group, i.e., rows 2 and 3.

Step 4: Click the “-” button to collapse the selected rows. Rows 2 and 3 are hidden, and only row 4 is visible, as shown below.

Step 5: Next, click the “+” sign button to expand the selected Rows.

The output is shown above. All three rows are visible when we click the “-” button.

How To Remove Outline And Ungroup Rows?

We will learn to Remove Outline And Ungroup Rows.

To Remove Outline > In the table, the data is as follows:

• Column A contains the Name.
• Column B contains the Annual Scores.

The steps to Remove Outline are as follows:

Step 1: Select cell B7. This cell calculates the total salary.

Step 2: Select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down > click on the “Auto Outline” option. The Grouped Rows form, as shown below.

Step 3: Now, to remove the outline, select the “Data” tab > go to the “Outline” group > click on the “Ungroup” option drop-down > click on the “Clear Outline” option.

The output is shown below. The outline of the group is removed, and the dataset looks as it was at the start.

To Ungroup Rows > In the table, the data is as follows:

• Column A contains the Fruits.
• Column B contains the Price.
• Column C contains the Value of Combine cells.

The steps to Ungroup Rows are as follows:

Step 1: Select the cell range, A2:C3.

Step 2: Select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down > click on the “Group” option. The “Group” window pops up, as shown below.

Step 3: Select the “Rows” option > click on “OK”. The selected rows form a group, i.e., rows 2 and 3.

Step 4: Now, to ungroup the rows, select the “Data” tab > go to the “Outline” group > click on the “Ungroup” option drop-down > click on the “Ungroup” option.

Step 5: The “Ungroup” window pops up, as shown below.

Step 6: Select the “Rows” option > click on “OK”.

The output is shown above. The rows are ungrouped, and the dataset looks as it was at the start.

Calculate Group Subtotals Automatically in Excel

We will learn to Calculate Group Subtotal Automatically in Excel.

In the table, the data is as follows:

• Column A contains the Name.
• Column B contains the Dress Size.

The steps to Calculate Group Subtotal Automatically in Excel are as follows:

Step 1: Select cell B2. These cells are grouped for subtotal.

Step 2: Go to the “Data” tab.

Step 3: Choose “Subtotal” from the “Outline” group.

Step 4: The “Subtotal” window pops up. Now, check/tick the checkbox of the “Dress Size”, the “Replace current subtotals”,and the “Summary below data” options.

Step 5: Click on “OK”.

The output is shown above. The individual rows’ subtotals and the grand totals are calculated.

Frequently Asked Questions

Download Template

This article helps understand Group in Excel with the formulas and examples. You can download the template and use it instantly.

Recommended Articles

This has been a guide to Group In Excel. Here we learn how to Create/Remove rows & columns & calculate group subtotals with examples & downloadable template. You can learn more about excel from the following articles –

• SUBTOTAL Function In Excel
• Pivot Table In Excel
• FILTER In Excel