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.


Download Practice Workbook

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


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.

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.

Group Cells with Same Value in Excel Using the Subtotal Feature

  • 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.

Group Cells with Same Value in Excel Using the Subtotal Feature

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

Group Cells with Same Value in Excel Using the Subtotal Feature

Step 2:

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

Group Cells with Same Value in Excel Using the Subtotal Feature

  • 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.

Group Cells with Same Value in Excel Using the Subtotal Feature

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

Group Cells with Same Value in Excel Using the Subtotal Feature

Read More: How to Make a Group of Cells Negative in Excel (5 Methods)


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.

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

  • 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.

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

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

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

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

Apply the Auto Outline Option

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.

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

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

Apply the Auto Outline Option t

Read More: How to Move a Group of Cells in Excel (4 Easy Ways)


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.

Use a Pivot Table

  • 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.

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

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.

Use a Pivot Table

  • 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.

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

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

Use a Pivot Table

Read More: How to Select Cells with Certain Value in Excel (5 Methods)


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!!!


Related Articles

Tags:

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo