In this tutorial, you will learn how to filter duplicates in Google Sheets.
How To Filter Duplicates In Google Sheets
Quick Navigation
When working with Google Sheets spreadsheets , you may want to flag duplicate values. This will be a tedious task when performed manually, but luckily there is a built-in function that can do it for you.
The COUNTIF function can be used to count the number of cells that contain a certain value. We can use this function to filter out duplicate values in our dataset.
Once we’ve flagged rows with duplicate values, we can apply a filter to either remove these values or display just the duplicates.
In this guide, we will show you how to use the COUNTIF function to filter duplicate values in Google Sheets.
How To Use a Filter to Show All Duplicates In a Column in Google Sheets
Here’s how to use a filter to show all duplicates in a column in Google Sheets.
Step 1
First, we’ll create a new column that we’ll use to flag values in our target range that are duplicates.
Step 2
Next, we’ll type the formula =COUNTIF($A$2:A2,A2)>1 on the first cell of the new column.
This formula will determine if the current value has appeared previously in the range.
Hit the Enter key to return the result of the formula.
Step 3
Next, we’ll use the AutoFill feature to copy the formula down the rest of the column.
Our formula uses an absolute reference to ensure that the range to look back on will adjust depending on the location of the current row.
Step 4
Next, select the range that contains both columns.
Step 5
We’ll now add a filter to the selected range.
In the Data menu, select the Create a filter option.
Step 6
Each column header should now have a filter icon. Click on the icon in the second column and adjust the options so that only TRUE values appear. Click on OK to apply the new filter.
Step 7
Your range should now show all values that are duplicates of a prior value in the given range.
Summary
This guide should be everything you need to filter duplicates in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.