How To Find & Remove Duplicates in Google Sheets

Last Updated on January 10, 2024 by Jake Sheridan

In this guide, you will learn all about finding, removing & highlighting duplicates in Google Sheets.

3 Ways To Find Duplicates in Google Sheets

Google Sheets provides various ways to find duplicates in Google Sheets.

Let’s go over some of the most popular and convenient methods you can use to look for duplicate values in your Google Sheets.

1. Using a helper column + COUNTIF

To use a helper column in conjunction with the COUNTIF function to flag duplicates in a column in Google Sheets or Excel, follow these steps:

Step 1

Open your spreadsheet in Google Sheets or Excel, and identify the column where you want to check for duplicates.

Let’s say you want to check for duplicates in Column A.

Step 2

In the first cell of your helper column (let’s say it’s B1), enter the following formula: =COUNTIF(A:A, A1).

This formula counts how many times the value in A1 appears in Column A.

Step 3

Drag the fill handle (a small square at the bottom-right corner of the cell) down the helper column to apply this formula to the rest of the cells in the column.

This action will copy the formula to the other cells, adjusting the row references accordingly.

2. Using conditional formatting

The COUNTIF function is integral in conditional formatting when finding duplicates because it allows you to count the number of times a specific value appears in a range.

When used in conditional formatting, it dynamically checks each cell against the specified range and returns a count of how often that value appears.

The COUNTIF function can be added as a custom formula to highlight cells where the count is greater than 1, indicating that the value appears more than once in the range.

This way, any duplicate entries are visually flagged through formatting changes (like a different cell color), making it easier to identify them in large datasets.

3. Using data cleanup suggestions

Google Sheets comes with a cleanup suggestions option which analyzes your sheet for possible improvements on your dataset, particularly with removing duplicates, trimming whitespace characters and fixing inconsistent number formatting.

To find duplicates in Google Sheets using the Cleanup suggestions, follow these steps:

Step 1

Open your Google Sheets document and select the column where you want to find duplicates.

For instance, if you’re looking for duplicates in Column A, click on the header of Column A to select the entire column.

Step 2

Click on “Data” in the menu bar at the top of your screen.

In the dropdown menu, look for and select “Data Cleanup.”

Step 3

This will open a sidebar with various data cleanup options.

Step 4

Clicking on any of the listed duplicate rows in the side panel will highlight the row in your sheet.

You can choose to ignore the duplicate or remove it on a row-to-row basis.

Step 5

If you’re confident that all duplicates should be removed, you can click on “Remove Duplicates.”

This will delete all but one instance of each duplicate entry, effectively removing all duplicates from your dataset.

How to highlight all duplicate cells in a single column in Google Sheets

We can use the COUNTIF function and the conditional formatting feature in Google Sheets to highlight duplicate cells.

To highlight duplicate cells, follow these steps:

Step 1

Open your Google Sheets document and select the range of cells where you want to find duplicates.

For example, if you want to find duplicates in cells A1 through A10, click and drag to select these cells.

In this example, we have a table containing all the individuals that participated in a certain business convention.

We want to know if there are any duplicate entries in our dataset.

We’ll select the first column of our range (column A) which contains the registration ID of each participant.

Step 2

Go to the menu bar and click on Format.

From the drop-down menu, select Conditional formatting.

This will open the Conditional Format Rules sidebar on the right side of your screen.

We’ll use the options available in the Single color tab.

Step 3

In the Conditional Format Rules sidebar, under the Format cells if… drop-down menu, select “Custom formula is.”

This allows you to enter a custom formula to determine which cells to format.

Step 4

For our selected range A2:A1000, the formula would be =COUNTIF(A$2:A$1000, A2)>1.

This formula counts the number of times each cell’s value appears in the range A2:A1000 and highlights the cell if it appears more than once.

Step 5

Click on the “Fill color” icon and select your preferred color to highlight the duplicate cells.

For now, let’s change our cell’s background color to red.

Step 6

Click Done in the Conditional Format Rules panelto apply the formatting.

Google Sheets will now highlight any cell in your selected range that contains a duplicate value.

In our current example, if there are any duplicate values in column A, they will be highlighted red, making it easier to identify them.

Highlighting all duplicates in multiple columns in Google Sheets

We’ve already covered how to highlight duplicates in a single column.

However, you may need to highlight all duplicates by comparing across columns as well.

We can start highlighting duplicates in multiple columns in Google Sheets by adjusting our custom formula.

Here’s how you can do it:

Step 1

For instance, let’s try finding duplicates in the range seen above (A2:C15).

Step 2

Click on Format > Conditional formatting.

This action opens the Conditional format rules sidebar on the right side of your screen.

Step 3

With the range still selected, in the Conditional Format Rules sidebar, ensure that the “Apply to range” field correctly shows your selected range.

In the formula input box, enter the following formula: =COUNTIF($A$2:$C$15, A2)>1. !– /wp:paragraph –>

This formula counts the occurrences of each cell’s value in the entire range and highlights the cell if it appears more than once.

The $ symbols are used to lock the column and row references, ensuring the formula checks the entire specified range.

Step 4

Click the Done button at the bottom of the Conditional Format Rules sidebar to apply your formatting rule.

Google Sheets will now highlight any cell in the selected range that contains a duplicate value.

Highlight the entire row if duplicates are in one column

To highlight the entire row in Google Sheets if duplicates are found in one column, we can use conditional formatting and a new custom formula.

Here’s a step-by-step guide you can follow to do this yourself:

Step 1

Open your Google Sheets document and decide which column you want to check for duplicates.

