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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Group Cells with Same Value in Excel: 3 Easy Ways

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 Group and Ungroup Columns or Rows in Excel


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 Group Similar Items in Excel


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


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.

Download Practice Workbook

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


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo