How to Remove Duplicates In Google Sheets

Let’s say you have a spreadsheet for expenses and some of the entries have been entered twice. The headaches that can come from that should be avoided at all costs. In some cases, wrong entries can be made even more times.

The larger your dataset is, the greater the chances of finding duplicate entries becomes. Because this is such a grave concern, Google has provided us with a number of tools we can use to find and remove duplicates in spreadsheets.

That’s why we’ve made this guide on removing duplicates in Google Sheets. We’re going to cover removing duplicates With the Data menu and the UNIQUE formula.

How to use Google Sheets ‘Remove duplicates’ feature

Open up your spreadsheet and select the range of data you want to analyze for duplicates. Then, click on the Data tab from the top menu bar and choose ‘Remove duplicates’ near the bottom.

When you select ‘Remove duplicates’, you’ll see a menu in which you can select how you want the search to work.

The first of these options is ‘Data has header row’. When you tick this box, you’re telling the search function that the first entry in each column is the header. That means the first row won’t be counted in the search.

If your columns have headers (friendly note: they always should), be sure to tick this box.

The second option is which columns to analyze. When you tick the ‘Select all’ box, all columns will be added to the search. If you only want to search a specific column(s), make sure to untick the ‘Select all’ box and only choose your desired columns.

Once you confirm the selection, you’ll only be told how many rows were removed and how many unique rows remain. It won’t tell you the location of the rows that were a duplicate.

Another thing to remember is that if you select all columns to be searched, every single column entry must match in order to be called a duplicate. This means that if all the entries of two rows match except one, it won’t be called a duplicate.

The final note with this method is that deleted rows don’t change the position of the rest of your data. Deleted rows will leave empty spaces behind them.

Remove duplicates with the UNIQUE function

Here’s the second way you can remove duplicates from your spreadsheet: the UNIQUE function.

The UNIQUE function works in a rather different manner. Where the above duplicate removal method removes duplicates, this function returns all the rows which are unique.

Here’s its syntax: =UNIQUE(cell_range)

We give the function a range to check and it returns all the values that are unique. That’s why we need to make sure that wherever we write the function, we have empty space. Otherwise, you’ll see the following error.

Since ‘asdf’ is in the way, we got an error.

Here’s the test dataset we’re working with:

In the next column, this is what our formula will look like: =UNIQUE(A1:A14)

And here’s the result of that formula:

We hope this article helped you in removing duplicates from your spreadsheet. We have a ton of content on Google Sheets like a dropdown menu guide, checkbox guide, and much more.