Filter for unique values or remove duplicate values

Filtering for unique values and removing duplicate values are two similar tasks, since the objective is to present a list of unique values. There is a critical difference, however: When you filter for unique values, the duplicate values are only hidden temporarily. However, removing duplicate values means that you are permanently deleting duplicate values.

A duplicate value is one in which all values in at least one row are identical to all of the values in another row. A comparison of duplicate values depends on what appears in the cell—not the underlying value stored in the cell. For example, if you have the same date value in different cells, one formatted as "3/8/2006" and the other as "Mar 8, 2006", the values are unique.

Check before removing duplicates: Before removing duplicate values, it's a good idea to first try to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect.

Follow these steps:

1. Select the range of cells or ensure that the active cell is in a table.

2. Select Data > Advanced (in the Sort & Filter group).

   

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

   • To filter the range of cells or table in place:

     • Select Filter the list, in-place.

   • To copy the results of the filter to another location:

     • ​​​​​​​Select Copy to another location.

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

     • Alternatively, select Collapse Dialog  to temporarily hide the popup window, select a cell on the worksheet, and then select Expand .

     • Check the Unique records only, then select OK.

   • The unique values from the range will copy to the new location.

When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted. 

Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

Follow these steps:

1. Select the range of cells or ensure that the active cell is in a table.

2. On the Data tab, select Remove Duplicates (in the Data Tools group).

   

3. Do one or more of the following:

   • Under Columns, select one or more columns.

   • To quickly select all columns, select Select All.

   • To quickly clear all columns, select 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 select​​​​​​​ Unselect All, and then under Columns, select those columns.

     Note: Data will be removed from all columns, even if you don't select all the columns at this step. For example, if you select Column1 and Column2, but not Column3, then the “key” used to find duplicates is the value of BOTH Column1 & Column2. If a duplicate is found in those columns, then the entire row will be removed, including other columns in the table or range.

4. Select OK, and a message will appear to indicate how many duplicate values were removed, or how many unique values remain. Select OK to dismiss this message.

5. Undo the change by select Undo (or pressing Ctrl+Z on the keyboard).

Quick formatting

Follow these steps:

1. Select one or more cells in a range, table, or PivotTable report.

2. On the Home tab, in the Style group, select the small arrow for Conditional Formatting, and then select Highlight Cells Rules, and select Duplicate Values.

   
    

3. Enter the values that you want to use and then choose a format.

Advanced formatting

Follow these steps:

1. Select one or more cells in a range, table, or PivotTable report.

2. On the Home tab, in the Styles group, select the arrow for Conditional Formatting, and then select Manage Rules to display the Conditional Formatting Rules Manager popup window.

   

3. Do one of the following:

   • To add a conditional format, select New Rule to display the New Formatting Rule popup window.

   • To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list. If necessary, choose another range of cells by selecting Collapse  button in the Applies to popup window temporarily hide it. Choose a new range of cells on the worksheet, then expand the popup window again . Select the rule, and then select Edit rule to display the Edit Formatting Rule popup window.

4. Under Select a Rule Type, select Format only unique or duplicate values.

5. In the Format all list of Edit the Rule Description, choose either unique or duplicate.

6. Select Format to display the Format Cells popup window.

7. Select the number, font, border, or fill format that you want to apply when the cell value satisfies the condition, and then select OK. You can choose more than one format. The formats that you select are displayed in the Preview panel.