Can You Group Data In Excel

Paul Gonzalez
• Saturday, 19 December, 2020
• 8 min read

While the automatic outlining capabilities in Excel 2013 work very well with numerical data, it is not so effective when working with non-numerical values or data that has no distinctive totals. In the workbook image below, there are no formulas or numeric totals, so you will need to group the data manually.

excel create table contents examples
(Source: www.educba.com)


To use the subtotal feature, you first need to select a range of cells that you would like to work with. In the “Add subtotal to” section, toucan select the columns you want totals for.

You will see that new subtotals for each region have been created, as well as a grand total that adds all the values together. In addition, Excel has automatically grouped this data together in a numbered outline.

For this, I have created on simple example data in a worksheet. Instead of seeing all the country names we can club all the countries into one and make the data very precise or else we can go one step ahead and group products as well and show very little data.

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings)35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion Step 2: After you click on that arrow you will see below dialogue box.

Toucan also type the Excel shortcut key SHIFT + ALT + RIGHT ARROW (hold Shift & Alt key together and press Right Arrow). Step 1: Remove all the manually added subtotals.

excel rows outline collapse data column collapsible expand auto grouped office levels automatically option expandable different manually below example them
(Source: www.ablebits.com)

Step 4: Here we need to select on what basis we are adding the subtotals. Under SUBTOTAL, we can add a variety of functions like SUM, AVERAGE, MIN, MAX in Excel, and many other things.

Clear Outline will remove all the grouping from the worksheet. SHIFT + ALT + RIGHT ARROW is the shortcut key to group selected cells.

This has been a guide to Group in Excel (Auto, Manual). Grouping in Excel is used when we have properly structured data and with the header’s names mentioned in the column.

There, grouping allows users to club rows or columns of any number together so that we can hide or in proper words subset the data under the selected columns and rows. Group : Press Shift + Alt + Right Arrow shortcut, rather than going on the data tab then clicking the group button and then selecting row or column option.

Ungroup: Press Shift + Alt + Left Arrow shortcut, rather than going on the data tab then clicking the ungroup button and then selecting the row or column option. Summary of Example 1: As the user selects the row for the month of March, it is grouped into one.

data excel grouping multiple manually groups table pivot names field pivottable row create select into office them selected ablebits
(Source: www.ablebits.com)

Now the user can hide or show the group using the button that is attached to the bracket created on the left side. Step 2: As the user has selected row for the month of March and the region is East.

Step 4: Now select the April month data for the same region. Step 6: Similarly select the north region and create a group.

Step 7: Now select the entire east region row and the 5th group will be created. Step 8 : Now select the entire north region row and the 6th group will be created.

Summary of Example 2 : As the user selects a row for each month, it is grouped into one. Now the user can hide or show the group using the button that attached to the bracket on the left side.

Summary of Example 3: The Q1, Q2 and Q3 sheets selected by the user will be grouped. Now a user can perform an edit on multiple sheets at the same time.

excel grouping ranges consolidation pivottable pivot table data multiple row column groups labels label into field filter fields report creating
(Source: www.ablebits.com)

Let’s take an example where a user wants to update the data for August in the East region from Q3 sheets to $1820. The user needs to make sure whenever they are going to use a group there should be column header, a summary row or subtotal if it is not there then we can create it with the help of the subtotal button which is available in the same toolbar of group button.

Always make sure whichever column a user wants to make a group has some label in the first row itself and there should be no blank row or column in Excel. Once the user ungroups or groups he can reverse by Undo button (Ctr+Z).

It is not possible to ungroup other adjacent groups of columns /rows at the same time; the user needs to do it separately. This guide will show you how to group in Excel with step-by-step instructions, examples, and screenshots.

Suppose we have a schedule in a worksheet that is becoming quite long, and we want to reduce the amount of detail that’s shown. Once you ’re finished, toucan press the “-” buttons in the margin to collapse the rows or columns.

The reason is that Excel does not make it clear to the user of the spreadsheet that cells have been hidden, and thus they may go unnoticed. Toucan download the Template for free if you wish to use it as an example or starting point for how to group in Excel and apply it to your own work and financial analysis.

month excel date pivot table dates grouping quarter specific function
(Source: www.extendoffice.com)

Keyboard shortcuts speed up your modeling skills and save time. Organizing the large data by combining the subcategory data is the process and it’s called Grouping of Rows in Excel.

When the number of items in line is not important then we can choose group rows that are not important but see the subtotal of those rows only. The number of rows is also lengthy when the worksheet contains detailed information or data.

As a report reader of the data, they don’t want to see lengthy rows; instead, they just want to see the detailed view, but at the same time, if they require any other detailed information, they need just a button to expand or collapse the view as required. In this article, we will show you how to group rows in Excel with expand/collapse to maximize the report viewing technique.

In the above data table, we have city and state-related sales and cost data, but when you look at the first two rows of the data, we have “California” state and the city is “Los Angeles,” but sales happened at different dates, so as a report reader everybody prefers to read what the state-wise sales and city-wise sales in a single column, so by grouping the rows we can create a single line summary view. Follow the below steps to group rows in Excel.

Step 2: Now select the first state rows (California City), excluding subtotals. Step 3: Go to the DATA tab and chose the Group option.

excel columns column grouping unhide hide proxi dev
(Source: www.wallstreetmojo.com)

Step 6: The moment you click on “Ok,” toucan see a joint line on the left-hand side. Now we could see only what is the total summary for the city “California,” again, if you want to see the detailed summary of the city, toucan click on the “PLUS” icon to expand the view.

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings)35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion Now again, select the city Colorado and click on the Group option.

Group by Using Shortcut Key With a simple shortcut in Excel, we can group selected rows or columns very easily. By using the Auto Outline, we can automatically group the data.

We need the summary of Sales & Cost columns so choose the same. Here we discuss how to group rows in Excel with expand/collapse using an auto outline and subtotal option with examples and a downloadable Excel template.

Related Videos

Other Articles You Might Be Interested In

01: Types Of Grouper In The Keys
02: Pandas Dataframe Grouper
03: Pandas Grouper For Not 1-dimensional
04: Pandas Grouper Is Not Defined
05: Pandas Grouper Not 1 Dimensional
06: Pandas Python Grouper Not 1-dimensional
07: Pan Fried Blackened Grouper Recipe
08: Pan Fried Grouper Cheeks
09: Pan Fried Grouper Fish Recipes
10: Pan Fried Grouper Recipes
1 louisiana.kitchenandculture.com - https://louisiana.kitchenandculture.com/recipes/pan-fried-grouper
2 www.nassauparadiseisland.com - https://www.nassauparadiseisland.com/recipe-pan-fried-grouper
3 www.yummly.com - https://www.yummly.com/recipes/pan-seared-grouper
4 www.myrecipes.com - https://www.myrecipes.com/recipe/pan-seared-grouper-balsamic-brown-butter-sauce
5 www.orlandosentinel.com - https://www.orlandosentinel.com/news/os-xpm-1989-06-15-8906150261-story.html
6 www.wellplated.com - https://www.wellplated.com/pan-fried-cod/
7 www.chowhound.com - https://www.chowhound.com/recipes/mario-batalis-fried-grouper-sandwich-31848
8 www.weightwatchers.com - https://www.weightwatchers.com/us/recipe/grouper-pan-roasted-tomatoes-1/5626a620a6d5b39610700ad4
9 www.allfoodchef.com - https://www.allfoodchef.com/sauteed-grouper-recipe/