2 ways to remove duplicates in Google Sheets

An article that teaches how to remove duplicates in Google Sheets (Content created by SpreadsheetClass.com)

Do you have duplicates in your Google spreadsheet that you want to remove? There are multiple easy ways to do this automatically instead of searching for them and deleting them one by one manually. In this article I am going to show you how to use the two main methods for removing duplicate entries / rows.

My preference is using the UNIQUE function, but you can also use the menu option without having to use a formula.

To remove duplicates with the “Remove Duplicates” menu option, follow these steps:

Select the cells that contain the duplicates to be removed
On the top toolbar, click “Data”, then in the drop-down menu click “Remove duplicates” (A menu will appear now)
Click the green “Remove duplicates” button

After following the steps above, the duplicates will be removed from your data.

To remove duplicates with the UNIQUE function, follow these steps:

Select the cell where you want your formula to be (Top left cell of the range where you want your unique list to appear)
Type =UNIQUE( to begin the function
Type the range of cells that contain the duplicates to be removed, such as A1:A17
Press “Enter” on the keyboard (Full formula will look like this: =UNIQUE(A1:A17)

After following the steps above, the formula will display the data / results with the duplicates removed.

Check out this lesson if you want to learn how to highlight duplicates instead of removing them.

This is a comprehensive lesson that covers both methods on how to remove duplicates including the menu option and the formula, but if you want more detail on using a formula to remove duplicates, read this article to learn how to use the UNIQUE function.

Removing duplicates from a single column

Let’s go over an example of removing duplicates in Google Sheets. As you can see in the image below, there is a list of names in column A.

This list contains duplicate names, and so we want to remove these duplicates to generate a list of unique names, meaning that only one of each name will be in the list.

To remove duplicate names, follow these steps:

Select the cells that contain the duplicate names (In this example the range is A2:A17)
On the top toolbar, click “Data”
When the drop-down menu appears, click “Remove duplicates”
Another menu will pop up, and when it does, click the big green button that says “Remove duplicates”

Part 1 of the example of removing duplicates in Google Sheets (Selected data with data menu clicked and remove duplicates option selected)

The “Remove duplicates” menu will give you the option to indicate if your selected data has a header row.

Part 2 of the example of how to remove duplicates in Google Sheets (The remove duplicates menu)

After following the steps above, the duplicate names are removed, and the shortened list will look like this:

Part 3 of the example of removing duplicates in Google Sheets (A unique list of names)

As you can see in the image above, the list is much shorter because the duplicate names have been removed.

This is great for when you want to generate a unique list of staff names by using data that has multiple entries for each staff member. It’s also great for when you want to remove duplicates when you’re not sure if a certain name has been manually entered more than once by accident.

This content was originally created by Corey Bustos / SpreadsheetClass.com

Removing duplicates from multiple columns

You can also remove duplicates with multiple columns selected in Google Sheets. When removing duplicates with multiple columns, the contents of the cells in both columns of the same row must be the same to be considered a duplicate entry / row.

For example, when you have a column containing first names, and another column containing last names, like in the example below… Even if there are duplicates in the first name column, if you refer to both columns when removing duplicates then having duplicate first names will not be enough for the entry/row to be considered duplicate, both the first name and the last name will have to be duplicates for Google Sheets to recognize the row as being a duplicate to be removed.

In the example below, there are two columns of data. One contains first names, and the other contains last names.

To remove the duplicates for multiple columns, follow these steps:

Select the range of cells that contains the duplicate data (In this example the range is A3:B13)
On the top toolbar, click the “Data” menu
In the drop-down menu that appears, click “Remove duplicates”
Another menu will pop up, and when it does, click the big green button that says “Remove duplicates”

Part 1 of the example of removing duplicates with multiple columns selected in Google Sheets (List of duplicate first and last names)

After following the steps above, the duplicates will be removed, and your data will look like this:

Part 2 of the example of removing duplicates with multiple columns selected in Google Sheets (List of unique first and last names)

Notice how even though some of the first names are duplicates, since the last names were different this meant that the row was not a duplicate.

Remove duplicates with a formula

If you want, you can use a formula to remove duplicates instead of using the menu option.

The formula that removes duplicates in Google Sheets is called the UNIQUE function.

Using a formula to remove duplicates is my preferred method, but I always prefer to use formulas when I can. A formula allows you to automatically remove duplicates when your data changes.

Just like with the menu option method to remove duplicates, the unique function can refer to a single column or multiple columns. Also, just like with the menu option method, when multiple columns are referred to with the UNIQUE function, the cells in the same row of the selected columns must both / all be duplicates for Google Sheets to recognize the entry as being a duplicate.

So if you refer to a single column with a formula like =UNIQUE(A1:A) than any duplicate values found within that single column will be considered a duplicate entry. But if you refer to multiple columns with a formula like =UNIQUE(A1:B) then the values from column A and column B must both be a duplicate for the entry to be considered a duplicate.