How to Use UNIQUE Function in Excel

Key Insights

The UNIQUE function automatically extracts distinct values from ranges, eliminating the need for manual filtering or complex formulas that plagued pre-2021 Excel versions.
UNIQUE works on both single columns and multi-column ranges, with the latter returning unique row combinations based on all specified columns together.
Combining UNIQUE with SORT, FILTER, and COUNTA creates powerful dynamic data analysis tools that update automatically when source data changes.

Introduction to the UNIQUE Function

Excel’s UNIQUE function arrived with Excel 365 and Excel 2021, finally giving users a native way to extract distinct values without resorting to advanced filters or convoluted helper column formulas. Before UNIQUE, removing duplicates required either the Remove Duplicates feature (which permanently alters your data) or array formulas that made your spreadsheet look like a cryptography exercise.

The syntax is straightforward:

=UNIQUE(array, [by_col], [exactly_once])

Parameters breakdown:

array: The range of cells you want to extract unique values from (required)
by_col: TRUE to compare columns, FALSE or omitted to compare rows (optional)
exactly_once: TRUE to return only values that appear once, FALSE or omitted to return all unique values (optional)

UNIQUE is a dynamic array function, meaning it automatically spills results into adjacent cells. You enter the formula once, and Excel populates as many cells as needed. This behavior is transformative for building dashboards and reports that update automatically.

Basic UNIQUE Function Usage

Start with the simplest application: extracting unique values from a single column. Suppose you have a customer list in column A with duplicate names scattered throughout.

=UNIQUE(A2:A20)

This formula returns each unique customer name exactly once. If you have 19 rows of data with 8 distinct customers, UNIQUE returns 8 values. The spill range adjusts automatically when you add or remove data from the source range.

For product IDs in column B:

=UNIQUE(B2:B50)

This extracts every unique product ID from your dataset. The beauty of UNIQUE is its simplicity—no sorting required, no helper columns, no VBA macros. The function handles everything internally and updates instantly when source data changes.

One critical point: UNIQUE preserves the order of first appearance. If “Johnson” appears in row 5 before “Anderson” in row 12, Johnson appears first in your unique list. This matters when you’re building reports where sequence carries meaning.

Working with Multiple Columns

UNIQUE becomes significantly more powerful when applied to multi-column ranges. Instead of finding unique values in a single column, it identifies unique row combinations across all specified columns.

=UNIQUE(A2:C20)

If columns A, B, and C contain Name, Department, and Location respectively, this formula returns unique combinations of all three. A person named “Sarah Chen” in “Marketing” at “New York” is considered different from “Sarah Chen” in “Sales” at “New York”—the combination makes them distinct.

This differs fundamentally from running UNIQUE on a single column:

=UNIQUE(A2:A20)  // Returns unique names only
=UNIQUE(A2:C20)  // Returns unique name-department-location combinations

The first formula might return 15 unique names. The second might return 45 unique combinations because the same person appears in different departments or locations.

Use multi-column UNIQUE when you need to identify distinct records based on multiple criteria. Common applications include finding unique customer-product pairs, distinct date-region combinations in sales data, or unique employee-project assignments.

Using the by_col and exactly_once Arguments

The second parameter, by_col, changes how UNIQUE evaluates your data. By default (FALSE or omitted), UNIQUE compares rows. Setting it to TRUE makes UNIQUE compare columns instead.

=UNIQUE(A1:E1, TRUE)

This extracts unique values from a horizontal range—useful when your data is organized in rows rather than columns. For a header row containing category names where some categories repeat, this returns each category once.

The third parameter, exactly_once, filters for values appearing only once in your dataset:

=UNIQUE(A2:A20, , TRUE)

Notice the double comma—it skips the by_col parameter while specifying exactly_once. This formula returns only values that appear exactly once. If “Johnson” appears three times, it’s excluded. If “Anderson” appears once, it’s included.

This parameter is invaluable for data quality checks. Use it to find one-off transactions, identify customers who made single purchases, or spot data entry errors where unique identifiers accidentally repeated.

