If you are working on some important data analysis job then the uniqueness of the data is very important. When you have less number of values you can manually validate and remove the duplicates but when the values are huge, say in hundreds or thousands then manual way of doing things is not only time consuming but also cumbersome. Let me quickly show you how to find duplicates in excel 2010 and also an easier way to remove them.

How to Find Duplicates In Excel 2010

I have a list of cities in hand and I just want to ensure that the list is unique and free of duplicate values.

Follow the below steps to highlight and reveal the duplicate cities.

  • Select the column that you would like to check for duplicate values.

List of cities with duplicate values

  • In Home Tab, find the Conditional Formatting option, click on Highlight Cells Rules, and click on Duplicate Values..

Conditional Formatting Duplicate Values

You can also achieve the above step by using the below shortcut:

Press ALT key and then press H key and then press L key, H key and D key all individually one at a time.

  • You should see a window as shown below, this will help you to format the cells which have duplicate values with a color. Click Ok.

Format Cells that has Duplicate Values

  • You should now see all duplicate values in the column that you have selected are highlighted in a color.
  • Now to just see the duplicate values alone, click on the Column Title (in my case it is Asia Cities) and press SHIFT + CTRL + L, this will help you to apply a filter for your column.
  • Click on the drop-down icon beside the column Asia Cities, mouse hover on Filter by Color, and select the colored cell under Filter by Cell Color.

Filter duplicate values by color

  • That’s it, you should now see the list of duplicate values that your data has. If you just want to list the unique values then you can just click Filter by Color and click No Fill under Filter by Cell Color as shown below.

Filter unique values excel

How to Remove Duplicates In Excel 2010

If you are least bothered about what those duplicate values are and just would like to remove them from your data then just follow the below steps:

  • Select the column that you would like to check for duplicate values.
  • In Data Tab, click Remove Duplicates Option. You will be presented with a dialog, click ok.

Remove duplicates excel

  • You should see a message that shows the number of duplicate values removed and the count of unique values that is remaining.

Remove Dupliates Excel Result Dialog

Found this useful? Please share it, you might never know who could be benefited.