BookmarkPrint
Contents
Home > Microsoft Office > Excel > Unique/Duplicate Values - Excel


Unique/Duplicate Values - Excel
In Excel, you can filter for unique values and remove duplicate values. Both functions will result in a list of unique values. Filtering for unique values will temporarily hide the duplicate values, whereas removing the duplicate values will permanently delete them. Duplicate values are defined as all values in a row exactly matching the values in another row.

Quick Links to sections in this article: 
Filter for Unique Values  Remove Duplicate Values 
Quick Format Unique/Duplicate Values       Advanced Format Unique/Duplicate Values 

Filter for Unique Values

Step 1:Select the range of cells that you wish to filter.

Step 2:Click the Data tab and choose Advanced from the Sort & Filter Group.

Step 3:
Choose whether to filter the list in place or copy it to another location. If you choose Copy to another location, enter the cell reference in the Copy to text box.

Step 4:Check Unique records only, then click OK.

Step 5:
The unique values will be displayed.


Remove Duplicate Values
Note: Removing duplicate values will permanently delete them.

Step 1:Select the range of cells that you wish to filter for duplicate values.

Step 2:Click the Data tab. Choose Remove Duplicates from the Data Tools group.

Step 3:If you have more than one column of data, select Expand the selection. Then click Remove Duplicates.
 Step 4:Select the columns that contain the duplicates you would like to remove. Click OK.

Step 5:
The duplicate values will be permanently deleted from the data when you click OK.


Quick Format Unique/Duplicate Values

Step 1:Select the range of cells you wish to filter for unique/duplicate values.

Step 2:
Under the Home tab, click Conditional Formatting.

Step 3:Select Highlight Cells Rules, then Duplicate Values...

Step 4:
Choose whether you want to format Duplicate or Unique values.

Step 5:
Select the formatting colors.

Step 6:Click OK.

Step 7:
The unique/duplicate values will be highlighted.



Advanced Format Unique/Duplicate Values

Step 1:Select the range of cells that you wish to filter for unique/duplicate values.

Step 2:
Click Conditional Formatting in the Styles group, then select Manage Rules...

Step 3:Click New Rule... if you would like to create a new formatting rule. Then jump down to Step 7.

Step 4:To edit the current rule, select Edit Rule... and then select the current conditional format.

Step 5:You may change the range of cells by clicking the Collapse Dialog button.

Step 6:Select the cells on the worksheet, then click the Expand Dialog button.

Step 6:You can also click Edit Rule... to change the formatting on the current rule. Then jump down to Step 8.

Step 7:Select Format only unique or duplicate values under Select a Rule Type.

Step 8
:
Click Format...

Step 10:Use the tabs at the top to choose formatting options. Click OK.

Step 11:
Click OK on the New/Edit Formatting Rule window.

Step 12:
Click Apply then OK on the Conditional Formatting Rules Manager.

 

Adapted from:
http://office.microsoft.com/en-us/excel-help/filter-for-unique-values-or-remove-duplicate-values-HP010342518.aspx


Related Articles


helpdesk.etown.edu/helpconsole2012/kb/default.aspx?pageid=unique_and_duplicate_values