Lessons Lesson 85

UNIQUE

About this lesson

UNIQUE returns the distinct values in a range as an array. COUNTA around it counts how many different values there are.

By the end you can

  • list the distinct values in a column with UNIQUE, and watch them spill.
  • count how many different values there are with COUNTA around UNIQUE.

Practises UNIQUE COUNTA ROWS SORT

By Ali, Founder, Excel Lessons

The idea

The question "how many different customers" had no clean answer in Excel for thirty years. UNIQUE is that answer: the distinct values, in first-seen order, as an array. One formula, and the list fills the cells below it.

That filling is called spilling, and it is why UNIQUE replaced a page of workarounds. You do not say how many results there will be. The formula finds out, and the sheet makes room. Wrapped in COUNTA it is a single number instead, and that is the form most sheets use it in. The third argument, TRUE, changes the question from "each value once" to "only values that occur once".

The mistake to watch for

Typing under a formula that spills. The block belongs to the formula at its top. Excel will not write over anything, so one label in the way turns the whole answer into #SPILL!. Leave the column under a UNIQUE clear. And the third argument changes the question, from each value once to only the values that occur once. That is a different number that looks like a smaller version of the same one.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, type:

=UNIQUE(B2:B9)

Watch what happens below it.

E2
Row A B C D E F G
1 Ticket Customer Product Distinct customers Only once How many customers
2 101 Acme Tee
3 102 Birch Hoodie How many products
4 103 Acme Cap
5 104 Cedar Tee
6 105 Birch Tee
7 106 Dale Hoodie
8 107 Acme Tee
9 108 Elm Cap

Every step

  1. 1

    In E2, type: =UNIQUE(B2:B9). Watch what happens below it.

    Hint. One range.

  2. 2

    In G2, wrap UNIQUE in COUNTA for the customer count.

    Hint. COUNTA of the UNIQUE.

  3. 3

    ROWS counts the rows of anything. Read =ROWS(UNIQUE(B2:B9)) and say what it returns.

    Hint. Same list, counted by rows.

  4. 4

    UNIQUE's third argument, TRUE, keeps only values that appear once. In F2, list the customers with a single ticket.

    Hint. Skip the second argument, TRUE for the third.

  5. 5

    In G4, wrap UNIQUE in COUNTA for the product count.

    Hint. COUNTA of UNIQUE over column C.