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

Get FREE Advanced Excel Exercises with Solutions!

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


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

Method 1: Group Rows by Cell Value in Excel Using DataTab

We will use the in-built feature Data Tab 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


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.


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


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.


Download Practice Workbook


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

<< Go Back to Group Cells in Excel | Outline in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo