Excel beginner UNIQUE distinct deduplicate dynamic arrays

Excel UNIQUE: Extract Distinct Values Automatically

UNIQUE Function (Excel) is a Excel function that unique scans b2:b100 and returns each distinct value once. Formula Genius generates and validates this formula automatically from a plain-English prompt.

UNIQUE returns a dynamic list of distinct values from a range. No more Remove Duplicates button or helper columns.

Generate This Formula Browse Library

The Formula

Prompt

"Get a unique list of departments from column B"

Excel 
=UNIQUE(B2:B100)

UNIQUE scans B2:B100 and returns each distinct value once. The result spills into adjacent cells and updates automatically when the source data changes.

Step-by-Step Breakdown

  1. UNIQUE(array, [by_col], [exactly_once]) extracts distinct values
  2. B2:B100 is the source range to deduplicate
  3. by_col (optional): TRUE = compare columns, FALSE/omitted = compare rows
  4. exactly_once (optional): TRUE = only values appearing once, FALSE/omitted = all unique values
  5. Results spill automatically

Edge Cases & Warnings

  • Empty cells in the range are treated as a value — you may get a blank in results
  • UNIQUE is case-insensitive: 'Sales' and 'sales' are treated as the same
  • Wrap in SORT for alphabetical order: =SORT(UNIQUE(B2:B100))
  • Requires Excel 365 or Excel 2021+

Examples

Prompt

"Sales, Marketing, Sales, HR, Marketing"

Excel 
Sales, Marketing, HR
Prompt

"=UNIQUE(B2:B100, , TRUE)"

Excel 
Only values appearing exactly once

Frequently Asked Questions

How do I count unique values?

Wrap in COUNTA: =COUNTA(UNIQUE(B2:B100)). Or for older Excel: =SUMPRODUCT(1/COUNTIF(B2:B100, B2:B100)).

Can UNIQUE work across multiple columns?

Yes. =UNIQUE(A2:C100) returns rows that are unique across all three columns combined.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples