How to Group Similar Items in Excel (4 Common Ways)

If you are working with data or items of different categories in your  Excel worksheet, then you might need to group similar items in your Excel. I’ll show you how to group similar items in an Excel workbook in this article.

Download Practice Workbook


4 Common Ways to Group Similar Items in Excel

In this section, you’ll find 4 methods for grouping similar items in Excel. Let’s check them!


1. Row or Column-wise Group for Similar Items

We may need to group similar items on the basis of rows or columns, in this section I’ll show you the way of grouping similar items row-wise and column-wise. Here, our dataset will be a list of some people with their addresses and purchased products. Address, First Name, Last Name, Product.

Dataset - Group Similar Items in Excel

Now, let’s start the procedure.

  • First of all, select the cells you want to group.
  • Then go to the Data tab and click Group.

  • Select whether you want to group the rows or the columns.

  • By clicking rows, your rows will be grouped.

Row based group for similar items in excel

  • Similarly, you can make a group based on columns.

  • And you will find the group column-wise.

Column and row-wise group for similar items in excel

That’s how we can group similar items on the basis of groups or columns in Excel. Now we can either show the group or hide it also. If sometimes we need to hide some data which is necessary for further use, we can hide or unhide them by making them grouped.


2. Group Cells with Same Items Using Excel Subtotal Feature

For the dataset of the previous method, I’ll show you the application of the Subtotal feature. By this method, we’ll get the total count of similar items in Excel. Let’s check it.

  • Firstly, select the row or the column you want to categorize, go to the Sort & Filter of the Home Tab, and click Sort A to Z or whatever you want to get.

Dataset for Grouping cells with similar items in Excel

  • Click Expand the selection.

  • After sorting the selection, go to the Data Tab and click Subtotal.

Grouping Similar items in Excel

  • Add Subtotal to the text you want to get.

  • You’ll get the total number of products you want to get.

Grouping similar items using subtotal feature in Excel

Thus we can get group cells with the same value in Excel by using the Subtotal feature. When we need to group the similar values in Excel and count the number of the repeated similar items, we get this ready by using the Subtotal feature.


3. Categorize Rows Based on a Similar Text

Suppose, you’ve got a similar number of texts in an Excel sheet that are slightly different. You want to group the similar texts together. In this data set, we have similar types of items that are slightly different in texts (i.e. Choco Fun 1 & Choco Fun 2). Let’s check how we can group these similar texts.

Dataset for Grouping Similar items

  • Firstly, add the relevant Formula in the cell you want similar texts.
=TRIM(LEFT(SUBSTITUTE(C5," ",REPT(" ",255),2),255))

Here, the function has been written using the TRIM, LEFT, SUBSTITUTE, and REPT functions to extract (LEFT) the name of the product after replacing the numbers from the product items (SUBSTITUTE) after the second occurrence. TRIM eradicates any unnecessary space.

  • Press ENTER & you’ll get the output in the desired cell.

  • Drag the formula to every cell you want the texts.

  • After getting the similar texts, go to the Data Tab and Click Sort by A to Z to group the similar texts together.

After sorting you will get the desired output.

Thus If we have a number of almost similar items which are slightly different, we can sort them together with the similarities they got.


4. Group Multiple Cells Using Excel UNIQUE Function

If you have repeated texts or values in an Excel sheet and you only want the unique texts or values, this method will help you get the result. Here we have a dataset of products from different countries in a shop and their weights. In the dataset, we have repeated values of products and we want to get only the unique values. Let’s check it.

  • Firstly, apply the formula to the desired cell you want to get the unique value.
=UNIQUE(B5:B8)

Here, the UNIQUE function collects the unique names from the column.

  • Drag the formula to every cell of the column you want to get the results.

  • Apply the formula for the other cells too.

Grouping Similar items
So, in this way, you can just ignore the repeated values and get the unique values you needed only.


Conclusion

In this article, we have learned how to group similar items in Excel. I hope from now on you can easily categorize similar items in Excel. However, if you have any queries or recommendations effective regarding this article, please do leave a comment below. Have a great day!

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo