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.

How to Group Similar Items in Excel: 4 Common Ways

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.

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.

Similarly, you can make a group based on columns.

And you will find the group column-wise.

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.

Click Expand the selection.

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

Add Subtotal to the text you want to get.

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

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.

Read More: How to Group Duplicates in Excel

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.

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.

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

Download Practice Workbook

Group Similar Items.xlsx

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!

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

Get FREE Advanced Excel Exercises with Solutions!