Jan names | filter | alpha |
Beth | Beth | Annette |
Jim | Jim | Annette |
Roger | Roger | Beth |
Joe | Joe | Bill |
Jeremy | Jeremy | Jeremy |
Bill | Bill | Jim |
Annette | Annette | Joe |
0 | 0 | Roger |
0 | #N/A | |
Beth | #N/A | |
Roger | #N/A | |
Jeremy | #N/A | |
Bill | #N/A | |
0 | #N/A | |
0 | #N/A | |
0 | #N/A | |
Jim | #N/A | |
Bill | #N/A | |
Annette | #N/A | |
joe | #N/A | |
0 | #N/A | |
0 | #N/A |
Filtering for 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 important. When you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values. A duplicate value is one where all values in the row are an exact match of all values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell. For example, if you have the same date value in different cells, one formatted as '12/8/2017' and the other as 'Dec 8, 2017', the values are unique. 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
- Select the range of cells, or make sure that the active cell is in a table.
- On the Data tab, in the Sort & Filter group, click Advanced.
- Do one of the following:ToDo thisFilter the range of cells or table in placeSelect the range of cells, and then click Filter the list, in-place.Copy the results of the filter to another locationSelect the range of cells, click Copy to another location, and then in the Copy to box, enter a cell reference.Note: If you copy the results of the filter to another location, the unique values from the selected range are copied to the new location. The original data is not affected.
- Select the Unique records only check box, and then click OK.
More options
Remove 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, click Remove Duplicates.
- Select one or more of the check boxes, which refer to columns in the table, and then click Remove Duplicates.Tip: If the range of cells or table contains many columns and you want to only select a few columns, clear the Select All check box and select only the columns that you want.
Apply conditional formatting to unique or duplicate values
- Select one or more cells in a range, table, or PivotTable report.
- On the Home tab, in the Styles group, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values.
- Select the options that you want in the New Formatting Rule dialog box, and then click OK.
Apply advanced conditional formatting rules to unique or duplicate values
- Select one or more cells in a range, table, or PivotTable report.
- On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.
- In the Style list, choose Classic, and then in the Format only top or bottom ranked values list, choose Format only unique or duplicate values.
- In the values in the selected range list, choose either unique or duplicate.
- In the Format with list, select an option for how you want the unique or duplicate values to be formatted.
Edit advanced conditional formatting rules
- Select one or more cells in a range, table, or PivotTable report.
- On the Home tab, in the Styles group, click Conditional Formatting, and then click Manage Rules.
- Make sure that the appropriate sheet or table is selected in the Show formatting rules for list.
- Select the rule, and then click Edit Rule.
- Select the options that you want, and then click OK.
Filter for unique values
- Select the range of cells, or make sure that the active cell is in a table.
- On the Data tab, under Sort & Filter, click the arrow next to Filter, and then click Advanced Filter.
- Do one of the following:ToDo thisFilter the range of cells or table in placeSelect the range of cells, and then click Filter the list, in-place.Copy the results of the filter to another locationSelect the range of cells, click Copy to another location, and then in the Copy to box, enter a cell reference.Note: If you copy the results of the filter to another location, the unique values from the selected range are copied to the new location. The original data is not affected.
- Select the Unique records only check box, and then click OK.
More options
Remove duplicate values
- Select the range of cells, or make sure that the active cell is in a table.
- On the Data tab, under Tools, click Remove Duplicates.
- Select one or more of the check boxes, which refer to columns in the table, and then click Remove Duplicates.Excel displays either a message indicating how many duplicate values were removed and how many unique values remain, or a message indicating that no duplicate values were removed.Tip: If the range of cells or table contains many columns and you want to only select a few columns, clear the Select All check box and select only the columns that you want.
Apply conditional formatting to unique or duplicate values
- Select one or more cells in a range, table, or PivotTable report.
- On the Home tab, under Format, click the arrow next to Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values.
- Select the options that you want, and then click OK.
Apply advanced conditional formatting rules to unique or duplicate values
- Select one or more cells in a range, table, or PivotTable report.
- On the Home tab, under Format, click the arrow next to Conditional Formatting, and then click New Rule.
- On the Style pop-up menu, click Classic, and then on the Format only top or bottom ranked values pop-up menu, click Format only unique or duplicate values.
- On the values in the selected range pop-up menu, click either unique or duplicate.
- On the Format with pop-up menu, select an option for how you want the unique or duplicate values to be formatted.
Edit advanced conditional formatting rules
- Select one or more cells in a range, table, or PivotTable report.
- On the Home tab, under Format, click the arrow next to Conditional Formatting, and then click Manage Rules.
- Make sure that the appropriate sheet or table is selected on the Show formatting rules for pop-up menu.
- Select the rule, and then click Edit Rule.
- Select the options that you want, and then click OK.