Blog

Duplicate values within an Excel dataset might be useful at times. But they can also ruin calculations. In fact, for some numeric duplicate entries, Excel might generate wrong results.

How To Remove Duplicates In Excel

If you are worried about duplicate values jeopardizing your Excel file, you have come to the right place. First, we will learn how to remove duplicate entries from a specific cell of any dataset. And then we will learn how to highlight duplicate values by individually analyzing them before removing.

Remove Duplicate Values From a Column

In the Excel sheet, I have 6 Column Headers. For the first example, I am going to remove the duplicate entries of ‘Jones’ from the Column ‘Rep’.

• Click on any Cell on the DataSet. For this example lets remove cells with the text ‘Jones‘.

• Navigate to the Data Tab> Data Tools.

• Click on Remove Duplicates.
• The Following Dialogue Box Will Appear.

• Check the Column that contains the value. For this example, Jones is listed under the Column Rep. So we need to select Rep and uncheck all the other columns from the dialogue box.
• Click on OK!
• Another dialogue box will appear showing you the summary of the operation.

Excel removed all the duplicate entries except the first-row entry of the value. You will also get to see how many unique values remain listed under the Column Rep after executing the function.

Highlight Duplicate Values

At times you might want to locate the position of duplicate values within a dataset before removing them. If you want to see the position of duplicate values inside your data set you have to use the Conditional Formatting feature. Let’s use the previous example again where we were dealing with the column Rep. We want to highlight every duplicate value in a different color.

• First, select the column. You can click on the Column Name to highlight the entire column at once.
  
• Now navigate to Home Tab> Conditional Formatting. From the drop-down select Highlight Cell Rules>Duplicate Values.

• From the new popup select how you wish to highlight the values.

Wrapping Up!

Was this blog helpful? Please let us know the type of Excel tutorials you would love to read. In the meantime here is a quick blog on How To Freeze Cells In Excel.