In Excel, there are several ways to filter for unique values—or remove duplicate values:
Applies to: Excel for Office 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013 Excel 2010, Excel 2007.
- Click Data > Sort & Filter > Advanced to filter for unique values.
- Remove duplicate values by pressing Data > Data Tools > Remove Duplicates.
- To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.
Learn about filtering for unique values or removing duplicate values
Filtering for unique values and removing duplicate values are two similar tasks, since the goal is to display a list of unique values. There is a major difference, however: When you filter for unique values, the duplicate values are only hidden for a short period of time. However, removing duplicate values means that you are completely deleting duplicate values. So in this way, you are capturing unique values but they will be erased. Before doing so, it is important that you assess whether you need such data because this can result in unreliable data, data loss, and inaccurate calculations.
A duplicate value is one in which each values in a minimum of one row are equal to every value in another row. A comparison of duplicate values is based on what appears in the cell—not the base 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 practice to begin to test the filter is on. Or conditionally format on for unique values. This is to confirm that you reach your expected results.
Filter for unique values
Follow these steps:
- Click the range of cells. Or check that the active cell is found in a table.
- Press Data > Advanced (in the Sort & Filter group).
- In the Advanced Filter popup box, do one of the following:
To filter the range of cells or table in place:
- Choose Filter the list, in-place.
To copy the results of the filter to another location:
- Pick Copy to another location.
- In the Copy to box, type a cell reference.
- Alternatively, press Collapse Dialogue to hide the popup window for the moment. Then, choose a cell on the worksheet. Next, select Expand.=
- Check the Unique records only. Then, press OK.
The unique values from the cell range will copy to the new, specified location.
Remove duplicate values
When you erase duplicate values, the only effect is on the values in the range of cells or table. Other values beyond the range of cells or table will stay the same. When duplicates are removed, the first occurrence of the value in the list is saved, but other similar values are removed.
Because you are permanently deleting data, it’s highly recommended that you copy the original range of cells or table to another worksheet or workbook before deleting duplicate values.
Follow these steps:
- Choose the range of cells, or check that the active cell is located in a table.
- On the Data tab, pick Remove Duplicates (in the Data Tools group).
- Do one or more of the following:
- Under Columns, pick one or more columns.
- To rapidly select all columns, choose Select All.
- To quickly clear all columns, press Unselect All.
If the range of cells or table includes several columns and you want to only choose a few columns, you may find it easier to pick Unselect All, and then under Columns, click those columns.
Note: Data will be deleted from each column, even if you don’t pick all the columns at this step. For example, if you select Column1 and Column2, but not Column3, then the “key” used to detect duplicates is the value of BOTH Column1 & Column2. If a duplicate is spotted in those columns, then the whole row will be removed, containing other columns in the table or range.
- Press OK, and a message will come up to confirm how many duplicate values were deleted, or how many unique values are still kept. Select OK to close this message.
- Undo the change by clicking Undo (or pressing Ctrl+Z on the keyboard).
Problems removing duplicates from outlined or subtotaled data
You cannot erase duplicate values from outline data that is outlined or that has subtotals. To delete duplicates, you must erase both the outline and the subtotals. For more information, see Outline a list of data in a worksheet and Remove subtotals.
Conditionally format unique or duplicate values
Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.
Follow these steps:
- Choose one or more cells in a range, table, or PivotTable report.
- On the Home tab, in the Style group, pick the small arrow for Conditional Formatting, and then select Highlight Cells Rules, and select Duplicate Values.
- Type the values that you prefer to use, and then select a format.
Follow these steps:
- Click one or more cells in a range, table, or PivotTable report.
- On the Home tab, in the Styles group, select the arrow for Conditional Formatting, and then press Manage Rules to present the Conditional Formatting Rules Manager popup window.
- Do one of the following:
Creating a conditional format:
- To create a conditional format, select New Rule to show the New Formatting Rule popup window.
Editing a conditional format:
- To alter 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 clicking 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 click Edit rule to display the Edit Formatting Rule popup window.
- Under Select a Rule Type, click Format only unique or duplicate values.
- In the Format all list of Edit the Rule Description, choose either unique or duplicate.
- Click Format to display the Format Cells popup window.
- Select the number, font, border, or fill format that you want to apply when the cell value satisfies the condition, and then click OK. You can choose more than one format. The formats that you select are displayed in the Preview panel.