Excel Formula Extract Unique Values from Column

Updated 6 November 2026 · Standard formula, shown below

This article explains how to extract unique values from a column in Microsoft Excel using formulas. You’ll learn several formula approaches, including dynamic array functions and classic formulas for older versions, along with step-by-step instructions and common troubleshooting tips.

Why Extract Unique Values?

Extracting unique values is a common data-cleaning task. You might need a list of distinct customers, product categories, or error codes from a long column. Removing duplicates manually is error-prone and slow, especially with thousands of rows. A formula gives you a live, automatically updating list that reflects changes in your source data. This is useful for building dashboards, validating entries, or preparing data for analysis.

Using UNIQUE (Excel 365 and Excel 2021)

If you have Microsoft 365 or Excel 2021, the UNIQUE function is the simplest method. It returns a dynamic array of all distinct values from a range, in the order they appear.

Syntax: UNIQUE(array, [by_col], [exactly_once])

array – the range of cells you want to extract from (e.g., A2:A100).
by_col – optional; use TRUE to compare columns instead of rows. Default is FALSE (rows).
exactly_once – optional; use TRUE to return only values that appear exactly once. Default is FALSE (all unique values).

Example: To get all unique values from column A (rows 2 to 100), enter in cell C2:

=UNIQUE(A2:A100)

Press Enter. The results will spill down automatically into the cells below C2. If you want only values that appear once, use =UNIQUE(A2:A100,,TRUE).

Classic Formula for Older Excel Versions

If you don’t have dynamic arrays, you can use an array formula or a helper column. The classic approach combines INDEX, MATCH, and COUNTIF.

Step-by-step:

In cell C2, enter this array formula (confirm with Ctrl+Shift+Enter in older Excel):
=IFERROR(INDEX($A$2:$A$100, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$100), 0)), "")
Drag the formula down in column C until it returns blanks.

This formula counts how many times each value in A2:A100 already appears in column C above the current row. It then returns the first value with a count of zero, i.e., the next unique value. The IFERROR returns an empty string when no more unique values exist.

Using Advanced Filter for a Static List

If you prefer a one-time extraction without formulas, Excel’s Advanced Filter can copy unique values to another location.

Select your data range (including header if present).
Go to the Data tab → Sort & Filter → Advanced.
Choose Copy to another location.
Set List range to your data, and Copy to an empty cell.
Check Unique records only and click OK.

This creates a static list that does not update when the source data changes. It’s useful for a quick report, but not for ongoing analysis.

How to Choose the Right Method

Your choice depends on your Excel version and whether you need a live list.

UNIQUE function – best for Excel 365/2021 users who want a dynamic, auto-updating list.
INDEX/MATCH/COUNTIF array formula – works in any version, but requires dragging and is less efficient on large datasets.
Advanced Filter – good for a one-time extraction, no formulas needed.
Power Query – ideal for complex transformations and large data; you can load data, remove duplicates, and refresh.

Common Problems and Fixes

Here are typical issues you might encounter:

#SPILL! error – The UNIQUE result doesn’t have enough empty cells. Clear cells below the formula or move it to a column with space.
#NAME? error – Your Excel version doesn’t support UNIQUE. Use the classic array formula instead.
Trailing spaces – “Apple � and “Apple� are treated as different. Use TRIM on your source data first, e.g., =UNIQUE(TRIM(A2:A100)) (dynamic array) or add a helper column with =TRIM(A2).
Case sensitivity – UNIQUE is case-insensitive by default. If you need case-sensitive uniqueness, use the classic formula with EXACT inside an array formula.
Blank cells – UNIQUE includes a blank cell if present. To exclude blanks, use =UNIQUE(FILTER(A2:A100, A2:A100<>"")).

Limitations and Performance

Array formulas and UNIQUE can slow down with very large datasets (hundreds of thousands of rows). The classic COUNTIF approach recalculates every time, which can be heavy. For large data, consider using Power Query (Get & Transform) or a pivot table instead. Also, remember that the UNIQUE function returns values in the order they first appear; if you need alphabetical order, wrap it with SORT: =SORT(UNIQUE(A2:A100)).

Frequently Asked Questions

How do I extract unique values from multiple columns into one list?

Use UNIQUE with the range covering all columns, then it will return unique rows, not individual cells. To get unique values from multiple columns stacked vertically, you can use =UNIQUE(TOCOL(A2:B100)) in Excel 365, which converts the range to a single column first.

Can I extract unique values with a condition (e.g., only for a specific category)?

Yes, combine UNIQUE with FILTER. For example, to get unique products from column B where column A equals “Active”: =UNIQUE(FILTER(B2:B100, A2:A100="Active")). In older Excel, use an array formula with IF inside the classic formula.

Why does my UNIQUE formula return #VALUE! error?

This usually happens if the range includes entire columns (e.g., A:A) and there are conflicting data types or errors in the cells. Use a specific range like A2:A100. Also, ensure the formula is entered normally (not as an array formula) in a version that supports dynamic arrays.

How do I remove duplicates from a column permanently?

Use the built-in Remove Duplicates command on the Data tab. Select the column(s) and click Remove Duplicates. This modifies your data, so consider copying the column first if you need to keep the original.

Does UNIQUE work on filtered data?

No, UNIQUE ignores hidden rows. If you need unique values from visible rows only, you’ll need a helper column that marks visible rows (e.g., using SUBTOTAL) and then filter on that, or use a more advanced formula with AGGREGATE.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.