Mahbubur Rahman
In this article, we will see how to group rows by cell value in Excel. For your better understanding, I am going to use a sample dataset. Here, the dataset contains, Year, States, Items, Sales Channel, and Sales Unit. For example, you want to summarize the total units sold at the states and sales channel level, grouped by the States and Sales Channel columns.
Download Practice Workbook
3 Simple Ways to Group Rows in Excel
Method 1: Group Rows by Cell Value in Excel Using DataTab
We will use the in-built feature DataTab in Excel to group rows by cell value.
First, we have to sort our data. So, go to the Data tab as shown in the following image and select Ascending sorting.
After that, we will select the entire table and go to the Data tab and select the Subtotal.
Now, a dialogue box will pop up, and we will do as the following image shows.
After, clicking OK, our worksheet will look like the following image.
As you can see, the data are grouped by the cell value we wanted.
Read More: How to Group Rows in Excel (5 Easy Ways)
Method 2: Group Rows by Cell Value by Pivot Table
We can also use Pivot Table to group rows by cell value in Excel. Let me show you, how you can do this.
First, we have to insert a pivot table. We will simply go to the insert tab and click on the Pivot table like the following picture shows.
After that, a dialogue box will pop up. From here, we will select the table range and select a cell where we want our table to be. Now, click OK.
As a result, we will get another dialogue box. Now we will drag the States and Sales Channel to the Row and Sales Unit in the Values section, as the following image describes.
Finally, our desired table is ready, and it looks like this.
So, we can see, we grouped rows by the cell value we wanted.
Read More: How to Group Rows in Excel Pivot Table (3 Ways)
Similar Readings:
- How to Lock Rows in Excel (6 Easy Methods)
- How to Unhide Rows in Excel (8 Quick Ways)
- Highlight Row If Cell Contains Any Text
- How to Collapse Rows in Excel (6 Methods)
- Excel Alternating Row Color with Conditional Formatting [Video]
Method 3: Group Rows by Cell Value Using Power QueryÂ
Power Query is one of the most effective tools to group data.
First, select the entire table and go to the power query and click From Table/Range.
Now, a new window will pop up and we will select Group By from the Home tab.
Now, a dialogue box will pop up and we will select Advance and fill the boxes as per the image shown. Then, click OK.
At last, our table is ready. Now, click on the Close & Load and the table will automatically be generated in the original workbook.
Read More: How to Create Rows within a Cell in Excel (3 Methods)
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.
Conclusion
There are three different ways to group rows by cell value in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback. You may also browse this site’s other Excel-related topics.
