How to Extract Unique Values in Excel — 4 Methods

Unique Values vs Remove Duplicates: The Key Difference

Remove Duplicates (Data > Remove Duplicates) modifies the original data in place — it permanently deletes rows that have the same value. The original detailed data is destroyed.

Extracting unique values produces a separate list of distinct values, leaving all original rows exactly as they are. The original column may still have many repeated values; you just get a clean summary list somewhere else.

Use extraction when:

• You need a dropdown list source built from existing data
• You want to count how many distinct regions, categories, or customers exist
• You are building a VLOOKUP or SUMIF reference table from raw data
• You want to audit all the distinct values in a column to find typos, inconsistencies, or unexpected entries

Use Remove Duplicates when the goal is permanent deduplication of the source data itself.

Method 1: UNIQUE Formula (Excel 365 and 2021)

The UNIQUE function extracts a deduplicated list that spills automatically into adjacent cells:

=UNIQUE(A2:A100)

To sort the result alphabetically:

=SORT(UNIQUE(A2:A100))

To count how many distinct values there are:

=COUNTA(UNIQUE(A2:A100))

The UNIQUE formula updates automatically when the source data changes, making it ideal for dynamic reference lists and dropdown sources. Reference the spill range as a dropdown source with the # operator: =$F$2# — see the guide on creating dropdown lists in Excel for details.

UNIQUE also works across multiple columns: =UNIQUE(A2:B100) returns distinct combinations of column A and column B values together.

Method 2: Advanced Filter

Works in all Excel versions. Advanced Filter extracts unique values to a separate location without formulas.

1. Click any cell in the column containing the values.
2. Go to Data > Advanced.
3. In the dialog, choose Copy to another location.
4. Set the List range to the column (including header): $A$1:$A$100.
5. Leave the Criteria range blank (no filter conditions — you want all unique values).
6. Set the Copy to field to the top-left cell of the destination: $F$1.
7. Check Unique records only.
8. Click OK.

Excel copies one instance of each distinct value to the destination. The result is a static copy — it does not update when the source changes. Re-run Advanced Filter whenever the source data is updated.

Method 3: PivotTable

PivotTables automatically group and deduplicate values in their Rows area:

1. Click any cell in the data.
2. Go to Insert > PivotTable.
3. Place the field you want to deduplicate in the Rows area.
4. Optionally add it to Values (set to Count) to see how many times each unique value appears.

The PivotTable Rows area shows each distinct value once, sorted alphabetically by default. You can copy this column and paste as values to create a static unique list. PivotTables also refresh when you click Refresh — making them semi-dynamic reference points for large datasets.

PivotTables have a built-in advantage for auditing: the Count column immediately shows how many rows each unique value has, making it easy to spot dominant values, unexpected singletons, or entries that should be merged.

Method 4: Free Extract Unique Values Tool

For a fast one-off extraction without formulas or configuration:

1. Upload your workbook to the Extract Unique Values tool.
2. Choose the sheet and column.
3. Preview the unique list.
4. Download the result as a clean list or as a new workbook column.

The tool runs in your browser — no server upload. It handles large columns and provides the count of unique values alongside the list.

Common Problems When Extracting Unique Values

Problem 1: "North" and "North " (trailing space) Treated as Different Values

The most common cause of unexpected duplicates in a unique list. "North" and "North " (with a trailing space) are different strings even though they look identical on screen. Excel treats them as two distinct values.

Fix: clean spaces before extracting.

=SORT(UNIQUE(TRIM(A2:A100)))

TRIM removes all leading and trailing spaces (and reduces multiple internal spaces to one). For non-breaking spaces (CHAR 160) that TRIM does not catch, see the guide on removing spaces in Excel.

Problem 2: Same Value in Different Cases Shown as Duplicates

UNIQUE is case-insensitive: "North", "NORTH", and "north" all return as the same value (the first occurrence is kept). This is usually the desired behavior. If you genuinely need case-sensitive unique extraction, use a helper column with EXACT comparisons, which requires a more complex array formula approach.

Problem 3: Numbers Stored as Text Appear as Separate Unique Values

The number 1 and the text "1" are different values in Excel — they appear to both be in the unique list because they are not equal. Use =ISNUMBER(A2) on a sample of the column to detect mixed types, then standardize all values to the same type before extracting. The Column Statistics tool can detect mixed types across all columns at once.

Problem 4: Blanks Appearing in the Unique List

UNIQUE includes blank cells as one distinct value (empty). To exclude blanks from the unique list:

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

FILTER removes blanks first, then UNIQUE deduplicates the remaining values. Sort the result: =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))).

How Many Unique Values Are There?

To count distinct values without extracting the full list:

=COUNTA(UNIQUE(A2:A100))              — Excel 365 (excludes blanks automatically)
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))  — all versions

The SUMPRODUCT formula is the classic approach for Excel versions before 365. It counts the number of first occurrences by giving each value a weight of 1/count-of-that-value — summing these weights gives the total number of distinct values.

Cleaning the Unique List After Extracting

Once you have your unique list, review it for:

• Capitalization inconsistencies: "North" and "north" — standardize to consistent case using PROPER, UPPER, or LOWER.
• Abbreviations vs full names: "NY" vs "New York" — create a mapping table to consolidate.
• Values with extra words: "North America" vs "North Americas" — correct typos.
• Unexpected entries: values that should not exist in this column at all — trace them back to their source rows and investigate.

After cleaning, use the standardized unique list as the canonical reference for the column — for example, as a dropdown validation source that prevents inconsistent entries going forward.

Frequently Asked Questions

Can I extract unique values from multiple columns combined?

Yes. In Excel 365, use UNIQUE on a helper column that concatenates the columns: =UNIQUE(A2:A100&"|"&B2:B100). This extracts distinct combinations of A and B values. The separator | prevents false matches where the end of one column value and the start of the next could look like a different combination.

Does UNIQUE work on rows instead of columns?

Yes. The third argument of UNIQUE controls whether it returns unique rows (default) or unique columns: =UNIQUE(A1:E5,,TRUE) returns unique columns. The second argument (TRUE) extracts rows that appear only once rather than all distinct rows — omit it or set to FALSE for the standard "deduplicated list" behavior.

How do I use unique values as a dropdown source?

Place the UNIQUE formula in a helper column (e.g., column H). Then create a Data Validation dropdown with the source set to the spill range: =$H$2#. The # symbol tells Excel to include the entire dynamic spill. When new values appear in the source column, the UNIQUE formula updates automatically and the dropdown gains the new option. See the full dropdown list guide for step-by-step setup.