Filter values and remove duplicates
In Microsoft Excel, several ways to filter the unique values or remove duplicate values:
To filter the unique values, use the Advanced Filter Options control group and filter the Data tab.
To remove duplicate values, use the Remove Duplicates the Data Tools group on the Data tab.
To highlight the unique or duplicate values, use the setup command Conditional Formatting in the Styles group Origin tab.
Filter 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.
In the Advanced Filter dialog box, do one of the following:
To filter the range of cells or table displayed, click Filter the list in place.
To copy the filter results to another location, perform the following actions:
Click Copy to another location.
In the Copy to box, enter a cell reference.
Or, click Shrink dialog box to temporarily hide the dialog box, select the cell in the spreadsheet and click Expand the dialog box.
Activate only check box without duplicates, and then click OK.
Unique values in the selected range are copied to the new location.
Delete duplicate values
When you remove duplicate values, only the values of the range of cells or table are deleted. Other values outside the range of cells or table are not altered or moved.
Because you permanently delete the data, it is advisable to copy the original range of cells or table in another worksheet before deleting.
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.
Perform at least one of the following:
Under Columns, select one or more columns.
To quickly select all columns, click Select All.
To quickly clear all columns, click Deselect All.
If the range of cells or table column contains numbers when you only want to select a few, it may be easier to click Deselect All and select the columns under Columns.
Click OK.
Excel displays a message indicating the number of duplicate values removed and the remaining number of unique values or to indicate that no duplicate value has been deleted.
Click OK.
Problem: I am having problems removing duplicate values in structured data plan or containing subtotals.
Apply conditional formatting to single or double values
NOTE You can not apply conditional formatting to fields in the Values area of a PivotTable report with unique or duplicate values.
Quick formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Rules highlighted cells.
Select values in duplicate.
Enter the values to use and then select a format.
Advanced Formatting
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.
The Manager dialog box rules conditional formatting appears.
Do one of the following:
To add a conditional format, click New Rule.
The New Rule dialog formatting appears.
To change a conditional format, do the following:
Make sure the spreadsheet or the appropriate table is selected in the Show list box rules for formatting.
If you wish, you can change the range of cells by clicking Collapse dialog box in the Apply to box to temporarily hide the dialog box and then selecting the new range of cells on the worksheet. Then select Expand the dialog box.
Select the rule and click Edit Rule.
The Edit Rule formatting dialog box appears.
Under Select a Rule Type, click Apply formatting only unique or duplicate values.
Under Edit the Rule Description, in the Apply formatting to all values list, select single or double
Click Format to display the Format Cells dialog.
Select the number, font, border or filling you want to apply when the cell value meets the condition, then click OK.
