Group in Excel

Updated on April 19, 2024
Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Group In Excel?

The Group is an Excel tool that groups two or more rows or columns. With this function, the user has the option to minimize and maximize the grouped data. The rows or columns of the group collapse on minimizing and expand on maximizing. The group option is available under the Outline section of the Data tab.

Group in Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Group in Excel (wallstreetmojo.com)

Key Takeaways

  • The Group in excel is used to group two or more rows or columns.
  • We can collapse or expand the grouped data by minimizing and maximizing, respectively.
  • The Excel shortcut keys to group data are Shift+Alt+Right Arrow. Similarly, the shortcut keys to ungroup the grouped data are Shift+Alt+Left Arrow.
  • The “clear outline” option removes grouping from the worksheet.
  • When we are using the “auto outline” option while grouping, the subtotals can either precede or succeed the grouped data.
  • Alternatively, we can also use the shortcut keys Shift+Alt+Left Arrow.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Group Data in Excel?

Let us consider a few examples.

Download Template

This article must be helpful to understand the Group in Excel, with its formula and examples. You can download the template here to use it instantly.

You can download this Group in Excel template here – Group in Excel template

Example #1 – Auto Outline With Succeeding Subtotals

The Excel sheet consisting of the country name, product, units sold, price,  gross salesCOGS, and profit is shown in the succeeding image. We can either club the countries into one or group the products into categories to make the data precise.

Let us go with the former approach and club the countries.

Group Example 1

The steps to create an auto outline with succeeding subtotals are listed as follows:

  1. Enter the data as shown in the following image.

    Group Example 1

  2. To each country, add subtotals manually

    Group in Excel

  3. Place the cursor inside the table. Click on Auto outline in Group under the Data tab.

    Group Example 1-2

  4. Clicking on Auto Outline will group the range included in the country-wise total.

    Group Example 1-3

  5. Clicking the plus sign (+) hides the sub-items of each country. The consolidated summary of every country can be seen as highlighted in the below image.

    Group Example 1-4

Example #2 – Auto Outline With Preceding Subtotals

In the previous method, we added the totals at the end of every country. Let us add the totals before the data of a particular country.

Group Example 2

The steps to group data with preceding totals are:

  • Step 1: Click on the Dialog Box Launcher under the Outline section of the Data tab.
Group Example 2-1
  • Step 2: The Settings dialog box appears.

Uncheck the box Summary rows below detail and click on Create to complete the process.

Group Example 2-2
  • Step 3: The group buttons appear at the top.
Group Example 2-3

Example #3 – The Collapse And Expansion Of Grouped Data

At any point in time, we can collapse or expand the group. In the top left-hand corner, there are two numbers following the name box. The numbers 1 and 2 appear within the boxes.

Clicking on 1 reveals the group summary, as shown in the following image.

Group Example 2-4

Clicking on 2 expands the table and reveals the breakup of the group, as shown below.

Group Example 2-5

Example #4 – Manual Grouping

The previous examples utilize the basic Excel formulas and groups automatically. An alternative method is to group manually.

The steps for manual grouping are as follows:

  • Step 1: Select the range (row-wise) that we have to group. To group Canada, select the range till row 14.
Group Example 3
  • Step 2: Select Group under the Data tab.
Group Example 3-1
  • Step 3: A dialog box, titled Group appears.

Since we are grouping the data row-wise, select “rows” option.

Alternatively, the Excel shortcut Shift+Alt+Right Arrow groups selected cells of the data.

Group Example 3-2
  • Step 4: Now, we have grouped the rows of Canada.
Example 3-3

Remember, we have to repeat the process of manually grouping for the other countries as well. Please note that we should select the data of every country before grouping.

Note: The data should not contain any hidden rows during manual grouping.

Example #5 – Automatic Subtotals

In the previous examples, we added the subtotals manually. Alternatively, we can also add subtotals automatically.

The steps to add subtotals automatically are:

  • Step 1: First, we should remove all the added subtotals manually.
Example 4
  • Step 2: Click on Subtotal under the Data tab.
Example 4-1
  • Step 3: The Subtotal dialog box appears.
Example 4-2
  • Step 4: Select the basis on which subtotals are to be added.
  • Select Country as the base, under At each change in: option.
group excel data Example 4-3
  • Step 5: Since totals are required, select Sum under Use function: option.

Note: The user can select different functions like sum, average, minmax, etc., in the “subtotal” dialog box.

Example 4-4
  • Step 6: For totaling the columns, select them under Add subtotal to:. Check the boxes for Units Sold, unit price, Gross Sales, COGS, and Profit.

Click OK.

group excel data Example 4-5
  • Step 7: The subtotals and the groups appear, as shown below.
Example 4-6

Important Things To Note

  • Group in excel is used to group rows and columns.
  • It is used to create structured financial models.
  • Also, it helps to read complex data.

Frequently Asked Questions

1. What is group in Excel?

The Group in Excel is a tool that helps club similar data. It provides an organized, compact, and readable view to the reader. For grouping, the worksheet must contain headings and subtotals for every column and row respectively. Moreover, there should not be any blank cells in the data to be grouped.

The groups in Excel are used to create structured financial models. Since groups provide minimize and maximize options, they are used to hide unnecessary calculations.

2. Why is data grouped and ungrouped in Excel?

The data is grouped and ungrouped for the following reasons:

• Grouping helps read through the detailed and complex data. Ungrouping removes the grouping of rows and columns, thereby helping the user go back to the initial data view.
• With grouping, it is possible to look at the required data sections of the worksheet. Ungrouping helps to look at the entire worksheet data in one go.

Note: The grouping shortcut is Shift+Alt+Right Arrow, and the ungrouping shortcut is Shift+Alt+Left Arrow.

3. How does ungrouping work in Excel?

The ungrouping of data works as follows:

• To ungroup the entire data, clear the outline.
Click Clear outline under the Ungroup arrow (Outline section of the Data tab).

• To ungroup particular data, select the rows to be ungrouped. In the ungroup box, select Rows option and click OK.
Alternatively, we can also use the shortcut keys Shift+Alt+Left Arrow.

Note: Ungrouping does not delete any data. In case the outline is removed, it cannot be undone with Ctrl+Z.

Recommended Articles

This has been a guide to Group in Excel (Auto, Manual). Here we discuss how to group and ungroup data in Excel along with examples and downloadable Excel template. You may learn more about Excel from the following articles –