How to Use UNIQUE Function in Excel - Practical Guide

Learn to use the UNIQUE function to extract distinct values from a range or array, automatically removing duplicates. This dynamic function saves time on data cleaning and reporting by instantly identifying unique entries without manual filtering or helper columns.

Why This Matters

The UNIQUE function eliminates manual deduplication and creates dynamic lists that update automatically when source data changes, improving efficiency in data analysis and reporting.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with array formulas and dynamic arrays
•Excel 365 or Excel 2021 (UNIQUE not available in older versions)

Step-by-Step Instructions

Identify your data range

Select the range containing data with potential duplicates (e.g., A1:A10). Note the column or row orientation for your formula.

Click on an empty cell for output

Select a cell where you want the unique values to appear (e.g., C1), ensuring enough space below for results.

Enter the UNIQUE formula

Type =UNIQUE(A1:A10) and press Enter. The formula automatically spills results into adjacent cells vertically.

Add optional parameters for control

Modify your formula to =UNIQUE(A1:A10, FALSE, FALSE) where the second parameter removes duplicates (TRUE=keep, FALSE=remove) and third parameter specifies column/row orientation.

Verify results and adjust as needed

Review the spilled results to ensure all duplicates are removed and data matches expectations; modify the range if necessary.

Alternative Methods

Combine UNIQUE with FILTER

Use =UNIQUE(FILTER(A1:A10, criteria)) to extract unique values based on specific conditions rather than the entire range.

Use UNIQUE with multiple columns

Apply =UNIQUE(A1:B10) to remove duplicate rows across multiple columns simultaneously.

Manual approach with Advanced Filter

In Data > Sort & Filter > Advanced Filter, select 'No duplicates' option for a non-formula alternative in older Excel versions.

Tips & Tricks

✓Use UNIQUE with entire columns (A:A) for automatic expansion when new data is added
✓Combine UNIQUE with SORT for organized unique values: =SORT(UNIQUE(A1:A10))
✓UNIQUE respects case sensitivity—'Apple' and 'apple' are treated as different values
✓The spill range cannot contain existing data; clear the area first if needed

Pro Tips

★Nest UNIQUE with COUNTIF to identify which unique values appear most frequently: =UNIQUE(A1:A10) paired with =COUNTIF(A:A, C1:C10)
★Use UNIQUE in data validation dropdowns: Data > Validation > List > =UNIQUE(A1:A100) for dynamic, duplicate-free lists
★Combine UNIQUE with CONCATENATE for multi-column deduplication: =UNIQUE(A1:A10&B1:B10) to treat combined values as unique identifiers

Troubleshooting

UNIQUE formula doesn't update when source data changes

Ensure your source range includes all current and future data. Use entire column references (A:A) instead of fixed ranges (A1:A10) for dynamic updates.

Getting duplicate values in results

Check the second parameter is set to FALSE (=UNIQUE(range, FALSE)). Verify source data doesn't have trailing spaces; use TRIM() if needed: =UNIQUE(TRIM(A1:A10)).

UNIQUE returns error when used with external data sources

Ensure external data is fully loaded before the formula calculates. Wrap UNIQUE in IFERROR to handle connection delays: =IFERROR(UNIQUE(A1:A10), "Loading...").

Related Excel Formulas

FILTER SORT COUNTIF TRIM TAKE

Frequently Asked Questions

What's the difference between the second and third parameters in UNIQUE?

The second parameter (exact_match) controls duplicate detection: TRUE keeps all values, FALSE removes duplicates. The third parameter (by_col) specifies orientation: FALSE processes by rows (default), TRUE processes by columns.

Can UNIQUE work with text and numbers together?

Yes, UNIQUE handles mixed data types, but it treats them distinctly. For example, the number 1 and text '1' are recognized as different values.

Does UNIQUE work in Excel 2019 or earlier?

No, UNIQUE is exclusive to Excel 365 and Excel 2021 or later. For older versions, use Advanced Filter or create a pivot table as alternatives.

How do I limit the number of unique values returned?

Nest UNIQUE within TAKE function: =TAKE(UNIQUE(A1:A10), 5) returns only the first 5 unique values.