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.

Group in Excel Intro

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

Group in Excel Intro Example
  • If we select the “Rows” option, we get a bar beside the selected rows.
Group in Excel Example.1
  • If we select the “Columns” option, we get the bar above the selected columns.
Group in Excel Intro Example.2
Key Takeaways
  • The Group in Excel means to club similar data in the worksheet. The Group in Excel modifies compact and readable data for the user in a structured way.
  • The Group in Excel groups two or more rows or columns in the data.
  • The Excel shortcut of the Group in Excel is “SHIFT+ALT+RIGHT”.
  • In Group in Excel, we can minimize by collapsing the grouped data or maximize by expanding the grouped data.

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.
Group in Excel How to 1

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

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

Group in Excel How to 1.1

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.

Group in Excel How to 1.2

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.

Group in Excel How to 1.3

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 in Excel How to 1.4

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.
Group in Excel How to 2

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.

How to 2.1

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

How to 2.2

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

How to 2.3

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

How to 2.4

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

Group in Excel How to 2.5

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

 How to 2.6

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.

How to 2.7

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.

How to 2.8

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.
Group in Excel Example 1

The steps to Collapse Rows in Excel are as follows:

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

Example 1.1

Step 2: Select the Datatab > 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.

Example 1.2

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

Example 1.3

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

Example 1.4

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.
Group in Excel Example 2

The steps to Expand Rows In Excel are as follows:

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

Example 2.1

Step 2: Select the Datatab > 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.

Example 2.2

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

Example 2.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.

Example 2.4

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

Example 2.5

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.
Group in Excel Example 3

The steps to Remove Outline are as follows:

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

Example 3.1

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.

Example 3.2

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

Example 3.3

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

Example 3.1

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.
Example 3.4

The steps to Ungroup Rows are as follows:

1: Select the cell range, A2:C3.

Example 3.5

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.

Example 3.6

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

Example 3.7

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.

Example 3.8

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

Group in Excel Example 3.9

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

Example 3.10

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.
Example 4

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

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

2: Go to the “Data” tab.

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

Example 4.1

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.

Group in Excel Example 4.2

5: Click on “OK”.

 Example 4.3

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

Frequently Asked Questions

How to group rows and columns in excel?

We can group rows and columns in Excel as follows,

Select any cell or a cell range > select the “Data” tab > go to the “Outline” group > click on the “Group” option drop-down.

Group in Excel Intro

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

Intro Example

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

Example.1

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

Intro Example.2

Where is Group in Excel?

We can access the Group in Excel as follows:

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

Group in Excel Intro

How to Group in Excel using shortcuts?

The Group in Excel shortcut keys are,

FAQ

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

Download Template

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

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.