For this example, we’ll search the id column (column A) of our table for duplicates.

However, we want to set up our conditional formatting so the entire row is highlighted.

Step 2

Go to the menu bar and click on Format > Conditional formatting.

This will open the Conditional Format Rules sidebar on the right side of your screen.

Step 3

Under the “Format cells if” dropdown menu, select “Custom formula is.”

This option allows you to enter a formula to control the formatting.

In the formula box, enter the formula for finding duplicates in Column A.

Use the formula =COUNTIF($A$2:$A$114,$A2)>1.

This formula counts how many times the value in each cell of Column A appears in the range A2:A114.

If it appears more than once, the entire row will be highlighted.

This formula works since we expanded the cell range in the Apply to range textbox to cover all six of our columns.

Step 4

Click Done in the Conditional Format Rules sidebar to apply the formatting.

Google Sheets will now highlight any row in your selected range where the corresponding cell in Column A is a duplicate.

How to highlight complete row duplicates in spreadsheets

In the previous examples, we’ve covered how to highlight a row based on duplicates found in one column.

However, this method does not check if the entire row is a duplicate of another row.

We can adjust our formula to only highlight complete row duplicates.

Here’s how:

Step 1

Open your Google Sheets document and decide which column you want to check for duplicates.

Step 2

Go to the menu bar and click on “Format.” From there, select “Conditional formatting.”

This will open the Conditional Format Rules sidebar on the right side of your screen.

Step 3

Under the “Format cells if” dropdown menu, select “Custom formula is.”

This option allows you to enter a formula to control the formatting.

In the formula box, enter the formula for finding complete row duplicates:

=COUNTIF(ArrayFormula($A$2:$A$18&$B$2:$B$18&$C$2:$C$18),$A2&$B2&$C2)>1.

This formula uses an ArrayFormula to concatenate three columns using the “&” character.

If our concatenated string appears more than once, then that means the entire row has been duplicated elsewhere in our dataset.

Step 4

Click Done in the Conditional Format Rules sidebar to apply the formatting.

Google Sheets will now highlight any complete row duplicates in your dataset.

Highlight actual duplicates

You might notice that in the prior examples, highlighting duplicate values also highlights the first instance of the value.

If you wish to skip the first instance of a value and only highlight each succeeding instance, we’ll need to adjust our COUNTIF formula.

Here’s how you can highlight all actual duplicate values in a range:

Step 1

Open your Google Sheets document and decide which column you want to check for duplicates.

In this example, we want to highlight all real duplicate instances of a color name in column A.

For example, the color “Indigo” appears three times in our sheet above but we only want to highlight the instances in A7 and A9.

Step 2

Go to the menu bar and click on “Format.” From there, select “Conditional formatting.”

This will open the Conditional Format Rules sidebar on the right side of your screen.

Step 3

Under the “Format cells if” dropdown menu, select “Custom formula is.”

In the formula box, enter the formula for finding duplicates in Column A. Use the formula =COUNTIF($A$2:A2,A2)>1.

This formula counts how many times the value in each cell of Column A appears in the range up to the current cell.

The lack of “$” characters in the cell reference after the colon allows the end of our range to end as far as the current cell being checked.

Step 4

Click “Done” in the Conditional Format Rules sidebar to apply the formatting.

Google Sheets will now highlight any cell in your selected range that has appeared at least once before in the cells above it.

How do I group duplicates in Google Sheets?

Grouping duplicates in Google Sheets involves a few steps to identify and then organize duplicate entries together.

Here’s how you can do it:

Step 1

Open your Google Sheets document where you have the data with potential duplicates.

Let’s assume your data is in Column A.

Step 2

Select the entire range you would like to sort to group duplicates together.

Step 3

Select Data > Sort range and click Advanced range sorting options.

Step 4

You should now see a Sort range dialog box. Check the “Data has header row” option if applicable.

Next, select the column you wish to sort by and select A to Z as our sort order.

Click Sort to proceed.

Step 5

After sorting the range in ascending order, all duplicate values should now be grouped together since they should be sorted next to each other.

How do I isolate duplicates in Google Sheets?

Isolating duplicates in Google Sheets involves identifying and then separating the duplicate values from the rest of your data.

Here are the steps to achieve this:

Step 1

Open your Google Sheets document where you have the data with potential duplicates.

Assume you’re working with data in Column A.

We’ll create a new helper column (duplicates) to keep track of which row has a duplicate value.

Step 2

In our example above, we’ll enter the formula =COUNTIF($A$2:$A2,A2).

This formula counts the number of times the value in cell A2 appears in the entire range up to that cell.

Step 3

We’ll drag down the formula to fill the rest of our duplicates column.

Each row in our column should now indicate whether this is the first time a value has appears, the second time, and so on.

Step 4

Select the entire table and click Data > Create a filter.

Step 5

Next, click the filter icon in the header of the duplicates column and set a filter to show only the cells with a count greater than 1.

This action will filter your sheet to only show rows where the corresponding value in Column A is a duplicate.

Step 6

You can now copy these filtered rows to a new locationif you need to separate them from the original data for further analysis.

Can you filter duplicates in Google Sheets?

Yes, you can filter duplicates in Google Sheets.

You can create a new list of unique values from your data range using the UNIQUE function.

For example, if your data is in column A, you can type =UNIQUE(A:A) in a new column to generate a list of unique values from column A.

This method doesn’t remove duplicates from the original data but creates a new list without duplicates.

Summary

Hopefully this guide has given you an overview of finding, highlighting and removing duplicates in Google Sheets.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: