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 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.
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
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.
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.
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
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.
- How to Group Rows in Excel by Name
- Group Rows with Plus Sign on Top in Excel
- How to Group Columns Next to Each Other in Excel
- How to Group Rows with Same Value in Excel
- How to Group Rows in Excel with Expand or Collapse