Identify and Remove Duplicate Values in Excel

Microsoft Office’s Excel spreadsheets allow you to organize data in a variety of ways. A business can calculate ongoing costs, keep track of customers and maintain an email list. Before using a database, you should check for duplicate values, to make sure you are not wasting money on repeating your efforts.

In this article I am going to show you how to identify and remove duplicate values in Excel. So let’s get started:

Use of Remove Duplicate Command

When you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.

Note: You are permanently deleting data, so it’s a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

  • Select the range of cells, or make sure that the active cell is in a table;
  • On the Data tab, in the Data Tools group and click Remove Duplicates.



Note: You cannot remove duplicate values from data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals. For more information, see Outline a list of data in a worksheet and Remove subtotals.

Use of Conditional Format

Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

  • Select one or more cells in a range, table, or PivotTable report;
  • On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules and select Duplicate Values;
  • Enter the values that you want to use, and then select a format.


I hope that this article helped you to understand how to identify and remove duplicate values in Excel. Feel free to share your thoughts about this article using the comments section below or catch us on Twitter.


Experienced IT Consultant working and living in the UK. In early years he was born and raised up in Greece and been to the UK since studies. He is passionate with technology, gadgets and computers, he really likes the internet and IT in general.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *