How to remove duplicated entries from an Excel 2016 list?


If you use Excel to analyze large data sets, you might have encountered the need to find and remove duplicate rows from your data. In today’s post, we’ll provide a simple tutorial to help you find those duplicates and scrub them from your data. Although Excel 2016 is used to outline the steps below, they are fully applicable to Excel 2013 as well.

To find the duplicate entries:

  • Open the spreadsheet and select the range in which you need to find the duplicated entries.

2015-10-19 17_04_02-Book1 - Excel

  • In your Home ribbon, select the drop down box of Conditional formatting.

2015-10-19 17_04_45-Book1 - Excel

  • Then move your cursor over the Highlighted cells and hit Duplicated values.

2015-10-19 17_05_50-Book1 - Excel

  • Now you can see that all the duplicated entries in the selected cells will be highlighted.

2015-10-19 17_06_26-Book1 - Excel



To manage Highlighting rules:

  • In the Home ribbon, select the drop down box of conditional formatting and select Manage rules.

2015-10-19 17_06_59-

  • Click on Edit rules in the selected formatting.

2015-10-19 17_08_04-Conditional Formatting Rules Manager

  • Select the rule type accordingly to your need.

2015-10-19 17_08_28-Edit Formatting Rule

  • Select Duplicate in the format all drop box to highlight the duplicate entries.
  • Select Unique in the format all drop box to highlight the unique entries.

2015-10-19 17_09_11-Edit Formatting Rule

  • Click on the format box to modify┬áthe font size, color, underling and effect and click OK in both dialog boxes.

2015-10-19 17_10_04-Edit Formatting Rule

To Remove the duplicated entries:

  • Warning: please make a copy of your original spreadsheet before removing any data!
  • Select the cells in which the duplicates has to be deleted.
  • Now, select Data in the ribbon and select Remove duplicates in the data tools menu.

2015-10-19 17_37_21-Book1 - Excel

  • Make sure the columns are selected and click on OK to remove the duplicate entries.

2015-10-19 17_39_53-Remove Duplicates

  • Check on the columns which are needed to be check for duplicate entries.


Leave a Comment: