How to find and remove duplicates in Google Sheets

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, we'll show you how to find and automatically remove duplicates.

How to find duplicates in Google Sheets

If you only want to find duplicates, so you can decide whether or not to delete them, the easiest way to do this is by using the UNIQUE function. In Google Sheets, the UNIQUE function returns unique values (i.e., data that's not duplicated) from a data range.

To get a unique list of values, 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).

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.

Click any cell that contains data. Then, select the Data tab > Data cleanup > Remove duplicates.
From the Remove duplicates window that appears, select which columns you'd like to include in your search for duplicate data. Click Remove duplicates.
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.
Google Sheets will let you know how many duplicate values were removed.