Group Data In Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Group Data In Excel?

Group data in Excel, as the name suggests, combines data into a group. The more data you have, the more confusion it will create in the final summary sheet. For example, if we show monthly sales reports from different categories and if the categories are too many, then we cannot see the full view of the summary in a single window frame. When the summary exceeds more than one window frame, we need to scroll down or scroll across to see the other month’s numbers. So in such cases, it is a good idea to group the data.

Too many subcategory line items may be overwhelming and complex to read. It increases the chances of reading wrongly. The good thing is that Excel is flexible, and we can organize the data in the group to create a precise summary by adding PLUS or MINUS signs.

Group Data image.png
Group Data Expanded

In this article, we will show you the ways of grouping data in Excel.

Table of contents
  • Grouping is a valuable approach that helps to present only the most significant data clearly and concisely.
  • The more data you have, the more confusion it creates in the summary sheet. Displaying multiple categories can make it difficult to view the whole view in a single window frame. Grouping data can help manage this issue by allowing users to scroll down or across.
  • To group Excel data, select all columns needed and remove the visible column using the shortcut key "ALT + SHIFT + Right Arrow."

How To Group Data In Excel?

Grouping data helps us analyze and manage data with ease. Let us learn how to group data in Excel with detailed examples.

Examples

Example #1

Below is the summary of the monthly sales summary across different business categories.

Group Data Example 1

As we can see above, we could see only two monthly summaries in the single page view. To see other months' summaries, we need to scroll to the right side of the sheet. But by grouping the data, we can see it in a single page view like the one below.

Group Data Example 1-8

Let us show you how to group the first-month sub-category breakup.

  • Step 1: We must select columns that we need to group. In this example, we need to group subcategory breakup columns and see the monthly total, so we need to choose only subcategory columns.
Group Data Example 1-1
  • Step 2: After selecting the columns for the group, we must next go to the "Data" tab. Under the "Outline" category of the "Data" tab, we have a "Group" option.
Group Data Example 1-2
Group Data Example 1-3

As soon as we select the "Group" option, we can see the grouped columns.

Group Data Example 1-4

Then, we must Click on the "minus" icon to see only the "Jan" month total.

Group Data Example 1-5
  • Step 4: Similarly, we must select the second month "Feb" columns and group the data.
Group Data Example 1-6
  • Step 5: One tip here is that we need not click on the "Group" option from the "DATA" tab. Rather, once the first-month grouping is over, select the second-month column and press the F4 key to repeat the previous task.
press F4 key Example 1-7

Like this, we can repeat the steps for each month to group the data.

Full month grouping Example 1-8

So, whenever we want to see the particular month sub-category breakup, we need to click on the "PLUS" icon to expand the grouped columns view.

For example, if we want to see "Mar" month subcategory breakup values, we must click on the "PLUS" icon.

Plus icon Example 1-9

To group the columns, click on the  "MINUS" sign.

minus symbol Example 1-10

Example #2 - Shortcut Key To Group The Data

A shortcut is a way of increasing the productivity in excel, so grouping the data to have a shortcut key.

The shortcut key to the group selected data is ALT + SHIFT + Right Arrow.

Group Data Shortcut Key
  • Step 1: First, we must select the columns to be grouped.
select columns Example 1-11
  • Step 2: Now press the shortcut key ALT + SHIFT + Right Arrow.
shortcut key Example 1-13

Example #3 - View Collapsed (Expanded) And Grouped Data Quickly

We can group the data to view a single-page summary or single window frame summary in Excel as we did above. We see level buttons when we group the data at the top left of the worksheet.

view grouped data Example 1-13

If we click on the first level button, "1," we can see "grouped" data. When we click on the level button "2," we can see "expanded" data.

Group Data Example 1-14

Important Things To Note

  • Grouping is the way of showing only the main data.
  • While grouping the Excel data, we must select all the data columns we need to group and leave out the column to be seen.
  • The shortcut key to group the selected data columns is ALT + SHIFT + Right Arrow.

Frequently Asked Questions

1. How do I auto group data in Excel?

To group cells in Excel, click "Group" in the "Outline" group on the "Data" tab, then select "Rows in the Group" dialog box, and click "OK." If you select entire rows, Excel automatically groups by row, and outline symbols appear beside the group.

2. How do you group data in Excel by column value?

To group your data by multiple columns, you can follow these steps: first, select the "Home" tab, then choose "Group By." Next, in the "Group By" dialog box, select "Advanced" to choose more than one column. Finally, to add another column, select "Add Grouping." These simple steps will help you effectively organize your data.

3. How do I group data by range in Excel?

To group data in a spreadsheet, first, go to the "Analyze" tab, then select "Group," and click on "Group Selection." In the grouping dialog box, you will need to specify the 'Starting at,' 'Ending at,' and 'By values.'