How To Remove Duplicate Values In Excel - Identify and Highlight Duplicate Values

Hi Friends,

In this article  we'll discuss about how to find Duplicates or Unique values in Microsoft Excel. In Microsoft Excel duplicate values may come anywhere in a column or in a row and sometimes it is the situation's demand to highlight or find out these duplicate values or to remove or count these duplicate values.

A duplicate value is one where all values in the row are an exact match of all the values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell.


So, our requirements can be listed as follows

1. Filter or Identify or Highlight Duplicate or Unique values

2. Remove or Delete Duplicate values

3. Remove or Delete Duplicate rows or columns

4. Count Duplicates or Unique values

The easiest way to identify and highlight the duplicate values is to click on Conditional Formatting button in the Styles group in Home tab. Then point to the first item in the drop down list named as Highlight Cells Rules and this will expand and at the bottom of this expanded list you will find Duplicate Values option Click on it as shown in the below images. 

Highlight Duplicate Values

Highlight Duplicate Values

A new window will appear as Duplicate Values as shown in the above image, select duplicate or unique as per your requirement from the drop down list. You can also select the cell color or the font color from the drop down list or you can customize the highlight color of this list. 

Now your data will highlight the duplicate or unique values as shown in the below image.

Highlight Duplicate Values

We have successfully identified and highlighted the duplicate values, you also can identify or highlight the unique values by the same techniques, only you need to change the duplicate option to unique in the drop down list.

Remove Duplicate Values or Filter Unique Values

In Excel, you have several ways to filter for unique values or remove duplicate values:

• To filter for unique values, use the Advanced command in the Sort & Filter group on the Data tab.

• To remove duplicate values, use the Remove Duplicates command in the Data Tools group on the Data tab.

• To highlight unique or duplicate values, use the Conditional 
Formatting command in the Style group on the Home tab as stated above.

Filtering Unique Values or Removing Duplicate Values

Filtering unique values and removing duplicate values are two closely related tasks because the displayed results are the same, a list of unique values. The difference, however, is when you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values.

Therefore, it's a good idea to filter for or conditionally format unique values first to confirm that the results are what you want before removing duplicate values.

Filter for Unique Values

1. Select the range of cells, or make sure the active cell is in a table.

2. On the Data tab, in the Sort & Filter group, click Advanced.

Advanced Option to find Duplicate Values

3. In the Advanced Filter dialog box, do one of the following:

    a. To filter the range of cells or table in place, click Filter the list, in-place.

    b. To copy the results of the filter to another location, do the following:

          i. Click Copy to another location.

          ii. In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog.

4. Select the Unique records only check box, and click OK.

The unique values from the selected range are copied to the new location.

Remove duplicate values

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.

Because you are permanently deleting data, please make a copy the original range of cells or table to another worksheet or workbook before removing duplicate values so that if you find something wring, you can rollback the data.

1. Select the range of cells, or make sure that the active cell is in a table.

2. On the Data tab, in the Data Tools group, click Remove Duplicates.

Remove Duplicates

3. Follow the below procedures:

• Under Columns, select one or more columns.

• To quickly select all columns, click Select All.

• To quickly clear all columns, click Unselect All.

• If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

4. Click OK.

A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed as shown in the below picture.

After Removal of Duplicate Values

5. Click OK and you are done.

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

Thank you for reading this article...