How to Group Cells with Same Value in Excel (3 Methods)

If you are working with data with different categories or have cells with the same value in your Excel worksheet, then you might need to group the cells with the same value. In this tutorial, I will show you how to group cells with same value in Excel.

Table of Contents hide

Download Practice Workbook

3 Easy Ways to Group Cells with Same Value in Excel

Method 1: Group Cells with Same Value in Excel Using the Subtotal Feature

Method 2: Apply the Auto Outline Option to Group Cells with Same Value in Excel

Method 3: Use a Pivot Table to Group Cells with Same Value in Excel

Quick Notes

Conclusion

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Group Cells.xlsx

3 Easy Ways to Group Cells with Same Value in Excel

Let’s assume a scenario where we have an Excel file that contains information about the products that a country exports to different countries in Europe. We have the Product name, exported Amount, and the Country to which the product is exported. I will use this worksheet to show you how to group cells with same value in Excel.

Method 1: Group Cells with Same Value in Excel Using the Subtotal Feature

The easiest way is to use the Subtotal feature in Excel to group cells with same value. For example, we will group all the cells with same value in the Country column. Let’s see how we can do that.

Step 1:

First, we will select all the cells in the Country Then, we will click on the Sort and Filter drop-down menu from the Editing section under the Home tab.
Next, we will select Sort A to Z from the Sort and Filter drop-down.

A window named Sort Warning will appear to ask if you want to select the next to our selection. We will select Expand the selection.
Then, we will click on the Sort.

Finally, we will see that all the cell values in the Country column have been sorted.

Step 2:

Now, we will click on Subtotal from the Outline section under the Data.

A new window titled Subtotal will appear. We will choose Country from At each change in drop-down.
Then, we will only select Amount from Add Subtotal to.
Finally, we will click on OK.

Now, we will see that all the rows of the worksheet have been grouped based on the cells with same value in the Country.

Method 2: Apply the Auto Outline Option to Group Cells with Same Value in Excel

Another way to group cells with same value in Excel is to use the Auto Outline feature in Excel. We have to do the following.

Step 1:

First, we will select all the cells in the Country Then, we will click on the Sort and Filter drop-down menu from the Editing section under the Home tab.
Next, we will select Sort A to Z from the Sort and Filter drop-down.

A window named Sort Warning will appear to ask if you want to select the next to our selection. We will select Expand the selection.
Then, we will click on the Sort.

Finally, we will see that all the cell values in the Product column have been sorted.

We have also added a new row under each type of product that indicates the total amount of product sold.

Step 2:

Now, we will click on the Group drop-down under the Data.
Then, we will select Auto Outline from the drop-down menu.

Now, we will see that all the rows of the worksheet have been grouped based on the cells with same value in the Product.

Similar Readings

Method 3: Use a Pivot Table to Group Cells with Same Value in Excel

Alternatively, we can also use the Pivot Table to group cells with same value in Excel. We have to follow the below steps.

Step 1:

First, select all the cells with the data including the column header.
Then, we will go to Insert ribbon and click on the PivotTable.

A dialog box will appear like the following image. Excel will automatically select the data for you. For the new pivot table, the default location will be a New Worksheet.
Then, click OK.

Step 2:

Now a new sheet will appear. It will have a box on the right side of the sheet titled PivotTable Fields. You will find it on the left side of the sheet if you are working with Excel 2007/2010.

Next, drag and drop fields from your data like the image below.
- Product fields to the Columns.
- Country fields to the Rows.
- Amount field to the Values.

Finally, we will see that Excel has created a pivot table for us that groups all the cells with same value.

Quick Notes

You have to sort the cells in your Excel worksheet before you can use the Subtotal or Auto Outline feature in Excel.
The pivot table will always sort the information in alphabetically ascending order by default. You have to use the sort option to reorder the information as per your need.
Select New Worksheet when you are creating a pivot table. If you select Existing Worksheet, a pivot table will be created in your existing sheet that contains the data. There is a substantial risk of data being distorted if we create the pivot table in our existing worksheet.

Conclusion

In this article, we have learned how to group cells with same value in Excel. I hope from now on you can group cells with same value in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!