UNIQUE Function
The UNIQUE function returns unique values from a range or array, eliminating duplicates automatically. Perfect for creating distinct lists from data.
Excel
Google SheetsExcelGoogle Sheets=UNIQUE(array, [by_col], [exactly_once])Practical Examples
Basic Product List
Extract unique products from a sales database
Multi-Column Unique Combinations
Find unique customer-product combinations for cross-sell analysis
Values Appearing Only Once
Identify single-occurrence items for special handling
Dynamic Dropdown Source
Create self-updating validation list for data entry
Cross-Reference Analysis
Compare unique values between two datasets
Common Errors and Solutions
UNIQUE result cannot spill due to blocked cells
The cells where UNIQUE needs to output results contain data or merged cells
1. Clear the spill range by deleting content below the formula cell 2. Remove any merged cells in the output area 3. Move the formula to a location with sufficient empty space 4. Use INDEX to extract specific values if space is limited
Always place UNIQUE formulas in areas with ample empty space below and to the right
Example:
Invalid parameter types or incompatible data
Mixing incompatible data types or using text values for boolean parameters
1. Ensure by_col parameter is TRUE or FALSE (or 1/0) 2. Check that exactly_once is a boolean value 3. Verify the source range contains valid data 4. Remove any error values from the source range
Use explicit TRUE/FALSE for optional parameters and clean source data first
Example:
Function not recognized in current Excel version
UNIQUE is only available in Excel 365/2021+ and Google Sheets, not in older Excel versions
1. Upgrade to Excel 365 or Excel 2021 2. Use Google Sheets which supports UNIQUE 3. Alternative: Use Remove Duplicates feature (Data tab) 4. Alternative: Use Advanced Filter with 'Unique records only'
Check Excel version before using dynamic array functions
Example:
Invalid range reference or deleted source data
The source range has been deleted, moved, or the reference is invalid
1. Verify the source range exists and is accessible 2. Re-establish the range reference 3. Use structured references for table data 4. Check for broken links if referencing other workbooks
Use table references or named ranges for more stable references
Example:
Not all unique values are displayed
Excel's row limit reached, or exactly_once parameter filtering too aggressively
1. Check if exactly_once is set correctly (FALSE for all unique values) 2. Verify source range includes all data 3. Check for leading/trailing spaces causing false duplicates 4. Use TRIM to clean text data before applying UNIQUE
Clean and normalize data before applying UNIQUE function
Example:
Advanced Tips and Techniques
Modern Excel and Sheets allow powerful combinations. Try =SORT(UNIQUE(data)) for alphabetical unique lists, or =FILTER(UNIQUE(data), LEN(UNIQUE(data))>0) to remove blanks from results.
For datasets over 10,000 rows, reference specific ranges instead of entire columns. Using A2:A10000 instead of A:A can improve calculation speed by 5-10x.
UNIQUE is case-sensitive. 'Apple' and 'apple' are treated as different values. Use UPPER or LOWER to standardize case if needed.
UNIQUE requires Excel 365/2021+ or Google Sheets. For Excel 2019 and earlier, use Remove Duplicates feature or create helper columns with COUNTIF.
Create dynamic dropdown lists by using UNIQUE as the source for Data Validation. The list auto-updates as new unique values appear in your data.
UNIQUE works across sheets and even workbooks. Use it to consolidate unique values from multiple data sources into a master list.
Need Help with UNIQUE Function?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Master the FILTER function to dynamically extract data meeting specific criteria. Learn syntax, examples, and error solutions for Excel 365 and Sheets.
Excel