How to Group Rows with Same Value in Excel (6 Useful Ways)

In general, Excel files contain numerous rows with a plethora of entries. To easily get around with such kinds of data Excel group rows with the same value is an effective way. Excel features such as Subtotal, Group, Pivot Table, Power Query as well as INDEX-MATCH formula group rows that have the same value.

Let’s say we have an organized dataset containing City wise Product sales. We want to group the rows depending on their row values.

Dataset-Excel Group Rows with Same Value

 In this article, we demonstrate multiple ways to Excel group rows with same value using features and formulas.


Download Excel Workbook


6 Easy Ways to Group Rows with Same Value in Excel

We take organized data as it’s easy to group organized sorted data. In case of unorganized data, users can execute Custom Sort to sort their data according to their demand. To apply the Custom Sort, Move to Home > Sort & Filter > Custom Sort.

Sorting-Excel Group Rows with Same Value

From the Sort window, users can choose any specific Column Heading (i.e., City) as Sort by option.


Method 1: Using Subtotal Feature to Group Rows with Same Value

When we want to group rows with same value, the Subtotal feature is a good option to sort entries. Also, Subtotal offers several functions to execute within the groups.

Step 1: Place the cursor in any cell within the dataset. Afterward, Go to Data > Click on Subtotal (from the Outline section).

Subtotal Feature-Excel Group Rows with Same Value

Step 2: The Subtotal window appears. In the window,

➧ Choose City as At each change in.

➧ Select any function offered in Use Function drop-down box.

➧ Tick Add Subtotal To columns.

➧ Enable other options as you prefer.

➧ Click on OK.

Subtotal

🔼 Excel groups rows depending on their values with subtotal as you can see from the below picture. You can hide any groups just by clicking the Minus () icon on the left side of the dataset. And there are 3 separate groups of rows showing 3 different prioritized items. Also, there are 3 different prioritized display options that showcase the different levels of grouping as the numbers (i.e., 1,2,3) above the dataset.

Subtotal Final result

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


Method 2: Auto Outline Data to Group Rows with Same Value

Sometimes, users have categorized data in their raw Excel files. As a result, they just want to group the entire data differentiating different Groups. Assume the following screenshot represents the categorized dataset.

Auto Outline-Excel Group Rows with Same Value

Step 1: Hover to the Data tab > Click on Group (from the Outline section) > Click on Auto Outline.

Auto Outline Feature

🔺 Keep in mind that without organized data Auto Outline will not work. Your data must be in sections with similar data types but different entries.

Step 2: In a moment, Excel forms 3 groups since you have 3 different categories.

Auto Outline Outcome

Read More: How to Group and Ungroup Columns or Rows in Excel


Method 3: Using Group Feature to Manually Group Rows

Grouping entries in the dataset can be intense when we categorize several entries. In case, we want to group rows with more than 1 criterion; we have to execute nested grouping. We can only execute nested grouping manually.

Single Grouping: Before being able to create a nested grouping, you have to Group Rows for an outer sphere.

Step 1: To do so, Select outer group rows then Go to Data > Click on Group (from the Outline section) > Click Group.

Group feature-Excel Group Rows with Same Value

Step 2: The Group dialog box appears. Choose Rows as grouping items. Click on OK.

Group feature

🔼 Clicking OK triggers Excel to make a group out of the selection. Repeat Steps 1 and 2 for other selections, you get single groupings out of the selections.

Single Group

Nested Grouping: Nested grouping requires categorizing data for more than 1 criterion. Perform Custom Sort and Subtotal for different categories of the data. Now, 1stexecute Single Grouping as done in the Single Groupin section. Afterward, selecting different category sections execute Data > Group. At last, you see something similar to the below picture.

Nested Group


Method 4: Group Rows with Specific Entries Using Pivot Table

Pivot Table is an effective tool to group similar rows with Subtotals. Pivot Table categorizes data in existing sections offering an overall synopsis of the data.

