How to group duplicates in Excel

By SpreadCheaters
Page last updated: 01/07/2023 | Next review date: 01/07/2025
close

Author bio


SpreadCheaters
View More north_east

In this tutorial, we will learn how to group duplicates in Excel. The following dataset represents item no., item name, their price, and the no. of items sold. There are many duplicates in this dataset, let’s first highlight them and then group them. Consider the following steps to learn this.

Grouping duplicates in Excel refers to the process of identifying and categorizing identical or similar values in a specific range or column of data. By grouping duplicates, you can organize and analyze the data more efficiently. Grouping duplicates can be beneficial for various purposes, such as data analysis, identifying patterns, and simplifying data presentation.

Step 1 – Select the range of cells

– Select the range of cells, where duplicates are present or you can select the whole worksheet.
– In our case we are selecting the range from A1 to D11.

Step 2 – Locate the home tab

– Go to the home tab.
– In the “Styles” group, click on “conditional formatting”.

Step 3 – Choose an option

– From the drop-down menu that appeared when you click on the “conditional formatting”, click on “highlight cell rules”

Step 4 – Choose an appropriate color 

– When you click on the “highlight cells rules” option another drop-down menu will appear.
– Click on the “duplicate values” option.
– Now choose an appropriate option. 
– Click on OK.
– It will highlight the duplicates in the dataset.

Step 5 – Click on the filter option

– Select the range where you want the filter to be applied.
– Go to the home tab, in the editing group click on the “filter” option.
– Alternatively, you can go to the data tab, and click on filter in the sort & filter group.

Step 6 – Click on the arrow

– Click on the filter options (the arrow).
– Select the option “filter by color”, and then choose the option “filter by cell color” option.
– This will group the duplicates, but if you want the other columns to group the duplicates as well then you have to first remove the filter from the column and then apply it to the other column turn by turn.