Easily Compare Duplicate Values in Excel

Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows you to analyze the list in order to have better data analysis and or just to figure out which duplicates might need to be removed.

Steps to Easily Analyze a List Containing Duplicate Values in Excel

1. Import the list into Excel, or if it is already there, simply make sure there are headers above the data; in this example, the headers are in row 1.
   
   Also make sure that there are no empty columns in the middle of your data set; if that is a difficult process then don't worry about it.
2. If there are empty columns in the middle of your data then you may need to select the entire data set for this step to work; otherwise, just select any cell within the data set.
   
   Or
   
3. Then, go to the Data tab and click the Filter button:
   
   You will notice drop-down arrows appear in the cells in row 1.  This is what allows you to perform the filter and to compare duplicates.
4. Click the arrow in the column that contains the duplicate values, in this case column A, and select a value from the list.
   
5. Uncheck where it says Select All and then select the value that you want to see and hit the OK button.
   
6. You will now see all values for asc-2:
   
7. To select another value, click the icon in cell A1 and select another value:
   
   Now, we see two different products:
   
   In this case, you will want to sort the data so that the duplicates for asc-2 and asc-4 are grouped together.  Do this by clicking the icon in cell A1 and selecting Sort A to Z or Sort Z to A.

Steps to Analyze Duplicates in a List that Contains Duplicate and Unique Values

This method is similar to the one above except that it assumes that there will be unique values also in this list.  This changes things because the previous method didn't tell you if a value was a duplicate or not until you did the filtering and, with a large list, this will waste a lot of time.

1. Highlight the duplicate values.  Select all data cells in the column that contains the duplicates:
   
2. Go to the Home tab and click the Conditional Formatting button and then Highlight Cells Rules and then Duplicate Values...
   
   
3. In the window that opens, choose the color you want for the duplicates and hit OK:
   
4. Now, you have a list of highlighted duplicates:
   
5. Add filtering to the data set by selecting a cell in the data set or selecting the entire data set and then going to the Data tab and clicking the Filter button:
   
6. In the column with the duplicates, click the arrow that appeared in the header row, and go down to the option Filter by Color and click the color of the cells that you want to show which, in this case, means the cells that contain duplicate values.
   
7. Now, we see only duplicates:
   
8. The best thing to do now is to sort the data so that all duplicates will be next to each other in the list so you can easily compare them.
   Click the icon in cell A1 and select one of the sort options, Sort A to Z or Sort Z to A.
   
9. You end up with a nice neat list showing only duplicate values and having them placed in order so you can compare these values.
   

Filtering in this example will not work like in the first example because the filter tool will show all possible options and not just the options that are duplicates.

Remove Filters

To remove all filters, click the icon in cell A1 and then click Clear Filter From... or simply select the data and go back to the Data tab and click the Filter button (this button adds the ability to filter and it can remove all filtering).

Notes

This tutorial shows you how to combine filtering, sorting, and conditional formatting in order to analyze data in Excel.  These features are combined to create a quick and easy to use analytical framework that allows you to better perform data analysis tasks in Excel.

There are other ways to analyze duplicate values in Excel but the two above methods should work just fine in most cases.

Don't forget to download the accompanying workbook so you can see these methods in action.