WallStreetMojo

WallStreetMojo

WallStreetMojo

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

How to Group Rows in Excel?

Grouping Rows in Excel

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. When the data rows are huge scrolling down and reading the report may lead to wrong understanding so the grouping of rows helps us to hide the unwanted numbers of rows.

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.

Group Rows in Excel

How to Group Rows in Excel with Expand/Collapse?

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

For example, look at the below data.

How to Group Rows in Excel Example 1.0

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 1: First create a subtotal like the below one.

Example 1.1

Step 2: Now select the first state rows (California City), excluding subtotals.

How to Group Rows in Excel Example 1.2

Step 3: Go to the DATA tab and chose the “Group” option.

Example 1.3

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

How to Group Rows in Excel Example 1.4

Step 5: Now, it will ask you whether to group rows or columns. Since we are grouping “Rows,” choose rows and click on ok.

 Example 1.5

Step 6:  The moment you click on “Ok,” you can see a joint line on the left-hand side.

How to Group Rows in Excel Example 1.6

Click on the “MINUS” icon and see the magic.

Example 1.7

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, you can click on the “PLUS” icon to expand the view.

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

Now again, select the city “Colorado” and click on the “Group” option.

How to Group Rows in Excel Example 1.8

Now it will group for the “Colorado” state.

Example 1.9.0

Group by Using Shortcut Key

With a simple shortcut in excel, we can group selected rows or columns very easily. The shortcut key to quickly group the data is SHIFT + ALT + Right Arrow Key.

Group Shortcut Key

First, select the rows that need to be grouped.

How to Group Rows in Excel Example 1.10

Now press the shortcut key SHIFT + ALT + Right Arrow Key to group these rows.

 Example 1.11

In the above, we have seen how to group the data and how to group row with expand and collapse option by using PLUS & MINUS icons.

The only problem with the above method is we need to do this for each state individually, so this takes a lot of time when there are a lot of states that are there. What would be your reaction if I say you can group with just one click???

Amazing, isn’t it? By using the “Auto Outline,” we can automatically group the data.

Example #1 – Using Auto Outline

The first thing we need to do is to create subtotal rows.

How to Group Rows in Excel Example 2

Now place a cursor inside the data range. Under Group drop-down, we can see one more option other than “Group,” i.e., “Auto Outline.”

Example 2.1

The moment you click on this “Auto Outline” option, it will group all the rows which are there above the subtotal row.

How to Group Rows in Excel Example 2.2

How cool is this??? Very cool, isn’t it??

Example #2 – Using Subtotals

If grouping the rows for the individual city is the one problem, then even before grouping rows, there is another problem, i.e., adding subtotal rows.

When hundreds of states are there, it is a tough task to create a subtotal row for each state separately, so we can use the “Subtotal” option to quickly create a subtotal for the selected column.

For example, we had the data like the below before creating subtotal.

Example 2.3

Under the Data tab, we have an option called “Subtotal” right next to the “Group” option.

How to Group Rows in Excel Example 2.4

Click on this option by selecting any of the cells of the data range; it will show below option first up.

Example 2.5

First, select the column that needs to have a subtotal; in this example, we need subtotal for “State,” so select the same from the drop-down list of “At each change in.”

How to Group Rows in Excel Example 2.6

Next, we need to select the function type since we are adding all the values to choose the “Sum” function in excel.

Example 2.7

Now select the columns that need to be summed. We need the summary of “Sales & Cost” columns so choose the same. Click on “Ok.”

How to Group Rows in Excel Example 2.8

We will have quick subtotal rows.

Example 2.9

Did you notice one special thing from the above image???

It has automatically grouped rows for us!!!!

Things to Remember here

  • Grouping rows shortcut key is Shift + ALT + Right Arrow Key.
  • Subtotal needs data should be sorted.
  • Auto Outline groups all the rows above the subtotal row.

Recommended Articles

This has been a guide to group rows in excel. 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. You may also look at these useful functions in excel –

  • Excel Maximum Number of Rows
  • Divide Cell in Excel
  • Group Columns in Excel
  • Group Worksheets In Excel
0 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 Rows Excel Template

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