In today’s data-driven world, organizing and analyzing information efficiently is crucial. Microsoft Excel provides a wide array of functions to make data management easier, and the UNIQUE formula is one such powerful tool. Whether you're working with sales records, employee lists, or survey results, the UNIQUE function can help you streamline your data by extracting distinct values from a dataset.
In this comprehensive guide, we will explore the UNIQUE formula in Excel, its applications, variations across Excel versions (2016, 2013, 2007), and how it compares with its usage in Google Sheets. This guide also addresses common issues like the formula not working, how to use it with conditions, across multiple columns, and more.
What is the UNIQUE Formula in Excel?
The UNIQUE() function is a dynamic array function introduced in Microsoft Excel 365 and Excel 2019. It allows users to extract a list of unique values from a range or array. It simplifies what used to require complex formulas or helper columns.
Syntax:
UNIQUE(array, [by_col], [exactly_once])
-
array: The range or array from which to return unique values. -
by_col(optional): TRUE compares columns against each other and returns unique columns. FALSE compares rows. -
exactly_once(optional): TRUE returns only values that occur once. FALSE (or omitted) returns all distinct values.
How to Use UNIQUE Formula in Excel
Let’s say you have a list of names in column A from A2 to A10, and some of them are repeated:
=UNIQUE(A2:A10)
This formula will return a list of unique names, eliminating duplicates.
UNIQUE Formula with Condition
While Excel doesn't support direct conditional logic inside UNIQUE(), you can combine it with FILTER():
=UNIQUE(FILTER(A2:A10, B2:B10="Sales"))
This example will return unique names where the department is "Sales".
UNIQUE Formula in Excel for Multiple Columns
If you want to find unique rows from multiple columns (e.g., names and departments):
=UNIQUE(A2:B10)
Excel will evaluate each row combination in both columns and return only the unique rows.
UNIQUE Formula in Excel Not Working
Common reasons include:
-
You're using an Excel version that doesn't support dynamic arrays (like 2016, 2013, 2007).
-
Typing error in formula or syntax.
-
Input range contains errors or blank cells.
Fixes:
-
Ensure you're using Excel 365 or 2019.
-
Use helper columns or legacy formulas like
Remove Duplicatesfeature for older versions.
UNIQUE Formula in Excel 2016 / 2013 / 2007
Unfortunately, Excel 2016, 2013, and 2007 do not support the UNIQUE() function.
Alternatives:
Use array formulas or helper columns:
=IF(COUNTIF($A$2:A2, A2)=1, A2, "")
Then filter out blank cells.
Another approach is using Power Query:
-
Select your data.
-
Go to Data > From Table/Range.
-
Open Power Query Editor.
-
Use "Remove Duplicates" feature.
UNIQUE Formula in Google Sheets
Google Sheets supports the UNIQUE() function in the following way:
=UNIQUE(A2:A10)
It behaves similarly to Excel 365. However, Google Sheets does not support the optional parameters by_col and exactly_once.
Example in Google Sheets with conditions:
=UNIQUE(FILTER(A2:A10, B2:B10="HR"))
This filters and extracts unique HR entries.
UNIQUE Formula Add in Excel
You don’t need to install anything for Excel 365 or Excel 2019. But if you're using an older version:
Options include:
-
Upgrade to Excel 365 for full functionality.
-
Use Office Add-ins like Kutools for Excel.
-
Use Power Query or VBA for advanced filtering.
UNIQUE Formula Function in Excel VBA
Here’s a simple VBA macro that mimics the UNIQUE function:
Function GetUniqueValues(rng As Range) As Variant
Dim cell As Range, dict As Object
Set dict = CreateObject("Scripting.Dictionary")
For Each cell In rng
If Not dict.exists(cell.Value) Then
dict.Add cell.Value, Nothing
End If
Next
GetUniqueValues = dict.keys
End Function
Use it like this:
=TRANSPOSE(GetUniqueValues(A2:A10))
Excel UNIQUE Formula in Cell
Just type the formula in any blank cell. If the result should populate downward:
=UNIQUE(A2:A100)
The formula dynamically expands.
Conclusion
The UNIQUE() function in Excel is a versatile, powerful tool that simplifies data analysis by removing duplicates. Whether you’re working on large datasets or need to prepare reports with unique entries, mastering this function will save time and improve accuracy.
However, always remember that UNIQUE() is only available in Excel 365 and Excel 2019. If you use older versions, consider workarounds using helper columns, Power Query, or even VBA.
Learn More on VibeScripted.xyz
If you are a professional in data management, admin roles, or facility management, visit VibeScripted.xyz for:
-
Detailed Excel tutorials
-
Excel function breakdowns like
IFERROR(),TRIM(),FIND() -
Expert guides on Admin & Facility operations
-
Real industry content, tips, and script writing ideas
At VibeScripted.xyz, we decode Excel for real-world professionals.