WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Group Data in Excel

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 data to the group to create a precise summary by adding PLUS or MINUS signs.

Group Data image.png

Group Data Expanded

How to Group Data in Excel?

You can download this Group Data Excel Template here – Group Data Excel Template

Example #1

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

Group Data Example 1

As you can see above, we could see only two 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 a single page view like the below one.

Group Data Example 1-8

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

Popular Course in this category
Sale
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
View Course
  • Step 1: 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.

Group Data Example 1-1

  • Step 2: 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.

Group Data Example 1-2

  • Step 3: Click on the drop-down list in excel of “Group” and again choose “Group.”

Group Data Example 1-3

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

Group Data Example 1-4

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

Group Data Example 1-5

  • Step 4: Similarly, select the second month “Feb” columns and group the data.

Group Data Example 1-6

  • Step 5: 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.

press F4 key Example 1-7

Like this 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 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.

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, 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

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.

view grouped data Example 1-13

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.

Group Data Example 1-14

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.
  • The 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 –

  • Group Columns in Excel
  • Group Worksheets In Excel
  • Using Power BI GroupBy
  • Grouped Bar Chart in Excel
7 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Group Data Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More