Group in Excel

What is Group in Excel?

The “Group” is an Excel tool which groups two or more rows or columns. With grouping, the user has an 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)

How to Group Data in Excel?

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

Let us consider a few examples.

Auto Outline With Succeeding Subtotals

The Excel sheet consisting of the country name, product, units sold, price, gross salesGross SalesGross Sales, also called Top-Line Sales of a Company, refers to the total sales amount earned over a given period, excluding returns, allowances, rebates, & any other discount. read more, COGSCOGSThe cost of goods sold (COGS) is the cumulative total of direct costs incurred for the goods or services sold, including direct expenses like raw material, direct labour cost and other direct costs. However, it excludes all the indirect expenses incurred by the company.read more, and profit is shown in the succeeding image. To make the data precise, we can either club the countries into one or group the products into categories.

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. In an Excel sheet, enter the data as shown in the following image.


    Group Example 1

  2. To each country, add subtotals manually, as shown in the following image.


  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” groups 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 shown in the following image.


    Group Example 1-4

Auto Outline With Preceding Subtotals

In the previous method, the totals were added 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 listed as follows:

  • Step 1: Click on the dialog box launcher under the “outline” section of the Data tab.
Group Example 2-1
  • Step 2: The dialog box, as shown in the following image, appears. Uncheck the box “summary rows below detail.” Click on “create” to complete the process.
Group Example 2-2
  • Step 3: The group buttons appear at the top.
Group Example 2-3

The Collapse and Expansion of Grouped Data

At any point of time, the group can be collapsed and expanded. On the top left-hand corner, there are two numbers following the name box. The numbers “1” and “2” appear within 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 in the following image.

Group Example 2-5

Manual Grouping

The previous examples utilize the basic Excel formulasBasic Excel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more and group automatically. An alternative method is to group manually.

The steps for manual grouping are listed as follows:

  • Step 1: Select the range (row-wise) to be grouped. To group Canada, select the range till row 14, as shown in the following image.
Group Example 3
  • Step 2: Click on “group” under the Data tab.
Group Example 3-1
  • Step 3: A dialog box appears, as shown in the succeeding image. 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: The rows of Canada are grouped, as shown in the following image.
Example 3-3

The process of manual grouping has to be repeated for the other countries as well. The data of every country has to be selected before grouping.

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

Automatic Subtotals

In the previous examples, the subtotals were added manually. Alternatively, automatic subtotals can be added.

The steps to add subtotals automatically are listed as follows:

  • Step 1: Remove all the subtotals that were added manually.
Example 4
  • Step 2: Click on “subtotal” under the Data tab.
Example 4-1
  • Step 3: A dialog box appears, as shown in the following image.
Example 4-2
  • Step 4: Select the basis on which subtotals are to be added. In the box “at each change in,” select “country” as the base.
group excel data Example 4-3
  • Step 5: Since totals are required, select “sum” under “use function.”

Note: The user can select different functions like sum, average, min, max, etc., in the “subtotalSubtotalThe Excel Subtotal function is a built-in function that returns the subtotal of a given data table or data sets. This formula requires two inputs, the first of which is the function number and the second of which is the range.read more” dialog box.

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

Frequently Asked Questions

1. What is the 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 the “rows” option and click “Ok.” Alternatively, press the shortcut “Shift+Alt+Left Arrow.”

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

Key Takeaways
  • The “group” is an Excel tool which groups two or more rows or columns.
  • The grouped data can be collapsed or expanded by minimizing and maximizing respectively.
  • The Excel shortcut “Shift+Alt+Right Arrow” groups data and “Shift+Alt+Left Arrow” ungroups data.
  • The “clear outline” option removes grouping from the worksheet.
  • While using the “auto outline” option of grouping, the subtotals can either precede or succeed the grouped data.

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>