How to Remove or Highlight Duplicates in Excel

Whenever you are using a huge set of data, then MS excel is the application that helps to manipulate and sort the data and get the final result desired by you. Most of the users use the basic options in the excel application but there are many more features which help users to make their data more improved and efficient. One of the features is finding the duplicates and either you can highlight or remove them from the spreadsheet depending upon your requirement. Also locating the duplicates and deleting them is quite easy if you follow the steps given below in this post. Please continue reading to know more.


ADVERTISEMENT

How to Highlight Duplicates in Excel Sheet

 

Step 1: Press Windows key on your keyboard and type excel.

Step 2: Then, press the Enter key to Open the MS Excel application.

 

Open Excel

 

Step 3: Then, Click Open button to open the saved excel sheet on which you want to highlight the duplicates.

 

Open Excel Sheet Menu 11zon

 

Step 4: Then, select the saved excel sheet in the open window and hit Enter key.

Step 5: Once the excel sheet is open, select the data which you want to highlight the duplicates as shown below.

 

Data Selected For Highlights 11zon


ADVERTISEMENT

 

Step 6: Under the Home tab, Select Conditional Formatting at the right side of the Excel app to see the dropdown list.

Step 7: Then, Hover the mouse cursor over Highlight Cells Rules and then, click Duplicate values from the list as shown in the below screenshot.

 

Conditional Formatiing Duplicate Values 11zon

 

Step 8: Click on values with dropdown menu and select whichever format you want the duplicates to be highlighted.

NOTE:- I have chosen Green fill with Dark Green Text, but you are free to choose any option as shown below.

 

Green File With Green Text 11zon

 

Step 9: After selecting the format, Click OK button as shown below.

 

Ok Button Highlight Duplicates 11zon

 

Step 10: You can observe that for the data set you had chosen, the duplicates present in them are highlighted as shown below.

 

Highlighted Duplicates In Green Text 11zon

 

Step 10: That’s it.

This is how you can highlight the duplicates in the excel sheet for better analysis of your data.

 

How to Remove Duplicates in Excel

 

Step 1: Press Windows key on your keyboard and type excel.

Step 2: Then, press the Enter key to Open the MS Excel application.

 

Open Excel

 

Step 3: Then, Click Open button to open the saved excel sheet on which you want to highlight the duplicates.

 

Open Excel Sheet Menu 11zon

 

Step 4: Then, select the saved excel sheet in the open window and hit Enter key.

Step 5: Now, Select the entire data set from the excel sheet as shown below.

 

Entire Dataset 11zon

 

Step 6: Go to the Data tab and Click Remove Duplicates option on the right side of the ribbon in excel app.

 

Click Remove Duplicates Button New 11zon

 

Step 7: Select the columns so that the duplicate rows are removed depending upon the column you have chosen.

NOTE:– If you select all the columns, then the entire row should match with another entire row to get removed as duplicates.


ADVERTISEMENT

Step 8: Click OK button to remove the duplicates.

 

Select Column To Remove Duplicates 11zon

 

Step 9: Now you can notice that the 3 duplicate values and removed and rest of the unique values are retained in the sheet as shown below.

Step 10: Click OK button to close the window.

 

Duplicates Removed And Unique Left 11zon

 

Step 11: To get a better hold and understanding of the removal of duplicates, please explore more with many options such as including many columns, etc.

That’s it.

Hope this article was informative and helpful.

Please let us know if there is any clarifications needed in the comments section below.

Thank you!

ADVERTISEMENT