Is there a way to filter out duplicates in Excel?

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

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.

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

1. Make sure the check box Unique Records Only is ticked and then click OK.

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 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.

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

Filter duplicates rows using the Advanced Filter Function in Excel

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.

COUNTIF and the Filter Function in Excel

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

1. Click in C4 and type the following formula.

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.

1. In the Ribbon, select Home > Filter.

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

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

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

COUNTIF and the Filter Function 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 C4 and type the following formula

1. Copy the formula down to row 25.

1. Select Data>Create Filter from the Menu

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

Click OK to filter the list.