UNIQUE function in Google Sheets - Deep Dive

Overview - UNIQUE function

What is it?

The UNIQUE function in Google Sheets extracts unique rows or values from a range or array. It removes duplicates and returns only one instance of each distinct item. This helps you quickly find all different entries without manually scanning or sorting. It works with numbers, text, or mixed data.

Why it matters

Without the UNIQUE function, finding distinct values would require manual filtering or complex formulas, which is slow and error-prone. UNIQUE saves time and reduces mistakes by automatically showing only unique data. This is essential for cleaning data, summarizing lists, or preparing reports where duplicates can confuse decisions.

Where it fits

Before learning UNIQUE, you should understand basic cell references and ranges in Google Sheets. After UNIQUE, you can explore related functions like SORT, FILTER, and ARRAYFORMULA to manipulate and analyze data more powerfully.

Mental Model

Core Idea

UNIQUE picks out only one copy of each different item from a list or table, ignoring repeats.

Think of it like...

Imagine you have a bag of mixed colored marbles and you want to lay out just one marble of each color on the table. UNIQUE is like picking one marble per color and leaving the rest in the bag.

Input Range:
┌─────┐
│ A   │
├─────┤
│ Red │
│ Blue│
│ Red │
│Green│
│Blue │
└─────┘

UNIQUE Output:
┌─────┐
│ A   │
├─────┤
│ Red │
│ Blue│
│Green│
└─────┘

Build-Up - 7 Steps

1

Foundation

Basic UNIQUE function usage

Concept: How to use UNIQUE to get distinct values from a simple list.

Type =UNIQUE(range) where 'range' is the cells with your data. For example, =UNIQUE(A1:A5) returns only the unique values from those cells, removing duplicates automatically.

Result

The formula outputs a vertical list of unique values from the input range.

Understanding that UNIQUE automatically filters duplicates saves manual effort and speeds up data cleaning.

2

Foundation

UNIQUE with text and numbers

Concept: UNIQUE works with any data type: text, numbers, or mixed.

If your range has numbers and text mixed, UNIQUE still returns each distinct entry once. For example, =UNIQUE({"apple", 3, "apple", 5, 3}) outputs apple, 3, 5.

Result

The output is a list with no repeated items, regardless of type.

Knowing UNIQUE handles mixed data types means you can use it broadly without worrying about data format.

3

Intermediate

UNIQUE with multiple columns

🤔Before reading on: do you think UNIQUE treats multiple columns as separate lists or combined rows? Commit to your answer.

Concept: UNIQUE can find unique rows when given multiple columns, not just single columns.

When you give UNIQUE a range with multiple columns, it looks at entire rows. It returns only rows that are unique across all columns. For example, =UNIQUE(A1:B5) returns rows where the combination of column A and B is unique.

Result

The output is a list of unique rows, ignoring duplicates of entire rows.

Understanding UNIQUE compares whole rows helps you avoid mistakes when working with tables instead of single lists.

4

Intermediate

Dynamic array behavior of UNIQUE

🤔Before reading on: do you think UNIQUE outputs values in one cell or spills into multiple cells? Commit to your answer.

Concept: UNIQUE outputs a dynamic array that spills into adjacent cells automatically.

When you enter UNIQUE in one cell, it fills the cells below (and to the right if multiple columns) with the unique results. You don't need to copy the formula down manually.

Result

The unique values appear in a block of cells starting from the formula cell, adjusting size automatically.

Knowing UNIQUE spills results dynamically lets you design flexible sheets without manual copying.

5

Intermediate

Using UNIQUE with FILTER for conditional uniqueness

🤔Before reading on: can UNIQUE filter unique values based on a condition alone? Commit to your answer.

Concept: UNIQUE alone does not filter by condition, but combined with FILTER it can show unique values meeting criteria.

Use =UNIQUE(FILTER(range, condition)) to get unique values that satisfy a condition. For example, =UNIQUE(FILTER(A1:A10, B1:B10="Yes")) returns unique values from A1:A10 where B1:B10 is Yes.

Result

The output is a unique list filtered by your condition.

Combining UNIQUE with FILTER unlocks powerful conditional data extraction.

6

Advanced

Handling blanks and errors in UNIQUE

🤔Before reading on: does UNIQUE include blank cells or error cells in its output? Commit to your answer.

Concept: UNIQUE includes blank cells as unique values but errors cause the formula to fail unless handled.

If your range has blank cells, UNIQUE treats them as a unique value and includes one blank in output. If there are errors like #N/A, UNIQUE returns an error unless you wrap it with IFERROR or FILTER to exclude errors.

Result

Output includes one blank if present; errors stop UNIQUE unless handled.

Knowing how UNIQUE treats blanks and errors helps prevent unexpected results or formula breaks.

7

Expert

UNIQUE with array literals and dynamic ranges

🤔Before reading on: do you think UNIQUE can work on arrays created inside the formula, not just cell ranges? Commit to your answer.

Concept: UNIQUE can process array literals and dynamic arrays created by other functions, not just static ranges.

You can write =UNIQUE({"A","B","A";"C","B","C"}) to get unique rows from an array inside the formula. Also, combining UNIQUE with functions like SORT or QUERY creates dynamic unique lists that update automatically.

Result

UNIQUE outputs unique values or rows from any array input, not limited to cell ranges.

Understanding UNIQUE's flexibility with arrays enables advanced dynamic spreadsheet designs.

