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.
How to Group Data in Excel?
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. , 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. , 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.
The steps to create an auto outline with succeeding subtotals are listed as follows:
- In an Excel sheet, enter the data as shown in the following image.
- To each country, add subtotals manually, as shown in the following image.
- Place the cursor inside the table. Click on “auto outline” in “group” under the Data tab.
- Clicking on “auto outline” groups the range included in the country-wise total.
- 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.
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.
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.
- 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.
- Step 3: The group buttons appear at the top.
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 boxName BoxIn Excel, the name box is located on the left side of the window and is used to give a name to a table or a cell. The name is usually the row character followed by the column number, such as cell A1.. The numbers “1” and “2” appear within boxes.
Clicking on “1” reveals the group summary, as shown in the following image.
Clicking on “2” expands the table and reveals the breakup of the group, as shown in the following image.
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. 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.
- Step 2: Click on “group” under the Data tab.
- 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 Excel Shortcut An Excel shortcut is a technique of performing a manual task in a quicker way.“Shift+Alt+Right Arrow” groups selected cells of the data.
- Step 4: The rows of Canada are grouped, as shown in the following image.
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.
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.
- Step 2: Click on “subtotal” under the Data tab.
- Step 3: A dialog box appears, as shown in the following image.
- Step 4: Select the basis on which subtotals are to be added. In the box “at each change in,” select “country” as the base.
- Step 5: Since totals are required, select “sum” under “use function.”
Note: The user can select different functions like sumSumThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges., average, minMinIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array., maxMaxThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values., etc., in the “subtotalSubtotalThe SUBTOTAL excel function performs different arithmetic operations like average, product, sum, standard deviation, variance etc., on a defined range.” dialog box.
- Step 6: For totaling the columns, select them under “add subtotal to.” Check the boxes for “units sold,” “unit priceUnit PriceUnit Price is a measurement used for indicating the price of particular goods or services to be exchanged with customers or consumers for money. It includes fixed costs, variable costs, overheads, direct labour, and a profit margin for the organization.,” “gross sales,” “COGS,” and “profit.” Click “Ok.”
- Step 7: The subtotals and the groups appear, as shown in the following image.
Frequently Asked Questions
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.
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.”
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.”
- 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.
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 –