Lutful Hamid
Very often we need to group similar data to quickly get an overview of the data. Also, we may need to group similar kinds of information to get an idea about different portions of any operation. In this article, we have shown you 5 easy ways to group rows Excel. We will give you an idea of how you can do so using the Data tab, keyboard shortcut, and commands.
Suppose a company operates in 3 regions and sells 3 different products- TV, Heater, and Fan. The data below shows the Sales volume of these 3 different products in 3 different regions. Now the company wishes to group the data according to different regions. Our table has 3 columns namely Region, Product, and Sales.
Download Practice Workbook
5 Easy Ways to Group Rows in Excel
Now we will look for methods to group rows. Suppose, we want to group the rows corresponding to sales in the North Region. The following methods will guide us through different methods to do so.
1. Grouping Rows Using Group Feature
We can use the Data tab on the ribbon to Group rows in Excel. First, we will have to select the corresponding rows.
Then we will go to the Data tab and click on Group.
A new dialogue box will appear.
We will select Rows here.
Then, press OK.
Here, it will group the rows.
We can see from the left that rows 5, 6, 7 have appeared to be grouped. We can use the minimize symbol (-) to collapse these rows to one.
When hidden, a plus sign(+) will appear. Clicking the plus sign, we can expand the grouped rows.
Read More: How to Group Rows in Excel with Expand or Collapse (5 Methods)
2. Creating Nested Groups to Group Different Rows
In simpler words, Nested Groups are group(s) inside another group. Suppose, after grouping the North Region we want to group together TV and Heater sold in that region. To do so, we will group the north region using the previous method (Method 1). Then we will select the rows indicating TV and Heater sold in the north region.
We will again go to the Data tab >> Group. And select Rows in the dialogue box.
Then, click OK.
We will find that another group has formed inside the previous group.
Here, rows 5, 6, 7 forms the outer group. Rows 5 and 6 form the inner group. We can collapse and expand groups using the steps shown in the previous method.
Related Content: How to Group Rows by Cell Value in Excel (3 Simple Ways)
3. Grouping Rows Using SHIFT + ALT + Right Arrow Key
We can use the keyboard shortcut SHIFT + ALT + Right Arrow Key () to group rows. To do so, we have to select the rows we want to group together.
Then we will press the SHIFT + ALT + Right Arrow Key () together. A dialogue box will appear. We need to select rows here and press OK.
We will see that the selected rows have grouped together. Here, rows 5, 6, 7Â have been grouped together.
We may use the minimize symbol (-) to collapse these rows to one.
When hidden, a plus sign(+) will appear. Clicking the plus sign, we can expand the group rows.
Related Content: Shortcut to Unhide Rows in Excel (3 Different Methods)
Similar Readings:
- How to Freeze Rows in Excel (6 Easy Methods)
- How to Highlight Active Row in Excel (3 Methods)
- [Fix]: Unable to Unhide Rows in Excel (4 Solutions)
- How to Color Alternate Rows in Excel (8 Ways)
- How to Create Rows within a Cell in Excel (3 Methods)
4. Grouping Rows in Excel Using Auto Outline
In the previous methods, we have done the groupings manually. Excel has a feature named Auto Outline that allows us to create groups automatically.
To use an Auto Outline, we have to create some rows that will differentiate between different groups. Here we have inserted additional regional total rows.
Then we will go to the Data tab >> Group >> Auto Outline.
We will find the data grouped according to different regions.
Related Content: How to Group Rows in Excel Pivot Table (3 Ways)
5. Grouping Rows in Excel Using Subtotal
We can use the Subtotal feature of Excel to group data as well as to get a summary of the data. To use the Subtotal feature we will have to Sort the data first. We can use the Sort and Filter feature of Excel to do so. To perform sorting, we will first need to select the range of data over which we want to perform sorting. Here, we selected the Region column.
Then go to, the Data tab and select Sort A to Z (Lowest to Highest).
A new dialogue box will appear. We need to select Expand the selection and click Sort.
Here, we have got sorted data.
We will select the entire data range.
Then go to Data tab >> select Subtotal.
A new dialogue box will appear.
We will select our preferences in this dialogue box.
At each change inbox: We will select the data of the column according to what we want to group the rows.
Use function box: We will select the mathematical operation of our choice. We can use SUM, COUNT, AVG, MIN, MAX, etc. functions.
Add subtotal to box: We will select the column to which we want to perform the mathematical operation.
Clicking Summary below data checkbox will show Subtotal after each group.
Pressing OK, we will get grouped data according to different regions.
Related Content: How to Select Row in Excel If Cell Contains Specific Data (4 Ways)
Things to Remember
When using the Subtotal feature be sure to select all the options correctly to get an accurate result.
Practice Section
We have included a practice section in the sheet provided so that you can easily get yourself familiar with the methods.
Conclusion
In this article, we have discussed 5 methods to group rows in Excel. These 5 methods can do the job of grouping rows very effectively. Practice and make yourself familiar with these methods. For any feedback or recommendation feel free to comment below. For any Excel-related problems, please feel free to contact us. Our team will be more than happy to help you out.
