=UNIQUE(array, [by_col], [exactly_once])One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.
Need more than a single UNIQUE formula? Try our free AI for Excel — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat.
array:
The range or array from which you want to extract unique values.
by_col:
A logical value (TRUE or FALSE) that specifies whether to compare values by column (TRUE) or by row (FALSE). The default is FALSE.
exactly_once:
A logical value (TRUE or FALSE) that specifies whether to return only values that occur exactly once in the array. The default is FALSE.
The UNIQUE function in Excel is a powerful tool that allows you to extract unique values from a range or array. It evaluates the specified range and returns a list of distinct values, eliminating any duplicates.
In simpler terms, the UNIQUE function helps you quickly identify and list unique entries in your data, making it easier to analyze and understand your datasets.
Imagine you are managing a customer database for a small business, and you have the following data in an Excel spreadsheet:
| Customer Name | Purchase Amount | Region |
|---|---|---|
| Alice | 200 | East |
| Bob | 150 | West |
| Charlie | 300 | East |
| Alice | 250 | West |
| Eve | 100 | East |
You want to find the unique customer names from your database.
To extract the unique customer names, you would use the UNIQUE formula as follows:
=UNIQUE(A2:A6)When you apply the formula, it returns a list of unique customer names. The output would be:
| Unique Customer Names |
|---|
| Alice |
| Bob |
| Charlie |
| Eve |
Here, the unique customer names are displayed without any duplicates.
The UNIQUE function is particularly useful when you need to analyze data sets with potential duplicates. It allows you to quickly generate a list of distinct entries, simplifying data analysis and reporting.
Understanding how to use the UNIQUE function can significantly enhance your data analysis capabilities in Excel, enabling you to extract meaningful insights from your datasets.
Happy analyzing!
You have a table where Column A contains a list of product names sold in a store, and you want to identify all the unique products that were sold during a specific period. You can use the UNIQUE function to extract a list of distinct product names from Column A. This allows for efficient inventory management and helps in understanding product diversity without the need for complex data manipulation or filtering techniques.
Beyond the UNIQUE function: Better Analyst is a full Excel AI that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free AI Excel assistant that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required.
Don't have a spreadsheet yet? Use our free AI spreadsheet generator to turn a text prompt into a downloadable Excel file — a full AI Excel sheet maker that builds headers, columns, and formulas from your description.
Create a free account for formula help, explanations, and spreadsheet analysis. Usage limits depend on your plan.
Browse our complete library of Excel formula guides
Compare your options
Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.
AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.