How To Filter Duplicate Values in Excel & Google Sheets

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

filter duplicate values 01

Advanced Filter Function

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

filter duplicate values 02

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

filter duplicate values 03

  1. Make sure the check box Unique records only is ticked, and then click OK.

filter duplicate values 04

  1. The rows with duplicate values will be hidden – you will notice that the row numbers are now in blue and some of the rows do not appear.
  2. In the Ribbon, select Data > Sort & Filter > Clear to remove the filter.

filter duplicate values 05

 

Filter Duplicate Values to an Alternative Range

  1. Select the range you wish to filter (ex B4:B25).
  2. In the Ribbon, select Data > Sort & Filter > Advanced.
  3. Select Copy to another location.

filter duplicate values 06

  1. Select the Range in the Copy to: box and make sure the Unique records only tick box is ticked.

filter duplicate values 07

 

Filter Duplicates Rows using the Advanced Filter Function

filter duplicate values 08

  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.

filter duplicate values 09

 

COUNTIF and the Filter Function in Excel

To filter using COUNTIF and standard Filter function, you need to add an additional helper column to your worksheet.

filter duplicate values 10

  1. Click in C4 and type the following formula.
=IF(COUNTIF($B$4:B4,B4)=1,1,0)
  1. Copy it down to row 25.
  2. If the city appears more than once, a zero will appear next to it when it appears for the second or subsequent time.

filter duplicate values 11

  1. In the Ribbon, select Home > Filter.

filter duplicate values 12

  1. Dropdown arrows will now appear in the heading row of your list.

filter duplicate values 13

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

filter duplicate values 14

Your list will now be filtered to only show unique values.

filter duplicate values 15

 

COUNTIF and the Filter Function 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 the following formula
=IF(COUNTIF($B$4:B4,B4)=1,1,0)
  1. Copy the formula down to row 25.

filter duplicate values 16

  1. Select Data>Create Filter from the Menu

filter duplicate values 17

  1. Remove the tick from the 0 so that you are only going to filter on the 1’s.

filter duplicate values 18

Click OK to filter the list.

 

filter duplicate values 19