In Excel (2007 and above), place each list of values in its own column. Now,

  1. Select cells in first list, then hold CTRL key and then select the second
  2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values. At this point, you are basically done, since the duplicates have been highlighted. However, to pick out all the unique (non duplicate) values, just sort the values using step 3
  3. Now, Sort –> Custom Sort –> By Cell Color – (all the unique , non-duplicate values should be without color).

The above method is used to comapare values in TWO different columns – and eliminate (highlight) any duplicates.

If you have the more normal challenge of finding duplicate rows  – based on duplicate values in a SINGLE column, you can use the built-in ‘Find Duplicates’ function (under Data)

  1. Select ALL the rows (highlight all your data)
  2. Use the Data–>Remove duplicates function. It should prompt you to pick the COLUMN( S ) that contain duplicates.
  3. Pick the COLUMN that contains duplicates.

That’s it. It should eliminate all  duplicate values.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin