You need to remove duplicate values from a list in Excel. Manually filtering or using advanced filters is time-consuming and does not update automatically. The UNIQUE function solves this by extracting distinct values from a source range. This article explains how to use the UNIQUE function to create a dynamic list that refreshes when your data changes.
Key Takeaways: Automatically Extracting Unique Values
- UNIQUE(array, [by_col], [exactly_once]): The core function syntax for returning distinct values from a specified range.
- Spill range: The dynamic array where results automatically appear, eliminating the need to copy formulas down a column.
- SORT(UNIQUE(array)): Combines functions to return a sorted list of unique items in one step.
What the UNIQUE Function Does
The UNIQUE function is part of Excel’s dynamic array functions. It scans a range of cells and returns a list of values that appear at least once, removing all duplicates. The result spills into adjacent cells, forming a spill range. This range automatically expands or contracts based on the number of unique items found. The function is available in Microsoft 365 and Excel for the web.
You must have a version of Excel that supports dynamic arrays. The function requires a source array, which can be a single column, a single row, or a two-dimensional range. Two optional arguments let you control the function’s behavior. The by_col argument tells Excel to compare columns instead of rows. The exactly_once argument filters the list to show only values that appear a single time in the source data.
Steps to Create a Unique List
Follow these steps to generate a list of unique values from your data.
- Select your output cell
Click on the cell where you want the first unique value to appear. Ensure there are several empty cells below or to the right for the spill range. - Enter the UNIQUE formula
Type an equals sign followed by UNIQUE(. Click and drag to select your source data range, or type its reference like A2:A100. Close the parenthesis and press Enter. - Review the spill range
Excel will display all unique values from your source. A blue border appears around the spill range. The formula exists only in the top-left cell of this range. - Sort the unique list
To sort the results, nest the UNIQUE function inside SORT. In your output cell, enter =SORT(UNIQUE(A2:A100)). Press Enter to get an alphabetically sorted unique list. - Use the exactly_once argument
To find items that appear only once in your source, add a third argument. Use the formula =UNIQUE(A2:A100, FALSE, TRUE). This will exclude any values that are duplicated.
Working with Two-Dimensional Ranges
You can extract unique values from a table with multiple columns and rows. Select a range like A2:B50 as your array argument. The UNIQUE function will return unique rows, comparing all values in each row. To compare by column, set the by_col argument to TRUE. Use the formula =UNIQUE(A2:D5, TRUE) to find unique columns in a horizontal data set.
Common Mistakes and Limitations
#SPILL! Error Appears
This error means something is blocking the spill range. Check for any data, merged cells, or a table border in the cells where results need to appear. Clear the obstructing content. Also, ensure you are not using the function inside an Excel Table, as tables do not support spill ranges. Convert the table to a regular range first.
Function Returns All Values, Not Unique Ones
If every value from your source appears, your data may contain extra spaces or non-printing characters. These cause Excel to see ‘Apple’ and ‘Apple ‘ as different values. Use the TRIM function to clean the source data first. Create a helper column with =TRIM(A2) and then use UNIQUE on that cleaned range.
Formula Does Not Update When Source Data Changes
The UNIQUE function is dynamic and should update automatically. If it does not, check that calculation is set to automatic. Go to Formulas > Calculation Options and select Automatic. Also, verify that your source range reference includes all relevant cells. Using a whole column reference like A:A can ensure no new data is missed.
UNIQUE Function vs. Remove Duplicates Tool
| Item | UNIQUE Function | Remove Duplicates Tool |
|---|---|---|
| Update Method | Updates automatically when source data changes | Static operation; must be rerun manually |
| Result Location | Creates a new, separate spill range | Removes duplicates directly in the original data range |
| Data Preservation | Keeps original data intact | Permanently deletes duplicate rows from the source |
| Formula Dependency | Is a live formula that can be edited | Is a one-time data command |
| Use with Other Functions | Can be nested inside SORT or FILTER | Works as a standalone feature on the Data tab |
You can now create self-updating lists of distinct items from any data set. Combine UNIQUE with SORT for organized reports or with FILTER for more complex conditions. For an advanced technique, use UNIQUE with the TEXTJOIN function to create a comma-separated list of unique values in a single cell.