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.

Note: If the formula in the cells is different, but the values are the same, they are considered duplicates. For example, if cell A1 contains the formula =2-1 and cell A2 contains the formula =3-2, as long as the value is formatted the same, they are considered to be duplicate values. If the same value is formatted using different number formats, they are not considered duplicates.

Aug 10, 2016 - Looking to find all the duplicates in a spreadsheet? Technique will also work with older versions of Excel, and its Mac equivalent, Numbers. Select the column or list that you will count all duplicates, and click the Kutools > Select > Select Duplicates & Unique Cells. In the opening Select Duplicate & Unique Cells dialog box, check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need, and click the Ok button.

For example, if the value in cell A1 is formatted as 1.00 and the value in cell A2 is formatted as 1, they are not considered duplicates. You can create a rule to color code unique or duplicate data in your sheet. This is especially helpful when your data includes multiple sets of 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. 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: To Do this Filter the range of cells or table in place Select the range of cells, and then click Filter the list, in-place. Copy the results of the filter to another location Select the range of cells, click Copy to another location, and then in the Copy to box, enter a cell reference. Note: You cannot remove duplicate values from data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals first.