Group Data in Excel

Group Data in Excel

The more data you have the more confusion it will create in the final summary sheet. For example, if you are showing 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 then 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. In this article, we will show you the ways of grouping data in excel.

Too much of subcategory line items may be overwhelming to see and very complex to read, this adds to the chances of reading wrongly. The good thing is the excel flexible and we can organize the excel data to the group to create a precise summary by adding PLUS or MINUS signs. By using the PLUS icon we can collapse and similarly by using the MINUS icon we can combine or group the data columns.

As you can see above we have PLUS icon if you click on the PLUS icon we can see the sub-category totals for each month.

If you click on the MINUS icon then we can group the data.

Now we can see how different categories are contributing to the “Jan” month total.

How to Group Data in Excel?

Ok, let’s learn how we can group the data step by step in excel.

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

As you can see above we could see only three months summary in the single page view, to see other months summary we need to scroll right of the sheet to see other remaining months to see the summary but by grouping the data we can see in single page view like the below one.

Ok, let me show you how to group the first-month sub-category breakup.

Select columns that we need to group, in this example we need to group sub-category breakup columns and see the only monthly total, so we need to select only sub-category columns.

After selecting the columns to the group go to the Data tab under the outline category of the Data tab we have a “Group” option.

Click on the drop-down list of “Group” and again choose “Group”.

As soon as you select the “Group” option we can see the selected columns are grouped together.

Click on the “Minus” icon to see only “Jan” month total.

Similarly, select the second month “Feb” columns and group the data.

One tip here is you need not click on the “Group” option every time from the DATA tab, rather once the first-month grouping is over select the second-month column and presses the F4 key to repeat the previous task one more time.

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

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

For example, if I want to see “Mar” month subcategory breakup values then we need to click on the “PLUS” icon symbol.

To Group the columns click on the minus sign.

Use Shortcut Key to Group the Data in Excel

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

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

First, select the columns to be grouped.

Now press the shortcut key ALT + SHIFT + Right Arrow.

View Collapsed (Expanded) and Grouped Data Quickly

As we did above we can group the data to view a single page summary or single window frame summary in excel. When we group the data at the top left of the worksheet we can see level buttons.

If you click on the first level button i.e. “1” we can see “Grouped” data when we click on the level button “2” we can see expanded data.

Things to Remember

• Grouping is the way of showing only the main data.
• While grouping the excel data we need to select all the data columns that we need to group and leave out the column that needs to be seen.
• Shortcut key to group the selected data columns is ALT + SHIFT + Right Arrow.

Recommended Articles

This has been a guide to Group Data in Excel. Here we discuss how to group data in excel with the shortcut key and the quickest way to view expanded or grouped data. You may learn more about excel from the following articles –

• How to Group Columns in Excel?
• How to Group Worksheets in Excel?
• Using Power BI GroupBy
• Create Grouped Bar Chart