How to Remove Duplicates in Google Sheets

Google Sheets is a powerful and versatile spreadsheet application that allows you to store, manipulate, and analyze data. However, sometimes you may encounter duplicate data in your sheets, which can affect the accuracy and efficiency of your work. Fortunately, Google Sheets offers several ways to find and remove duplicates in your data, depending on your needs and preferences. In this article, we will show you how to remove duplicates in Google Sheets using three different methods: the built-in Remove Duplicates tool, the UNIQUE function, and an add-on.

Method 1: Using the Remove Duplicates Tool

One of the easiest and fastest ways to remove duplicates in Google Sheets is using the built-in Remove Duplicates tool. This tool allows you to select a range of cells or columns and delete any rows that have duplicate values in them. Here are the steps to use this method:

Select the range of cells or columns that you want to remove duplicates from. You can also select the entire sheet by clicking on the gray box on the top-left corner of the sheet.
Go to the Data menu > Data cleanup > Remove duplicates.
In the Remove duplicates window that appears, select which columns you want to include in your search for duplicate data. You can also check or uncheck the option “Data has header row” if your data has a header row or not.
Click on Remove duplicates. You will see a message telling you how many duplicate rows were found and removed, and how many unique rows remain. Click OK to close the message.

Note: This method will permanently delete the duplicate rows from your sheet, so make sure you have a backup copy of your data before using it.

Method 2: Using the UNIQUE Function

Another way to remove duplicates in Google Sheets is using the UNIQUE function. This function returns a list of unique values from a range of cells or columns, without deleting any data from your sheet. You can use this function to create a new sheet or a new range of cells with only unique values. Here are the steps to use this method:

Click on an empty cell where you want to output the unique values.
Type =UNIQUE( and then select the range of cells or columns that you want to remove duplicates from. Alternatively, you can type the range manually, such as A1:A10.
Press Enter or close the parenthesis to complete the function. You will see a list of unique values from your selected range appear in the output cell and below it.
If you want to copy and paste the unique values to another location, make sure you use Paste special > Paste values only option, otherwise you will paste the formula instead of the values.

Note: This method will not affect your original data, but it will create an array formula that will automatically update if you change your data. If you want to prevent this, you can copy and paste the values only as mentioned above.

Method 3: Using an Add-on

The third way to remove duplicates in Google Sheets is using an add-on. An add-on is a third-party extension that adds extra features and functionality to Google Sheets. There are many add-ons available for Google Sheets that can help you with various tasks, including removing duplicates. One of them is Remove Duplicates by AbleBits, which offers more options and flexibility than the built-in tool or the UNIQUE function. Here are the steps to use this method:

Install the Remove Duplicates by AbleBits add-on from the Google Workspace Marketplace. You can also access it from the Add-ons menu > Get add-ons in Google Sheets.
After installing the add-on, go to the Add-ons menu > Remove Duplicates > Start.
In the sidebar that opens, select the range of cells or columns that you want to remove duplicates from.
Choose whether you want to compare all columns or specific columns for duplicate values.
Choose what you want to do with the duplicate rows: delete them permanently, move them to another location, copy them to another location, or color them.
Click on Find duplicates or unique > Find duplicates.
You will see a summary of how many duplicate rows were found and processed according to your choice. You can also click on View results to see more details.

Note: This method requires an internet connection and a subscription fee after a 30-day free trial period.

Conclusion

In this article, we have shown you how to remove duplicates in Google Sheets using three different methods: the built-in Remove Duplicates tool, the UNIQUE function, and an add-on. Each method has its own advantages and disadvantages, so you can choose the one that suits your needs and preferences best.

We hope this article was helpful and informative for you. If you have any questions or feedback, please feel free to leave a comment below.

¹: https://workspace.google.com/marketplace/app/remove_duplicates/1019957405880

How to Remove Duplicates in Google Sheets