See all How-To Articles

How to Filter Unique Values in Excel & Google Sheets

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

 

ilter unique records intro

 

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.

 

countduplicates advanced

 

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

 

filter unique records action

 

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

 

filter unique records unique

 

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

 

filter unique records filtered

 

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.

 

filter unique records helper

 

  1. Click in C3 and type the following formula:
=IF(COUNTIF($B$3:B3,B3)=1,1,0)
  1. Copy it down to Row 11.
  2. If the destination appears more than once, a zero appears next to it when it appears for the second or subsequent time.

 

filter unique records count if

 

  1. In the Ribbon, select Home > Filter.

 

filter duplicate values 12

 

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

 

filter unique records filter

 

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

 

filter unique records select 1

 

Your list now shows only unique values.

 

filter unique records

 

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)
  1. Copy the formula down to Row 11.

 

filter unique records gs helper

 

  1. Select Data > Create Filter from the Menu

 

filter duplicate values 17

 

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

 

filter duplicate values 18

 

  1. Click OK to filter the list.

 

filter unique records gs filtered