Under the Hood

UNIQUE scans the input range or array from top-left to bottom-right, comparing each row or value to previously seen entries. It keeps track of which entries have appeared and outputs only the first occurrence of each distinct item or row. Internally, it uses a hash or lookup method to detect duplicates efficiently. The output is a dynamic array that spills into adjacent cells, resizing automatically as data changes.

Why designed this way?

UNIQUE was designed to simplify the common task of removing duplicates without manual sorting or filtering. Early spreadsheet users had to rely on complex formulas or manual steps. The dynamic array output aligns with modern spreadsheet design, allowing formulas to output variable-sized results without copying. This design balances ease of use with performance for large datasets.

Input Range/Array
┌───────────────┐
│ Value/Row 1   │
│ Value/Row 2   │
│ Value/Row 3   │
│ ...           │
└───────────────┘
       ↓
Duplicate Check (compare each to previous)
       ↓
Unique Entries Stored
       ↓
Dynamic Array Output
┌───────────────┐
│ Unique 1      │
│ Unique 2      │
│ Unique 3      │
│ ...           │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UNIQUE remove duplicates only in single columns or also in multiple columns? Commit to your answer.

Common Belief: UNIQUE only works on single columns and cannot handle multiple columns or rows.

Reality: UNIQUE can handle multiple columns and returns unique rows based on all columns combined.

Why it matters: Believing UNIQUE only works on single columns limits its use and causes people to write more complex formulas unnecessarily.

Quick: Does UNIQUE automatically sort the output? Commit to your answer.

Common Belief: UNIQUE sorts the output list alphabetically or numerically by default.

Reality: UNIQUE preserves the original order of first occurrences and does not sort the output.

Why it matters: Expecting sorted output can cause confusion and errors when order matters; you must use SORT separately if sorting is needed.

Quick: Will UNIQUE ignore blank cells automatically? Commit to your answer.

Common Belief: UNIQUE ignores blank cells and does not include them in the output.

Reality: UNIQUE treats blank cells as a unique value and includes one blank in the output if blanks exist in the input.

Why it matters: Not knowing this can lead to unexpected blank rows in results, affecting data analysis or presentation.

Quick: Can UNIQUE handle error values inside the input range without issues? Commit to your answer.

Common Belief: UNIQUE ignores error values and returns unique values from the rest of the data.

Reality: UNIQUE returns an error if any error values exist in the input range unless errors are handled separately.

Why it matters: Assuming UNIQUE ignores errors can cause formulas to break unexpectedly, disrupting workflows.

Expert Zone

1

UNIQUE preserves the order of first appearance, which is crucial when the sequence of data matters in reports.

2

When used with multi-column ranges, UNIQUE treats each row as a single combined entity, not separate columns, which affects how duplicates are detected.

3

UNIQUE's dynamic array output can overwrite adjacent cells if they contain data, so careful sheet layout is needed to avoid accidental data loss.

When NOT to use

UNIQUE is not suitable when you need sorted unique lists; use SORT(UNIQUE(range)) instead. Also, if you want to count unique values, use COUNTUNIQUE. For very large datasets with complex conditions, QUERY or FILTER combined with UNIQUE may be more efficient.

Production Patterns

In real-world sheets, UNIQUE is often combined with FILTER to extract unique filtered data dynamically. It is used in dashboards to list distinct categories or names. UNIQUE also pairs with SORT to create sorted unique lists for dropdown menus or validation. Advanced users embed UNIQUE inside ARRAYFORMULA or QUERY for dynamic reporting.

Connections

FILTER function

Builds-on

Knowing how FILTER extracts data by conditions helps you combine it with UNIQUE to get unique values that meet specific criteria.

Dynamic arrays

Same pattern

Understanding dynamic arrays explains how UNIQUE outputs spill results automatically, a key modern spreadsheet behavior.

Set theory (mathematics)

Conceptual parallel

UNIQUE implements the mathematical idea of a set by removing duplicates, helping bridge spreadsheet skills with fundamental math concepts.

Common Pitfalls

#1 Expecting UNIQUE to sort results automatically.

Wrong approach:

=UNIQUE(A1:A10)

Correct approach:

=SORT(UNIQUE(A1:A10))

Root cause:

Misunderstanding that UNIQUE preserves original order and does not sort output.

#2 Using UNIQUE on a range with error values without handling errors.

Wrong approach:

=UNIQUE(A1:A10) (where A5 contains #N/A error)

Correct approach:

=UNIQUE(FILTER(A1:A10, ISERROR(A1:A10)=FALSE))

Root cause:

Not knowing that errors cause UNIQUE to fail unless filtered out.

#3 Placing UNIQUE formula where adjacent cells have data, causing overwrite errors.

Wrong approach:

Put =UNIQUE(A1:A20) in B1 when B2:B10 already have data.

Correct approach:

Place =UNIQUE(A1:A20) in a clear area with empty cells below and to the right.

Root cause:

Ignoring that UNIQUE spills results dynamically and overwrites existing cells.

Key Takeaways

UNIQUE extracts distinct values or rows from a range, removing duplicates automatically.

It works with single or multiple columns and preserves the order of first appearance.

UNIQUE outputs a dynamic array that spills into adjacent cells, adjusting size as data changes.

Combining UNIQUE with FILTER or SORT expands its power for conditional and sorted unique lists.

Handling blanks and errors properly is essential to avoid unexpected results or formula errors.