Syntax=UNIQUE(range, by_column, only_once)range – The data to filter duplicate entries.by_column – [Optional] To filter the data by columns or rows.only_once – [Optional] To filter data that appears only once. Filters all duplicates by default.
Example Usage=UNIQUE(A2:A10)
//Returns only unique values from the range A2:A10, discarding duplicates.
Simple guide to eliminate duplicates in Google Sheets
We’ll learn two methods to remove duplicates in Google Sheets in this tutorial. One simple and one not so simple but more versatile.
Background
Let’s say we are working with a dataset where there is a possibility of multiple duplicate entries. We want an output where all the duplicate entries are removed.
Duplicates in Google Sheets can be removed by a few methods. One is by using the UNIQUE function; another is by using the GROUP BY clause of the QUERY function. This article covers in detail how to remove duplicates in Google Sheets using the two methods mentioned.
Eliminate duplicates in Google Sheets using the QUERY function
Setting up the data range to find and remove duplicate values
For ease of querying, we’ll create a named range that we can refer to in the formula instead of specifying the range of the data sets.
1. Selecting the data range
- Press Ctrl + A (Windows) or Cmd+A (Mac) to select the entire data range.
- Go to Data.
- Select Named Ranges.
2. Naming the data range
- Give a suitable name to your data range. Here I am naming it “Dataset”.
- Click on Done to create a named range.
Eliminate duplicates in Google Sheets using Query function
We can remove duplicates in Google Sheets using the GROUP BY clause of the QUERY function.
The formula to eliminate duplicates entries in Google Sheets with the QUERY function is:
=QUERY(Dataset,"Select A,B,SUM(C) Group By A,B label SUM(C) 'total sales' ")- We have used the named range “Dataset” to refer to the range of the data.
- The query will remove duplicate entries by grouping the dataset in columns A and B.
- The output will be columns A, B, and the sum of the values of column C with the heading “total sales”, grouped by columns A and B, as shown below.
Using the UNIQUE command to eliminate duplicates in Google Sheets
The UNIQUE function returns only the unique values discarding the duplicate values.
The syntax of the UNIQUE function is:
=UNIQUE(range)Here as well, we can use the named range in the UNIQUE formula.
So, the UNIQUE formula to remove duplicates in Google Sheets is as under:
=UNIQUE(Dataset)On entering the formula, the function will return all the unique values from the range while eliminating the duplicates.
- This will keep UNIQUE rows of the dataset, without grouping them.
- The duplicate column values will be added, similar to what we did in the GROUP BY clause by using the SUM function in the query.
- Also, this will NOT change the order of the rows, unlike GROUP BY, where the unique rows were sorted in ascending order, as shown below.
Conclusion
The query function can be used to eliminate duplicates in Google Sheets. You can also remove duplicates using the UNIQUE function. The query function automatically removes duplicate entries in the output. If you want to know more about the Google Sheets Query function, you can go through the documentation here.
See Also
You can check out other equally good articles on Google Sheets here.
Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.
How to highlight cells in Google Sheets: Learn how to highlight the duplicates in Google Sheets without removing them.