UNIQUE is an awesome Excel and Google Sheets formula that most people don’t know about. UNIQUE is used to create a list that doesn’t have any repeated values. For example, if you have a list of all your customer orders, the fastest way to get the unique list of customer names is to use the UNIQUE formula and link to your customer list in your order data set.
Before UNIQUE, the best way to create a de-duplicated list in Excel or Google Sheets was to use Remove Duplicates. To use Remove Duplicates, you must first copy your data and paste as values in a clear area of your sheet. Then with the data selected, go to Data > Remove Duplicates, and it will update that list to have one of each value (literally removing all the duplicate values). This is static – if your original data changes, you need to go back and do the whole Remove Duplicates routine again.
Let’s check out an example:
Here we have a list of customer orders. In column B, we want to see our unique list of customer names. The formula in cell B3 is =unique(E3:E21). By filling in the single cell B3, Excel or Sheets will fill in a list of cells based on how many unique values are in the list we are linking to
Unique is live
If a customer is renamed in the dataset, our dynamic array function will automatically update.
The UNIQUE of multiple columns
Unique can take multiple columns of input, and it will output the unique combination of those values. So if we link to the customer and location data, it will output the unique combination of customer names and locations. Note, the columns of data must be adjacent to each other.
Make sure you give UNIQUE enough space
UNIQUE will #REF! in Google Sheets or #SPILL! in Excel if it doesn’t have enough room to output all the data because some other value or formula is in the way. In the below example, we have data in cell B12. When we add another value into the customer list, the UNIQUE list is longer and needs to spill into B12. But it won’t overwrite the data we already had there, so instead it gives us a #REF! error in Sheets or #SPILL! in Excel. As soon as we remove that data, the error goes away and the list expands to fill the space
UNIQUE syntax
Most of the time, we’ll use the simple syntax =UNIQUE(array) where the array is our original list of data. But UNIQUE also has two optional inputs by_col and exactly_once:
=UNIQUE (array, [by_col], [exactly_once])
array – Range or array from which to extract unique values
by_col – [optional] How to compare and extract (by row or by column)
- FALSE (default) = By row
- TRUE = By column
Normally the unique function is deduplicating based on rows, but if we have data arranged horizontally instead of vertically, we need to input TRUE as the second input to unique for it to deduplicate the data.
exactly_once – [optional] Which type of values to extract.
- FALSE (default) = all unique values
- TRUE = values that occur once
Usually we use UNIQUE to get every value from our data set one time (regardless of how many times it showed up in our data set). But if we have a special case where we want to find the set of values that appear in our data set exactly one time, then we can input TRUE as the third input to UNIQUE
Share
Related articles
November 15, 2021
Search in Google Sheets (You’ll suddenly love Sheets)
Use Search in Google Sheets to quickly access any formatting and any functionality. Option + / on a Mac and Alt + / on PC
November 13, 2021
2021 Private Equity Recruiting: Outlook & Interview Advice
CarterPierce is a leader in private equity, growth equity, and hedge fund recruiting, placing analysts from Investment Banking and Consulting programs every year. With another private equity recruiting season kicking off, Recalc Academy founder Katie Fifer sat down with Jill Pierce to learn what her team is seeing across the private equity hiring and to hear her advice on landing a buy-side …
2021 Private Equity Recruiting: Outlook & Interview Advice Read More »