Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Group Rows by Cell Value in Excel (3 Simple Ways)

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.

How to group rows by cell value


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.

How to group rows by cell value sort
After that, we will select the entire table and go to the Data tab and select the Subtotal.

group rows by cell value data tab
Now, a dialogue box will pop up, and we will do as the following image shows.

group rows by cell value by subtotal
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.

group rows by cell value by pivot table
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.

group rows by cell value pivot table
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:


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.

group rows by cell value power query
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.

group rows by power query

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.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo