FormulaFix · Plain-English guides to Excel and Google Sheets formulas

How to Use the UNIQUE Function in Excel (and Google Sheets)

The UNIQUE function extracts distinct values from a range, array, or reference. It is available in Microsoft Excel 365/Excel 2021 (and later) and in Google Sheets. While the core idea is the same, the syntax and optional arguments differ slightly between the two platforms.

1. Syntax Overview

Platform Syntax Key points
Excel =UNIQUE(array, [by_col], [exactly_once]) * array – range or array to evaluate (required).
by_col – FALSE (default) scans rows; TRUE scans columns.
 exactly_once – FALSE (default) returns all distinct values; TRUE returns only values that appear once.
Google Sheets =UNIQUE(range, [by_column]) * range – source range (required).
by_column works like Excel’s by_col.
 No exactly_once argument; you must combine other functions to get “once‑only” values.

Both versions spill results into adjacent cells, so you do not need to copy the formula down manually.

2. Basic Usage

2.1 Simple list of distinct values (row‑wise)

A
Apple
Banana
Apple
Cherry
Banana 
=UNIQUE(A2:A6)   // works in Excel and Google Sheets

Result
Apple
Banana
Cherry

The function scans the column (default by_col = FALSE) and returns each fruit once, preserving the order of first appearance.

2.2 Return only values that appear once (Excel)

=UNIQUE(A2:A6, FALSE, TRUE)

Result
Cherry

exactly_once = TRUE filters out any item that occurs more than once.

2.3 Column‑wise uniqueness (horizontal data)

A B C
Red Blue Red
Green Blue Yellow 
=UNIQUE(A1:C2, TRUE)   // Excel only; Google Sheets uses the same flag

Result
Red
Blue
Green
Yellow

Setting by_col = TRUE deduplicates across columns instead of rows.

3. UNIQUE on Single vs. Multiple Columns

3.1 One column

A (Names)
Alice
Bob
Alice
Carol
Bob 
=UNIQUE(A2:A6)

Result
Alice
Bob
Carol

3.2 Multiple columns (row‑wise)

A (ID) B (Product)
101 Apple
102 Banana
101 Apple
103 Cherry
102 Banana 
=UNIQUE(A2:B6)

Result

ID Product
101 Apple
102 Banana
103 Cherry

Each entire row is treated as a single key; duplicate rows are removed.

3.3 Column‑wise deduplication (Google Sheets only)

=UNIQUE(A2:B6, TRUE)

Result
101
102
103
Apple
Banana
Cherry

Excel ignores the by_col flag for this purpose; it always works row‑wise.

4. Nesting UNIQUE with Other Functions

Combining UNIQUE with other formulas lets you sort, count, or filter data without extra helper columns.

Goal Formula (Excel 365/2021) Formula (Google Sheets) Sample data Result
Alphabetically sorted list =SORT(UNIQUE(A2:A10)) =SORT(UNIQUE(A2:A10)) Apple, Banana, Apple, Cherry, Banana Apple, Banana, Cherry
Count occurrences of each distinct value =LET(u,UNIQUE(A2:A10),c,COUNTIF(A2:A10,u),CHOOSE({1,2},u,c)) =ARRAYFORMULA({UNIQUE(A2:A10), COUNTIF(A2:A10, UNIQUE(A2:A10))}) Same as above Apple 2, Banana 2, Cherry 1
First N unique rows that meet a condition =INDEX(FILTER(UNIQUE(B2:D10), C2:C10="Yes"), SEQUENCE(3)) =INDEX(FILTER(UNIQUE(B2:D10), C2:C10="Yes"), SEQUENCE(3)) See table below First three unique rows where Flag = “Yes”

Quick tip: Wrap UNIQUE with SORT whenever you need a predictable order; otherwise the result follows the source order.

5. Real‑World Scenarios

Scenario Sample data Formula Result
Remove duplicate customer IDs 101 | Alice | NY
102 | Bob | CA
101 | Alice | NY
103 | Carol | TX |=UNIQUE(A2:A5)`		 101, 102, 103
List distinct product categories Apple | Fruit
Banana | Fruit
Carrot | Vegetable
Broccoli | Vegetable		 =UNIQUE(B2:B5) Fruit, Vegetable
Unique full records from a sales log 1/1 | Jane | 200
1/2 | John | 150
1/1 | Jane | 200
1/3 | Jane | 300		 =UNIQUE(A2:C5) 1/1 

Adapting for each platform
Excel and Google Sheets use the same formula for these basic cases. When you need the exactly_once filter, only Excel provides a built‑in argument; in Sheets you must combine UNIQUE with FILTER and COUNTIF.

6. Common Pitfalls & How to Fix Them

Pitfall Why it occurs Fix / tip
#SPILL! error Adjacent cells contain data, blocking the dynamic array. Clear the spill area or use @ to force a single‑cell result (e.g., =INDEX(UNIQUE(A2:A10),1)).
Blank cells appear as a single “empty” entry UNIQUE treats all blanks as identical. Pre‑filter out blanks: =UNIQUE(FILTER(A2:A10, A2:A10<>"")).
Case‑sensitivity confusion By default the function is case‑insensitive. Convert the source to a consistent case first, e.g., =UNIQUE(LOWER(A2:A10)).
Hidden rows still counted UNIQUE works on the underlying range, ignoring filters. Apply FILTER to the source before UNIQUE, e.g., =UNIQUE(FILTER(A2:A10, SUBTOTAL(103, OFFSET(A2, ROW(A2:A10)-ROW(A2), 0)))).
Function not available Excel versions prior to Office 365/2021 lack UNIQUE. Use a legacy array formula with IFERROR, INDEX, MATCH, and COUNTIF, or upgrade to a version that supports dynamic arrays.

7. Advanced Variations & Alternatives

Goal Excel formula Google Sheets formula Explanation
Unique values while ignoring blanks =UNIQUE(FILTER(A2:A100, A2:A100<>"")) Same FILTER removes empty cells before UNIQUE runs.
First N unique items =INDEX(UNIQUE(A2:A100), SEQUENCE(N)) Same SEQUENCE(N) creates 1…N; INDEX extracts the first N distinct entries.
Unique rows based on several columns =UNIQUE(A2:C100) Same The whole row is treated as a composite key.

When UNIQUE isn’t available

Alternative Platform How to use
Remove Duplicates (Data → Remove Duplicates) All desktop Excel versions Deletes duplicate rows in place via the UI.
Array formula with MATCH/COUNTIF Excel 2016 and earlier =IFERROR(INDEX($A$2:$A$100, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$100), 0)), "") entered with Ctrl+Shift+Enter.
QUERY with SELECT DISTINCT Google Sheets =QUERY(A2:B, "SELECT DISTINCT A, B", 0) – returns unique rows without the UNIQUE function.

8. FAQ

What version of Excel is required for the UNIQUE function?
UNIQUE is available in Excel for Microsoft 365, Excel 2021, and later. Earlier desktop versions (e.g., Excel 2019, 2016) do not include it.

Can the UNIQUE function be used in Google Sheets?
Yes. Google Sheets supports UNIQUE(range, [by_column]). The syntax is similar, but there is no exactly_once argument.

How do I remove duplicates from multiple columns using UNIQUE?
Select the full range that includes all columns, e.g., =UNIQUE(A2:C100). Excel and Sheets treat each entire row as a single record and return only distinct rows.

Is there a way to sort the results of UNIQUE automatically?
Wrap the function with SORT: =SORT(UNIQUE(A2:A10)). This works in both Excel and Google Sheets and returns the distinct list in alphabetical (or numeric) order.

Can I use UNIQUE in a dynamic array formula?
Absolutely. UNIQUE itself is a dynamic‑array function, so you can nest it inside other dynamic functions such as SORT, FILTER, INDEX, or LET without additional special handling. The result will spill automatically.

End of guide.

Related guides