How To Filter Duplicate Values in Excel & Google Sheets

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

 

 

Advanced Filter to Hide Duplicate Values

1. Click in the range where you wish to apply the filter to (any cell from B3 to B25).
2. In the Ribbon, go to Data > Sort & Filter > Advanced.

 

 

3. You can either filter the list in place, or you can put the results in a different range on your worksheet.

 

 

4. Tick the Unique records only checkbox, and then click OK.

 

 

5. The rows with duplicate values are hidden. Notice that the row numbers are now in blue and some of the rows do not appear.
6. In the Ribbon, go to Data > Sort & Filter > Clear to remove the filter.

 

 

Filter Duplicate Values to a New Location

1. Select the range you wish to filter (e.g., B4:B25).
2. In the Ribbon, go to Data > Sort & Filter > Advanced.
3. Choose Copy to another location.
4. Select the new location range in the Copy to: box and tick the Unique records only checkbox.

 

 

This leaves you with the original list unchanged and a separate list of only unique values.

 

 

Filter Duplicates Rows

The sections above show how to filter duplicate values, and each example has just one column, but you can also filter when there are duplicate rows in a table. Apply the filter to the entire range, and hide any rows where all columns are identical.

 

 

1. Click in the range you wish to apply the filter to (any cell from B3 to E11).
2. In the Ribbon, select Data > Sort & Filter > Advanced.
3. Filter the list in place and tick the option to show unique records only.
4. Click OK.

 

 

Use COUNTIF to Filter Duplicates

To filter using the COUNTIF Function alongside standard filtering, first add a new helper column to your worksheet.

 

 

1. Click in C4 and type in the formula:

=IF(COUNTIF($B$4:B4,B4)=1,1,0)

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

 

 

4. In the Ribbon, go to Home > Filter.

 

 

5. Filter buttons/arrows now appear in the heading row of your list.

 

 

6. Click the filter arrow in the helper column and check 1 as the value to show.

 

 

Your list is now filtered to show only unique values.

 

 

Filter Duplicates in Google Sheets

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

1. Click in C4 and type in the formula:

=IF(COUNTIF($B$4:B4,B4)=1,1,0)

2. Copy the formula down to Row 25.

 

 

3. In the Menu, go to Data>Create Filter.

 

 

4. Remove the tick from the 0 to exclude them and show only the 1s.

 

 

5. Click OK to filter the list.