Step 1: Highlight the entire dataset. Then, move to Insert > Click on Pivot Table (from Tables section) > Click on From Table/Range.

Pivot table-Excel Group Rows with Same Value

Step 2: In a moment, Excel brings up PivotTable from table or range window.

➧ Choose New Worksheet as Choose where you want the PivotTable to be placed.

➧ Click on OK.

Pivot table options

Step 3: Excel loads PivotTable Fields in a new worksheet. In the new worksheet,

➧ Tick necessary PivotTable Fields (i.e., City, Category, and Sold Quantity(kg)) to display in Rows.

➧ Display the Sum of Sold Quantity in the Values area.

Pivot table fields

As you can see Pivot Table groups the similar rows differentiating their Category also. That means Pivot Table is capable of subgrouping rows depending on their values.

Read More: How to Group Rows in Excel Pivot Table (3 Ways)


Method 5: Grouping Same Values Using Power Query in Excel

Similar to the Pivot Table, Power Query groups rows considering each entry along with the cells in a row. Therefore, Power Query Editor offers a Group By feature in its Home section.

Step 1: In order to go to the Power Query Editor, Select rows then move to Insert > Click on From Table/Range (in the Get & Transform Data section).

Power Query-Excel Group Rows with Same Value

Step 2: If your data is not in Table, Excel brings up the Create Table dialog box.

➧ Tick My Table has headers option.

➧ Click on OK.

Create Table

Step 3: It takes a few seconds for Excel to create a Table and open Power Query Editor. In Power Query Editor, Choose the Home section > Click on Group By.

Group By feature

Step 4: Excel fetches the Group By dialog box. In the dialog box,

➧ Add necessary grouping (i.e., Category) using Add Grouping option.

➧ Assign a name (i.e., Grouped Rows) for the new column and Choose what Operation has to be performed (i.e., Sum).

➧ Assign a column (i.e., Sold Quantity(kg)) to execute the Sum Operation.

➧ Click on OK.

Group By window

🔼 Performing Group By creates a new column with sorted rows depicting the Sum of each grouped row.

Group By outcome

 Step 5: You have to load the data into a worksheet. Go to Close & Load > Click on Close & Load.

Close & Load

🔼 Excel takes a moment and loads the data as depicted in the following image.

Final Outcome


Method 6: Group Rows with Certain Value Using INDEX-MATCH Formula

There is an alternative way to group the rows, Condensing. An INDEX-MATCH formula can condense rows with same value in just 1 row.

Step 1: Paste the following formula in any blank cell (i.e., G5) adjacent to the dataset.

=INDEX($B$5:$B$15,MATCH(0,COUNTIF($G$4:G4,$B$5:$B$15),0))

Inside the formula, $B$5:$B$15 = array, MATCH(0,COUNTIF($G$4:G4,$B$5:$B$15),0) delivers the row_num for the INDEX function. The COUNTIF portion passes the lookup_array for the MATCH function. At last, the formula fetches a single entry of each similar entry from the $B$5:$B$15 array.

INDEX MATCH-Excel Group Rows with Same Value

Step 2: Press ENTER then drag the Fill Handle to display each different entry within the given array. As a result, the formula condenses the same entries in rows.

INDEX MATCH outcome

Step 3: Again, to aggregate the Sold Quantity of the same City, use the below formula in an adjacent cell (i.e., H5).

=SUMIF($B$5:$B$15,G5,$E$5:$E$15)

The SUMIF formula takes $B$5:$B$15 as range, G5 as Criteria, and $E$5:$E$15 as sum_range.

SUMIF formula

Step 4: Use the ENTER key to apply the formula and drag the Fill Handle to display each City’s total Sold Quantity.

Formula outcome


Conclusion

In this article, we demonstrate features and formulas to Excel group rows with same value. Excel’s Auto Online or Group feature work just fine for relatively compact data. Pivot Table or Power Query excels in the categorized grouping. Hope you find your desired way out within these mentioned methods. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo