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.
 In this article, we demonstrate multiple ways to Excel group rows with same value using features and formulas.
How to Group Rows with Same Value in Excel: 6 Easy Ways
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.
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).
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.
🔼 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.
Read More: How to Group Rows by Cell Value in Excel
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.
Step 1: Hover to the Data tab > Click on Group (from the Outline section) > Click on Auto Outline.
🔺 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.
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.
Step 2: The Group dialog box appears. Choose Rows as grouping items. Click on OK.
🔼 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.
Nested Grouping: Nested grouping requires categorizing data for more than 1 criterion. Perform Custom Sort and Subtotal for different categories of the data. Now, 1st execute 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.
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.
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.
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.
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 by Name
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).
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.
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.
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.
🔼 Performing Group By creates a new column with sorted rows depicting the Sum of each grouped row.
 Step 5: You have to load the data into a worksheet. Go to Close & Load > Click on Close & Load.
🔼 Excel takes a moment and loads the data as depicted in the following image.
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.
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.
Step 3: Again, to aggregate the Sold Quantity of the same City, use the below formula in an adjacent cell (i.e., H5).
The SUMIF formula takes $B$5:$B$15 as range, G5 as Criteria, and $E$5:$E$15 as sum_range.
Step 4: Use the ENTER key to apply the formula and drag the Fill Handle to display each City’s total Sold Quantity.
Download Excel Workbook
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
- Group Rows with Plus Sign on Top in Excel
- How to Group Rows in Excel with Expand or Collapse
- How to Group Rows in Excel
- How to Group Columns Next to Each Other in Excel
<< Go Back to Group Cells in Excel | Outline in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
