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

In this article, we demonstrate multiple ways to group rows with same value using Excel features and formulas. Suppose 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

To simplify the process of grouping rows by value, the data should be organized by sorting it first, as it much easier to group organized sorted data. In case of unorganized data, execute a Custom Sort by going to Home > Sort & Filter > Custom Sort.

Sorting-Excel Group Rows with Same Value

From the Sort window, choose any Column Heading (e.g. City) as the Sort by option.

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

The Subtotal feature groups entries, and offers several functions to execute within the groups.


  • Place the cursor in any cell within the dataset.
  • Go to the Data tab.
  • Click on Subtotal (from the Outline section).

Subtotal Feature-Excel Group Rows with Same Value

The Subtotal window appears.

  • Choose City as At each change in.
  • Select any function offered in Use Function drop-down box.
  • Tick the column in which to Add Subtotal To.
  • Enable other options as desired.
  • Click on OK.


Excel groups our rows depending on their values with a Subtotal for each group, as in the below picture.

Hide any groups by clicking the Minus () icon on the left side of the dataset. There are 3 separate groups of rows showing 3 different prioritized items. Above the dataset, there are also 3 different prioritized display options that showcase the different levels of grouping (i.e., 1,2,3).

Subtotal Final result

Read More: How to Group Rows by Cell Value in Excel

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

Suppose, as in the following screenshot, that our data is already categorized with Subtotals, and we now want to differentiate between our groups.

Auto Outline-Excel Group Rows with Same Value


  • Go to the Data tab.
  • Click on Group (from the Outline section).
  • Click on Auto Outline.

Auto Outline Feature

Without organized data Auto Outline will not work. The data must be in sections with similar data types but different entries.

Excel forms 3 groups, since we 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 many entries. To group rows by more than 1 criterion, we need to execute nested grouping, which can only be done manually.

Single Grouping:

Before being able to create a nested grouping, we need to Group Rows for an outer sphere.


  • Select the outer group rows.
  • Go to the Data tab.
  • Click on Group (from the Outline section).
  • Click Group.

Group feature-Excel Group Rows with Same Value

The Group dialog box appears.

  • Choose Rows.
  • Click on OK.

Group feature

Excel makes a group from the selection.

  • Repeat Steps 1 and 2 for other selections to get individual groupings for each selection.

Single Group

Nested Grouping:


  • As above, perform Custom Sort and Subtotal for the different categories of data.
  • Execute Single Grouping for each group.
  • For each different category section, execute the Data > Group process.

The result is something similar to the below picture.

Nested Group

Method 4 – Group Rows with Specific Entries Using Pivot Table

Pivot Table categorizes data in existing sections offering an overall synopsis of the data.


  • Highlight the entire dataset.
  • Go to the Insert tab.
  • Click on Pivot Table (from Tables section).
  • Click on From Table/Range.

Pivot table-Excel Group Rows with Same Value

The PivotTable from table or range window opens.

  • Choose New Worksheet as Choose where you want the PivotTable to be placed.
  • Click on OK.

Pivot table options

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

  • Tick the necessary PivotTable Fields (City, Category, and Sold Quantity(kg)) to display in Rows.
  • Display the Sum of Sold Quantity in the Values area.

Pivot table fields

The Pivot Table groups the similar rows differentiating their Category too.

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. The Power Query Editor offers a Group By feature in its Home section.


To open the Power Query Editor:

  • Select the rows to group.
  • Go to Insert.
  • Click on From Table/Range (in the Get & Transform Data section).

Power Query-Excel Group Rows with Same Value

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

  • Tick My Table has headers.
  • Click on OK.

Create Table

Excel creates a Table and opens Power Query Editor.

  • Choose the Home section.
  • Click on Group By.

Group By feature

Excel opens the Group By dialog box.

  • Add necessary grouping (Category) using the Add Grouping option.
  • Assign a name (Grouped Rows) for the new column.
  • Choose what Operation has to be performed (Sum).
  • Assign a column (Sold Quantity(kg)) to execute the Sum Operation.
  • Click on OK.

Group By window

A new column with sorted rows depicting the Sum of each grouped row is created.

Group By outcome

To load the data into a worksheet:

  • Go to Close & Load.
  • Click on Close & Load.

Close & Load

Excel loads the data as in the following image.

Final Outcome

Method 6 – Group Rows with Certain Values Using INDEX-MATCH Formula

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


  • Enter the following formula in any blank cell (G5) adjacent to the dataset:

The formula fetches a single entry of each similar entry from the $B$5:$B$15 array. $B$5:$B$15 = array, and 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.

INDEX MATCH-Excel Group Rows with Same Value

  • Press ENTER and drag the Fill Handle to display each different entry within the given array.

The formula condenses the same entries into rows.


  • Similarly, to aggregate the Sold Quantity for the same City, use the formula below in an adjacent cell (H5):

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

SUMIF formula

  • Press ENTER to apply the formula.
  • Drag the Fill Handle down to display each City’s total Sold Quantity.

Formula outcome

Download Excel Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF