• Home

  • Productivity

  • App tips

App tips

3 min read

How to find and remove duplicates in Google Sheets

Let custom formulas and built-in tools do the work of spotting data doppelgängers for you.

By Jessica Lau · June 23, 2024
A hero image for Google Sheets app tips with the Google Sheets logo on a green background

Let's say you have a list of email addresses that you collected through a form on your website. You want to know how many email addresses you received, but you're worried that someone may have filled out the form twice, which would inflate your numbers. 

When you're working with large amounts of data in a spreadsheet, you're bound to have duplicate records. Whether it was human error or robots that put them there, those duplicates can mess with your workflows, documentation, and data analysis.

Here, we'll show you how to find duplicates in Google Sheets, so you can decide whether or not to delete them yourself. Plus, you'll also learn how to automatically remove duplicates and create a list of unique values in a few simple clicks.

How to find duplicates in Google Sheets 

If you only want to find data doppelgängers, your best bet is to highlight all duplicate content using conditional formatting and a custom formula. The formula varies slightly depending on how many columns contain repeated values. 

How to highlight duplicates in a single column in Google Sheets

If you only have one column with duplicate data, here's how to easily spot repeat values. 

  1. Highlight the data range you want to check for duplicate information. Then select Format > Conditional Formatting.

    Portion of Google Sheets worksheet with the data in column B highlighted. The format tab is selected with an arrow pointing to the conditional formatting option int he dropdown menu.

  2. From the Conditional format rules window that appears, click the dropdown menu under Format rules, and select Custom formula is.

  3. Enter a custom duplicate checking formula in the Value or formula bar. In this example, we're looking for duplicates in cells B2:B15, so the custom formula is =COUNTIF($B$2:$B$15,B2)>1. If your duplicates are in a different data range (for example, A2:A15), your custom formula would be =COUNTIF($A$2:$A$15,A2)>1.

    Conditional format rules window in a Google Sheets worksheet. The format rules section title is highlighted and a portion of the custom formula is visible in the values or formula bar.

  4. Customize how your duplicates will appear on the spreadsheet under Formatting style. By default, Google Sheets will highlight duplicate data in green. Then click Done. (Tip: If you change the fill color, choose a high-contrast color scheme, such as light yellow 3, to improve readability.)

    Portion of the conditional format rules window in a Google Sheets worksheet. The formatting style section title is highlighted.

  5. You can now review the duplicate data (highlighted) and decide whether you need to delete any redundant information.

    Portion of Google Sheets worksheet with duplicate data in column B highlighted.

How to highlight duplicates in multiple rows or columns in Google Sheets 

If you have duplicate data in multiple rows or columns, repeat steps one to three from above, but change the custom duplicate checking formula to =COUNTIF($A:$Z,Indirect(Address(Row(),Column(),)))>1

Tip: If you only want to scan for duplicates in specific rows or columns, simply update the data range under Apply range to match the cell range you want to check for repeats. 

GIF showing a portion of a Google Sheets worksheet with data selected in cells A2:D5 on the left and the conditional format rules window on the right. Four cells of duplicate data in the data range A2:D5 are highlighted. The data range in the conditional format rules window is changed from A2:D5 to A2:B5. Now there are only two cells of duplicate data in columns A and B highlighted .

Customize how your duplicates will appear on the spreadsheet under Formatting style. Then click Done

Portion of a Google Sheets worksheet. Four cells of duplicate data in the data range A2:D5 are highlighted.

How to remove duplicates in Google Sheets 

If you want to dive right into nixing redundant data without manually reviewing them first, Google has made this really easy to accomplish. Here's how to remove duplicate data in Google Sheets.

  1. Click any cell that contains data. Then, select the Data tab > Data cleanup > Remove duplicates.

    A Google Sheets worksheet with data in columns A and B (attendee names and attendee emails, respectively). The data tab in the ribbon is highlighted. From the dropdown menu of the data tab, an arrow points to the data cleanup option, and then another arrow points to the remove duplicates option.

  2. From the Remove duplicates window that appears, select which columns you'd like to include in your search for duplicate data. Click Remove duplicates

    A remove duplicates pop-up window in a Google Sheets worksheet. The following items are selected: Data has header row and columns to analyze: select all. The remove duplicates button is on the bottom right of the pop-up window.

    Note: If your spreadsheet includes a header row, be sure to select Data has header row, so that Google Sheets ignores this row when removing duplicates. 

  3. Google Sheets will let you know how many duplicate values were removed. 

A pop-up window in a Google spreadsheet with text that reads "4 duplicate rows found and removed;. 10 unique rows remain."

Bonus: How to find unique values in Google Sheets

If you want to keep your original data and get a list of unique values (i.e., data that's not duplicated) from a data range, you can use the UNIQUE function in Google Sheets. 

To do this, select an empty column in your spreadsheet. Then input the UNIQUE function using the cell range you want to scan for duplicates, leaving behind only unique values. For example, =UNIQUE(A2:B15)

A Google Sheets worksheet with original data in columns A and B (attendee names and attendee emails, respectively). Only unique data from columns A and B appear in columns D and E. Cell D2 is selected and the formula bar reads =UNIQUE(A2:B15).

Related reading:

  • How to find and remove duplicates in Excel

  • How to add a dropdown list in Google Sheets

  • How to freeze columns in Google Sheets

  • How to merge cells in Google Sheets

  • How to sort in Google Sheets

  • How to use IMPORTRANGE in Google Sheets

This article was originally published in May 2018 by Deb Tennen. The most recent update was in June 2024.

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.

tags
mentioned apps

Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
See how Zapier works
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'