UNIQUE Formula In Google Sheets
Quick Navigation
The UNIQUE formula returns a list of, well… unique values from a list.
No more using Find + Replace and accidentally deleting a load of useful stuff. Give it a list of keywords and it will remove duplicates.
Give it a list of URLs and it will de-dupe them.
Give it a list of… you get the idea.
What is the UNIQUE Formula?
The UNIQUE function is a quick way to remove duplicate lines from a table of data in a spreadsheet. It returns the unique rows in the provided source range while discarding duplicates.
The rows are returned in the order in which they first appear in the source range.
The syntax of the UNIQUE function is:
=UNIQUE(range)
In this, the argument range is the data range from which you want to extract the unique rows.
This can be really helpful when you have data that repeats information and you want to find out how many unique instances there are.
It’s common to use this function when evaluating form responses. They usually have a lot of entries with repetitive responses. In the example below, we have some responses to the question “Where are you from?”.
When to use UNIQUE for marketing stuff?
- Making your datasets nice and tidy (by removing any repeated values).
- De-duplicating keyword lists.
- General keyword research organisation.
How To Use The UNIQUE Formula in Google Sheets
The UNIQUE function can quickly create a new list that contains all the countries that are present in the original list of the responses, but every instance appears only once on the new list.
As the only variable of the function, you need to supply the range of the data, for example:
=UNIQUE(A2:A16)
The UNIQUE function is an array function. This means that you only need to write this function once and it automatically fills the whole array (range of cells) with the results. Just select a cell that you want to be the upmost cell of your new column containing the unique instances and write the formula once.
The only thing you should pay attention to is that there has to be enough empty area below this cell where the output from the UNIQUE function is expected to flow into.
If this area is not empty, but there are occupied cells below the formula, you get an error for the whole function.
Now, let’s discuss some common issues that come up with the UNIQUE formula.
Infinite ranges
Your form results may not be final yet.
What if you still accept responses and you don’t want to modify the range in the formula every time a new response arrives?
You can change the reference to an infinite column in the following way:
=UNIQUE(A2:A)
And this way the whole column A starting from A2 will be included in the function, regardless of the number of entries.
Deleting the original range
Sometimes you don’t want to keep the original records in the sheet, but only need the list of the unique values.
However, the output of the UNIQUE formula depends on the original list, so you can’t simply delete the source data.
If you need to copy and paste the distinct values that the UNIQUE formula returned, first you need to copy the distinct values and select a destination cell.
Now, select the option Edit > Paste > Paste Special > Paste values only from the menu. With this solution, you can remove the formula and only keep the results from it.
After that, you can now delete the original range with the duplicated values if you would like to.
Summary
Such a simple function, but believe me, it can save you headaches down the line. Especially if you working with large sets of keyword data.
The UNIQUE function works best when combined with other formulas.