# How to Group Duplicates in Excel (3 Easy Techniques)

Sometimes our Excel dataset may have duplicate values. These duplicates can be just multiple entries of the same data. To find out how many duplicates our dataset has and to take further actions such as removing the duplicates we may need to group the duplicates together. In this article, Iâ€™ll demonstrate 3 easy and effective methods to group duplicates in Excel.

Consider the following dataset with several duplicate values. Now, Iâ€™ll show you how you can group duplicates using this dataset.

## How to Group Duplicates in Excel: 3 Easy Methods

### 1. SUMPRODUCT Function to Group Duplicates

With the SUMPRODUCT function you can find the number of duplicates of a dataset and you can use this number to group the duplicates together. First,

âž¤ Type the following formula in the first cell of the first empty column next to your dataset (E5),

`=SUMPRODUCT(--(B5&C5&D5=\$B\$5:\$B\$15&\$C\$5:\$C\$15&\$D\$5:\$D\$15))`

Here the formula will return 1 for each unique data and the number of occurrences for data with duplicate values.

âž¤ Press ENTER

As a result, you will get the number of duplicates for the first data. 1 means the data has been found one time in the whole dataset.

Now,

âž¤ Drag cell E5 to the end of your dataset.

As a result, we will get the number of duplicates for all data.

After that,

âž¤ Right click on the selected cells.

âž¤ Click on Sort to Expand it and select Sort Largest to Smallest.

It will rearrange the rows of your dataset and will put the duplicates together. So, the duplicates will be grouped together.

Now letâ€™s see how you can extract the unique values from this dataset. First,

âž¤ Go to Data > Sort & Filter > Advanced.

As a result, a box named Advanced Filter will appear.

âž¤ Select the action Copy to another location, select an empty cell in the Copy to box, check the Unique records only box, and click on OK.

As a result, you will see, only the unique values are copied into the new location.

### 2. Group Duplicates with Conditional Formatting

You can also group duplicates with conditional formatting. But before applying conditional formatting we need to make a column of combined data including all of the strings from previous columns of the same row.

âž¤ Type the following formula in cell E5,

`=B5&C5&D5`

The formula will combine cells B5, C5, and D5 and will return in cell E5.

âž¤ Press ENTER

As a result, you will get the combination of cells B5, C5, and D5 in cell E5.

Now,

âž¤ Drag cell E5 to the end of your dataset.

As a result, you will get the combined data for all other cells.

At this time,

âž¤ Select your dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

A window named Duplicate Values will be opened.

âž¤ Select a formatting style in the values with box. (For this dataset Iâ€™ve chosen Yellow Fill with Dark Yellow Text).

After that,

âž¤ Press OK.

At this step,

âž¤ Go to Home > Editing > Sort & Filter > Filter.

As a result, a little downward arrow will appear beside each column header.

âž¤ Click on the arrow besides Combined.

âž¤ Go to Filter by Color and select the formatting cell color (Yellow) from Filter by Cell Color. Then click on OK.

Now, you will see all the duplicates of your dataset are grouped together.

Read More: How to Group Similar Items in Excel

### 3. Group Duplicates by Combining COUNTIFS and IF Functions

You can also group Duplicates by using the COUNTIFS function and the IF function altogether. First,

âž¤ Type the following formula in cell E5,

`=IF(COUNTIFS(\$B\$5:\$B\$15,B5,\$C\$5:\$C\$15,C5,\$D\$5:\$D\$15,D5)=1,"Unique","Duplicates")`

The formula will return Unique if the data of row 5 has no other copy and Duplicates if the data of row 5 matches with the data of any other rows.

âž¤ Press ENTER.

Cell E5 will show Unique that means the data of row 5 is unique.

Now,

âž¤ Drag cell E5 to the end of your dataset.

After that,,

âž¤ Go to Home > Editing > Sort & Filter > Filter.

As a result, a little downward arrow will appear besides the Column headers.

âž¤ Click on the downward arrow of the column header Status.

âž¤ Check on Duplicates and click on OK.

As a result, you will get the duplicates grouped together.

To get the same values altogether,

âž¤ Click on the arrow besides Salesman column (You can also produce the same result by selecting any other columns except the Status column)

It will open the filtering menu.

âž¤ Check on only one name from the list and click on OK.

As a result you will get the same values grouped together.

## Conclusion

By following any of the above described methods you will be able to group duplicates in Excel. If you have any kind of confusion, please leave a comment.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF