How to Filter Unique Values in Excel & Google Sheets

This tutorial demonstrates how to filter unique values in Excel and Google Sheets.

 

 

Filter Unique Values With Advanced Filter

1. Click in the list that contains the duplicate values and then, in the Ribbon, select Data > Sort & Filter > Advanced.

 

 

2. You can either select Filter the list, in place or Copy to another location. For this example, select Filter the list, in place.

 

 

3. Then, ensure that Unique records only is ticked and click OK.

 

 

The list then shows only unique values, as any duplicate rows are hidden.

 

 

Note: If you select Copy to another location then a list of unique values is placed at the range you have selected the list to be copied to. All the rows in your worksheet are still visible.

COUNTIF and Filter

To filter using the COUNTIF Function and standard Filter feature, you need an additional helper column.

 

 

1. Click in C3 and type the following formula:

=IF(COUNTIF($B$3:B3,B3)=1,1,0)

2. Copy it down to Row 11.
3. If the destination appears more than once, a zero appears next to it when it appears for the second or subsequent time.

 

 

4. In the Ribbon, select Home > Filter.

 

 

5. Drop-down arrows appear in the heading row of your list.

 

 

6. Click the drop-down arrow in the helper column and select 1 as the value to show.

 

 

Your list now shows only unique values.

 

 

Filter Unique Values in Google Sheets

Google Sheets does not have an advanced filter. You can filter duplicate values by using the same formula as you would use in Excel.

1. Click in C3 and type the following formula

=IF(COUNTIF($B$3:B3,B3)=1,1,0)

2. Copy the formula down to Row 11.

 

 

3. Select Data > Create Filter from the Menu

 

 

4. Remove the tick from the 0 so that you filter only on the ones.

 

 

5. Click OK to filter the list.