Excel UNIQUE Function: A Practical Stepwise Guide
Learn how to use the Excel UNIQUE function to remove duplicates, spill results, and pair with FILTER and SORT for clean data. A practical XLS Library guide with syntax, examples, and best practices.
UNIQUE is an Excel function that returns the distinct values from a range or array. It spills the results into adjacent cells automatically.
What the UNIQUE function does and why it matters
The unique function excel is a cornerstone for data cleaning and analysis in modern spreadsheets. It returns the distinct values from a listed range or array, automatically placing the results into adjacent cells thanks to dynamic arrays. When you work with large datasets, duplicates can obscure trends, inflate counts, and complicate filtering. The UNIQUE function helps you trim noise, create clean categories, and feed other formulas with reliable inputs.
According to XLS Library, teams that adopt the unique function excel can streamline reporting, reduce manual deduplication, and unlock new possibilities for dashboards and data validation. Whether you’re building a customer list, consolidating survey results, or preparing data for a pivot table, understanding how UNIQUE behaves—what it considers a duplicate and how it spills—gives you a reliable tool to shape clear, accurate analyses.
Syntax and spill behavior
The syntax for the unique function excel is straightforward: =UNIQUE(array, [by_col], [exact_once]). The array argument is required and can be a vertical or horizontal range. The by_col parameter is optional and controls whether Excel scans by columns instead of rows when you supply a multi-column array. The exact_once parameter, also optional, returns only values that appear exactly once in the array. In modern Excel, the function spills automatically, placing as many results as there are unique values, into adjacent cells to the right or below the formula. If you need to capture unique values in a specific order, you can wrap the result with SORT or FILTER.
Basic examples: deduplicating a single column
Suppose you have a list of names in A2:A20 that includes duplicates. Enter =UNIQUE(A2:A20) in B2. Excel spills the list of distinct names into cells B2, B3, and so on. If you want to maintain case-insensitive deduplication, you can wrap the input with LOWER, i.e., =UNIQUE(LOWER(A2:A20)). The result is a compact, tidy column of unique values you can feed into a drop-down list, a pivot table, or a summary chart.
If your dataset contains blanks, UNIQUE will treat empty cells as a value, producing a unique empty entry unless you filter out blanks first with FILTER. For example: =UNIQUE(FILTER(A2:A20, A2:A20<>'')).
People Also Ask
What is the Excel UNIQUE function and what does it do?
UNIQUE returns the distinct values from a range or array and spills the results into adjacent cells in modern Excel. It simplifies deduplication and prepares clean inputs for dashboards and analyses.
UNIQUE returns distinct values from your data and spills them automatically for easy use in your analyses.
Which Excel versions support UNIQUE?
UNIQUE is available in Office 365 and Excel for Microsoft 365 and later. Older perpetual-license versions do not support dynamic arrays, so you may need alternative methods.
UNIQUE works in modern Excel with dynamic arrays; older versions don’t support it.
How do I extract unique values from a single column?
Use =UNIQUE(A2:A100) to return distinct values from that column. The results spill automatically into adjacent cells.
Just use the UNIQUE function with your column range and let Excel spill the results.
Can UNIQUE handle multiple columns?
Yes. Use =UNIQUE(A2:B100) to get distinct row combinations. This is useful for identifying unique customer-product pairs.
Yes, you can deduplicate by rows with a two column range.
How can I filter unique values by a condition?
Combine with FILTER, for example =UNIQUE(FILTER(A2:A100, B2:B100="Yes")). This returns unique values from A where B equals Yes.
Pair UNIQUE with FILTER to constrain what gets deduplicated.
How do I sort the results of UNIQUE?
Wrap with SORT: =SORT(UNIQUE(A2:A100)). You can specify order with additional arguments.
Use SORT to control the order of the unique results.
The Essentials
- Use UNIQUE to remove duplicates quickly
- Combine with FILTER and SORT for dynamic lists
- Be aware of dynamic array requirements and compatibility
- Plan for blanks and case sensitivity with appropriate wrapping