When we are working in Google Sheets, it’s quite common to have duplicates. In order to remove the duplicates in Google Sheets, we can use the UNIQUE function. The UNIQUE function in Google Sheets allows us to extract unique rows from a range, removing any data that is repeated. When you have a huge amount of data, such as responses from a Google Form, this is really useful. UNIQUE allows you to rapidly determine which values only appear once in the dataset.
To get a better idea about the UNIQUE function, read this article.
In our previous article, we understood how to highlight duplicates. In this article, let’s understand everything about how to highlight duplicates with the help of Google Sheet tips provided on this page.
|
Table of Contents |
UNIQUE Function Syntax
The UNIQUE function syntax has been explained below:
=UNIQUE(range)
range: The information to be filtered based on unique entries.
How to Remove Duplicates in Google Sheets?
Take a look at the following dataset.
Here, if you see, there are multiple duplicate entries in almost all the columns. Let’s understand how to remove the duplicates in Google Sheets with the help of the UNIQUE function.
- How To Highlight Duplicates In Google Sheets?
- The Ultimate Guide to Using Conditional Formatting in Google Sheets
- How to Create a Drop Down List in Google Sheets: Add/Remove/Customize Drop Down Menu
How to Remove Duplicates from Single Column?
Follow the steps listed below to remove the duplicates from the single column:
- Step 1: Open the Google Spreadsheet where you would like to remove the duplicates.
- Step 2: Now move to the cell where you want to see the results.
- Step 3: Enter the formula as “=UNIQUE(A2:A20)“. Here we have provided the cell range as A2 to A20. Based on the cell range you can alter your UNIQUE formula.
- Step 4: Press the “Enter” button.
This would eliminate the duplicates immediately, leaving you with a list of unique names.
Points to Note:
- To remove the list, either delete the first cell or select the entire range and press delete. Individual cells (except the first, which deletes the entire list) cannot be deleted in Google Sheets. Because this is a formula, any changes to the original list will immediately update it.
- If there is existing data in the cells that the unique function is supposed to populate, the unique function gives a #REF! error.
How to Remove Duplicates in Google Sheets with Extra Spaces?
When there are leading or trailing spaces, you may run into another issue. Oliver, for example, has a trailing space in the example below (in A12). When the UNIQUE function is applied to this data set, both names are considered unique, and both names are returned in the result.
Let’s understand how to overcome this issue by following the step listed below:
- Step 1: Open the Google Sheets where you want to remove the duplicates in Google Sheets which has extra space.
- Step 2: Now move to the cell where you would like to show the UNIQUE data (Removing duplicates).
- Step 3: Enter the formula as “=ArrayFormula(UNIQUE(TRIM(A2:A20)))“. If you see, here we have used the TRIM function along with the UNIQUE function.
- Step 4: Press the “Enter” button.
You will see the results as shown below. These spaces will be automatically removed and the final result will be displayed after the spaces have been removed.
How to Remove Duplicates from Multiple Columns?
In the above example, we saw how to remove the duplicates in a single column. Now let’s understand how to remove the duplicates from the Mulitple Columns by following the steps listed below:
- Step 1: Move the cell where you would like to remove the duplicates from multiple columns.
- Step 2: Now enter the formula “=UNIQUE(A2:C10)“. Based on the cell range you can alter this formula.
- Step 3: Press the “Enter” button and you will see the results as shown below.
In case if your dataset has extra spaces or has trailing text, then simply use the following formula – =ArrayFormula(UNIQUE(trim(A2:B10))).
