UNIQUE

What it does

UNIQUE removes repeats from a list and returns each value once, in the order it first appears. The result spills into as many cells as it needs and updates when the list changes.

Syntax

=UNIQUE(array, [by_col], [exactly_once])

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

array

The list or table to take the values from.

by_col

FALSE or left out compares rows; TRUE compares columns.

exactly_once

TRUE returns only values that appear exactly once. Left out, every distinct value is returned.

Example

Orders came from five cities, some more than once. Which cities are there?

The formula in C2:

=UNIQUE(A2:A6)

The result spills into the cells below: Lyon, Porto, Graz

Lyon and Porto each appear twice, Graz once. Each is listed once, in the order they first appear: Lyon, Porto, Graz.

Common mistakes

• Leaving no room for the result. If cells below are filled, the formula shows #SPILL!; clear them.
• Expecting it to sort. Wrap it in SORT for an alphabetical list.
• Using exactly_once to remove duplicates. TRUE drops every value that repeats, not just the extra copies.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• UNIQUE: each value onceModule 11 · Dynamic arrays
• #REF! and #SPILL!Module 13 · Finding and fixing errors
• A sales reportModule 14 · Combined skills

More from this module

• FILTER
• SORT
• SORTBY
• SEQUENCE

All functions