Combining both optional parameters:

=UNIQUE(A1:E1, TRUE, TRUE)

This compares columns and returns only those appearing once in the horizontal range.

Combining UNIQUE with Other Functions

UNIQUE’s real power emerges when combined with other Excel functions. These combinations solve complex problems with elegant, readable formulas.

Sorted unique list:

=SORT(UNIQUE(A2:A20))

This returns unique values in alphabetical order. SORT wraps UNIQUE, taking its output and ordering it. For numeric data, this creates ascending sorted lists automatically.

Count unique values:

=COUNTA(UNIQUE(A2:A20))

COUNTA counts non-empty cells in the unique list, giving you the count of distinct values. Before UNIQUE, this required array formulas like =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)). The new approach is infinitely more readable.

Filtered unique values:

=SORT(UNIQUE(FILTER(A2:B100, B2:B100>100)))

This filters your data first (keeping only rows where column B exceeds 100), then extracts unique combinations, then sorts the result. Three powerful functions working together in a single, comprehensible formula.

Dynamic dropdown lists:

=UNIQUE(FILTER(B2:B100, A2:A100=E1))

If E1 contains a category selection, this returns unique values from column B where column A matches that category. Use this as the source for data validation dropdowns that update based on other selections.

Common Use Cases and Best Practices

Scenario: Sales analysis with unique customer counts by region

Suppose you have sales data with columns for Region (A), Customer (B), Product (C), and Amount (D). You want unique customer counts per region.

First, create a unique region list:

=SORT(UNIQUE(A2:A1000))

Then, for each region, count unique customers:

=COUNTA(UNIQUE(FILTER(B2:B1000, A2:A1000=F2)))

Where F2 contains the region name. This counts distinct customers in that region.

Best practices:

Always reference the same row count across columns when using multi-column ranges. =UNIQUE(A2:C20) works; =UNIQUE(A2:C25) with data only to row 20 includes blank rows that may create unexpected results.
Place UNIQUE formulas in dedicated output areas to avoid spill conflicts. Dynamic arrays need empty cells below and to the right. Putting formulas too close together causes #SPILL! errors.
Use structured references with Tables for automatic range expansion. If your data is in a Table named “SalesData”, use =UNIQUE(SalesData[Customer]) instead of cell references. When you add rows to the table, UNIQUE automatically includes them.
Combine with IFERROR for cleaner outputs when ranges might be empty:

=IFERROR(UNIQUE(A2:A20), "No data available")

Troubleshooting and Limitations

#SPILL! errors occur when UNIQUE can’t populate adjacent cells because they contain data. Clear the cells in the spill range or move your formula to an area with sufficient empty space.

Blank cells are treated as unique values. If your source range contains multiple blank cells, UNIQUE returns one blank in its output. To exclude blanks:

=UNIQUE(FILTER(A2:A20, A2:A20<>""))

This filters out empty cells before extracting unique values.

Case sensitivity: UNIQUE is case-insensitive. “Johnson”, “JOHNSON”, and “johnson” are treated as identical. If you need case-sensitive unique values, you’ll need helper columns with formulas that distinguish case.

Compatibility: UNIQUE only works in Excel 365 and Excel 2021. Workbooks using UNIQUE will show #NAME? errors in older versions. If you’re sharing files with users on older Excel versions, you’ll need alternative approaches or need to copy-paste values to maintain compatibility.

Performance with large datasets: UNIQUE handles thousands of rows efficiently, but with datasets exceeding 50,000 rows, you may notice calculation delays. For very large datasets, consider using Power Query instead, which is optimized for big data operations.

Handling errors gracefully:

=IFERROR(UNIQUE(A2:A20), "No unique values found")

This prevents error displays when your source range is empty or contains only errors, making your spreadsheets more user-friendly and professional.

The UNIQUE function represents Excel’s evolution toward modern data analysis capabilities. It eliminates workarounds, reduces formula complexity, and makes spreadsheets more maintainable. Master it, combine it strategically with other functions, and you’ll handle data deduplication tasks that previously required VBA or external tools.