Excel 2021 or laterDynamic arrays

UNIQUE

UNIEK in Dutch Excel

Returns each value of a range once, in the order it first appears, as a list that spills and follows the source.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1Entry no.JournalJournals used
225-0412VRKVRK=UNIQUE(B2:B8)=UNIQUE(B2:B8)
325-0413INKINK
425-0414VRKBNK
525-0415BNKMEM
625-0416INK
725-0417MEM
825-0418VRK
=UNIQUE(B2:B8)
=UNIQUE(B2:B8)

What UNIQUE does and when to use it

What it does: it returns each value of a list once, in order of first appearance, for example the customers that occur in an invoice list. The list spills into the cells below and grows or shrinks with the data.

When to use it: to build the row labels of a summary, fill a drop-down list from data or count the different values in a column. It replaces the Remove Duplicates button for anything that must stay live. For totals per value on thousands of rows, a PivotTable is faster.

Two main gains:

  • Stays live: the list updates when the data changes, while Remove Duplicates gives a fixed result.
  • Source untouched: Remove Duplicates deletes rows from your data; UNIQUE only reads it.

How Excel evaluates it

Excel works as follows:

  • it reads array and, unless by_col is TRUE, treats each row as one item
  • it compares items without regard to letter case, so ab-204 and AB-204 are the same item; the spelling of the first one seen is the one returned
  • without exactly_once it keeps the first occurrence of each item and drops later repeats; with exactly_once TRUE it drops every item that occurs more than once

The result spills from the formula cell, and other formulas can refer to the whole of it with the # operator, such as E2#.

Watch out: Empty cells count as an item and UNIQUE returns them as 0, so a blank in the source adds a 0 to the list without any warning. Remove blanks first with FILTER.

Mental model

A guest list at the door: the first time a name arrives it is written down, every later arrival under the same name is waved through without a new line.

Terms used on this page: operator, spill, array, argument, cell

Syntax

Official UNIQUE documentation
UNIQUE(array, [by_col], [exactly_once])
UNIQUE(array; [by_col]; [exactly_once])
ArgumentTypeMeaningDefault
arrayRequiredThe values to deduplicate; with several columns, whole rows are compared.
by_colOptionalTRUE compares columns, for data laid out across; FALSE or omitted compares rows.FALSE
exactly_onceOptionalTRUE keeps only values that occur once; FALSE or omitted keeps one of each.FALSE

Return value:The distinct rows (or columns) of array, or those that occur exactly once

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

List the journals used in an auditfile extract

You exported journal lines from the client's XAF auditfile to a sheet, one row per entry. Before you plan your journal entry testing you want to see which journals were used, each one once, without sorting and deleting rows by hand. The extract is refreshed whenever the client sends a new export.

ABCDE
1Entry no.JournalJournals used
225-0412VRKVRK=UNIQUE(B2:B8)=UNIQUE(B2:B8)
325-0413INKINK
425-0414VRKBNK
525-0415BNKMEM
625-0416INK
725-0417MEM
825-0418VRK
9
Formula cell (D2)InputSpilled result

Formula in D2

=UNIQUE(B2:B8)
=UNIQUE(B2:B8)

Result: VRK / INK / BNK / MEMVRK / INK / BNK / MEM

Step by step
  1. Excel reads B2:B8, seven journal codes in one column, so each row is one item.

  2. Excel works down the column:

    • B2, VRK: not seen before, keep it
    • B3, INK: new, keep it
    • B4, VRK: already in the list, skip it

  3. It continues:

    • B5, BNK: new, keep it
    • B6, INK: seen, skip
    • B7, MEM: new, keep it
    • B8, VRK: seen, skip
  4. Four journals remain, in the order they first appeared: VRK, INK, BNK, MEM. They spill from D2 to D5. MEM is the one for manual entries, so it goes on your list for testing.
  5. If the next export holds a line in a journal called PAY, the list grows to five, with PAY in D6. VRK stays, because B2, B4 and B8 still hold it.

Watch out: The list is not sorted. If you want alphabetical order, wrap it: SORT(UNIQUE(B2:B8)).

More on this example: why it works, 1 variation

Why it works

UNIQUE keeps the first occurrence of each value and drops the later ones, so the output order mirrors the order in which the journals first turned up in the export. Because it is a formula, a new journal in a refreshed export appears in the list on the next recalculation.

Variations

=SORT(UNIQUE(B2:B8))
=SORT(UNIQUE(B2:B8))

The same journals in alphabetical order: BNK, INK, MEM, VRK.

Intermediate

Find invoices that exist in only one list

You match the invoice numbers on the client's receivables list to the invoices booked in the general ledger. You stacked both lists in one column. An invoice in both lists appears twice; an invoice that appears only once is missing on one side and needs follow-up with the client.

ABCDE
1Invoice no.SourceNot matched
225-1001Receivables list25-1003=UNIQUE(A2:A9, FALSE, TRUE)=UNIQUE(A2:A9; FALSE; TRUE)
325-1002Receivables list25-1005
425-1003Receivables list
525-1004Receivables list
625-1001General ledger
725-1002General ledger
825-1004General ledger
925-1005General ledger
10
Formula cell (D2)InputSpilled result

Formula in D2

=UNIQUE(A2:A9, FALSE, TRUE)
=UNIQUE(A2:A9; FALSE; TRUE)

Result: 25-1003 / 25-100525-1003 / 25-1005

Step by step
  1. by_col is FALSE, so Excel compares rows of A2:A9, one invoice number per row.

  2. It counts each number:

    • 25-1001: twice (A2, A6)
    • 25-1002: twice (A3, A7)
    • 25-1003: once (A4)
    • 25-1004: twice (A5, A8)
    • 25-1005: once (A9)
  3. exactly_once is TRUE, so every number with a count above 1 is dropped entirely. 25-1003 and 25-1005 remain, in the order they first appear.
  4. The two numbers spill from D2 to D3. Column B shows why: 25-1003 is on the receivables list but not booked in the ledger (possible unbooked sale or cut-off issue), and 25-1005 is booked in the ledger but not on the list.
  5. Compare UNIQUE(A2:A9) without the third argument: it returns five numbers, 25-1001 to 25-1005, one of each. That answers 'which invoices exist', not 'which invoices lack a counterpart'.

Watch out: An invoice that is booked twice in the ledger and missing from the receivables list also appears twice, so it is not flagged. To catch that, count per source or use COUNTIF and look for anything other than 1 per list.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

exactly_once turns UNIQUE from 'one of each' into 'only the singles', which is exactly the test for an invoice that was not matched. Every matched invoice appears twice and drops out, so only the exceptions are left for you to follow up.

More to watch out for

  • An invoice number with a trailing space from the export, "25-1002 ", does not match "25-1002", so both copies show up as singles. Clean the column with TRIM first.

Variations

=UNIQUE(A2:A9)
=UNIQUE(A2:A9)

One of each invoice number: everything that appears on either list, five numbers here.

Advanced

A sorted relation list without the blank

You extracted the postings on account 1300 Debiteuren from the XAF auditfile and want the customers, sorted, as the first column of a small reconciliation to the receivables list. One posting has no relation code, so the column has a blank. The totals per relation go in the column next to it.

ABCDEFG
1Entry no.RelationDebitRelationTotal
225-0501D1003€1,210.00€ 1.210,00D1001=SORT(UNIQUE(FILTER(B2:B9, B2:B9<>"")))=SORT(UNIQUE(FILTER(B2:B9; B2:B9<>"")))
325-0502D1001€450.00€ 450,00D1002
425-0503€300.00€ 300,00D1003
525-0504D1003€880.00€ 880,00D1004
625-0505D1002€150.00€ 150,00
725-0506D1001€90.00€ 90,00
825-0507D1004€640.00€ 640,00
925-0508D1002€260.00€ 260,00
10
Formula cell (E2)InputSpilled result

Formula in E2

=SORT(UNIQUE(FILTER(B2:B9, B2:B9<>"")))
=SORT(UNIQUE(FILTER(B2:B9; B2:B9<>"")))

Result: D1001 / D1002 / D1003 / D1004D1001 / D1002 / D1003 / D1004

Step by step
  1. The innermost function runs first. FILTER tests B2:B9<>"" and finds one empty cell, B4, so it returns the 7 relation codes without it: D1003, D1001, D1003, D1002, D1001, D1004, D1002.
  2. UNIQUE keeps the first occurrence of each code: D1003, D1001, D1002, D1004. Four relations.
  3. SORT puts them in ascending order on the only column: D1001, D1002, D1003, D1004. They spill from E2 to E5.

  4. In F2, =SUMIFS(C2:C9, B2:B9, E2#) uses the whole spilled list as criteria and spills four totals:

    • D1001: 450 + 90 = 540
    • D1002: 150 + 260 = 410
    • D1003: 1210 + 880 = 2090
    • D1004: 640
  5. The four totals add up to 540 + 410 + 2090 + 640 = 3680. The ledger total is 3980, so 300 is the posting without a relation code (25-0503): the reconciling item you must ask the client to explain.
  6. Without FILTER, UNIQUE(B2:B9) would return D1003, D1001, 0, D1002, D1004, because the blank B4 counts as a value and comes back as 0. SORT puts numbers before text, so the reconciliation would start with a row called 0.

Watch out: COUNTA(UNIQUE(B2:B9)) to count relations silently returns 5 instead of 4, because the blank came back as 0 and counts as a value. Count the filtered version instead.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Each function does one job on the output of the one inside it: FILTER removes what should not count, UNIQUE collapses repeats, SORT orders what is left.

Referring to the result as E2# lets the totals column grow and shrink with the relation list, so the two never fall out of step.

More to watch out for

  • If every cell in B2:B9 is empty, FILTER has nothing to return and the whole formula shows #CALC!. Add an if_empty to FILTER if that can happen.
  • Typing anything in E3:E5 or F3:F5 blocks the spill and gives #SPILL!; the reconciliation needs free cells below both formulas.

Variations

=SUMIFS(C2:C9, B2:B9, E2#)
=SUMIFS(C2:C9; B2:B9; E2#)

The totals column for F2: one SUMIFS per code in the spilled list, returning 540, 410, 2090 and 640.

=ROWS(UNIQUE(FILTER(B2:B9, B2:B9<>"")))
=ROWS(UNIQUE(FILTER(B2:B9; B2:B9<>"")))

Only the number of different relations, 4, when you do not need the codes.

Master

Duplicate supplier invoices, and the trap of exactly_once

In the purchase journal INK you test for duplicate payments: supplier invoice numbers booked more than once. The extract has one row per posting. You want the list of duplicated invoice numbers in one formula that names its steps, says so plainly when there are none, and keeps working when the extract doubles in size.

ABCDEF
1Entry no.Invoice no.AmountDuplicate invoices
225-0711INV-4471€1,210.00€ 1.210,00INV-4471=LET( invoices, B2:B9, distinct, UNIQUE(invoices), times, COUNTIF(invoices, distinct), FILTER(distinct, times > 1, "No duplicate invoices") )=LET( invoices; B2:B9; distinct; UNIQUE(invoices); times; COUNTIF(invoices; distinct); FILTER(distinct; times > 1; "No duplicate invoices") )
325-0712INV-5120€3,400.00€ 3.400,00INV-5120
425-0713INV-4471€1,210.00€ 1.210,00
525-0714INV-5188€815.50€ 815,50
625-0715INV-6002€249.90€ 249,90
725-0716INV-5120€3,400.00€ 3.400,00
825-0717INV-4471€1,210.00€ 1.210,00
925-0718INV-6120€1,100.00€ 1.100,00
10
Formula cell (E2)InputSpilled result

Formula in E2

=LET(
  invoices, B2:B9,
  distinct, UNIQUE(invoices),
  times, COUNTIF(invoices, distinct),
  FILTER(distinct, times > 1, "No duplicate invoices")
)
=LET(
  invoices; B2:B9;
  distinct; UNIQUE(invoices);
  times; COUNTIF(invoices; distinct);
  FILTER(distinct; times > 1; "No duplicate invoices")
)

Result: INV-4471 / INV-5120INV-4471 / INV-5120

Step by step
  1. LET names invoices as B2:B9, the eight invoice number cells.
  2. distinct is UNIQUE(invoices): INV-4471, INV-5120, INV-5188, INV-6002, INV-6120. Five different invoice numbers, in order of first posting.

  3. times is COUNTIF(invoices, distinct). Because the criteria argument is a 5-item list, COUNTIF runs five times and returns 5 counts:

    • INV-4471: 3 (25-0711, 25-0713, 25-0717)
    • INV-5120: 2
    • INV-5188: 1
    • INV-6002: 1
    • INV-6120: 1
  4. FILTER keeps the invoice numbers whose count is above 1: the first and second. INV-4471 and INV-5120 spill into E2:E3.
  5. The amount at risk follows from the data: INV-4471 was booked 3 times for 1210, so 2 x 1210 = 2420 is too much; INV-5120 twice for 3400, so 3400 is too much. Together 5820 to follow up.
  6. Compare UNIQUE(invoices, FALSE, TRUE): it returns INV-5188, INV-6002 and INV-6120, the invoices booked once, the exact complement of this list. Set B9 to INV-5188 and it moves from that list to this one: E2:E4 then reads INV-4471, INV-5120, INV-5188.

Watch out: The mirror-image mistake is reading 'unique' as 'occurs once'. UNIQUE(invoices, FALSE, TRUE) looks like a tidy invoice list, three numbers with no repeats, but it has silently left out exactly the invoices you are testing for.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

UNIQUE builds the list of keys once, COUNTIF accepts that whole list as its criteria and answers per key, and FILTER keeps the keys that pass, so the formula reads like the question: invoice numbers booked more than once.

Do not use this construction on a full ledger of hundreds of thousands of lines: COUNTIF scans the whole extract once for every distinct invoice number, which grows quickly. There a PivotTable with Invoice no. in the rows and a count of entries does the same grouping in one pass, at the cost of a manual refresh when the data changes.

More to watch out for

  • COUNTIF needs a real range as its first argument. If invoices were built with FILTER or another function, COUNTIF would refuse it; keep the reference to the sheet, as here.
  • When nothing is booked twice, FILTER returns No duplicate invoices, and ROWS(E2#) then returns 1, not 0. Count with SUM(--(times > 1)) inside the LET if you need the true number.

Variations

=UNIQUE(FILTER(B2:B9, COUNTIF(B2:B9, B2:B9) > 1))
=UNIQUE(FILTER(B2:B9; COUNTIF(B2:B9; B2:B9) > 1))

Without LET: keep every posting row whose invoice number occurs more than once, then collapse the repeats. Shorter, but it counts once per posting instead of once per invoice number.

=UNIQUE(B2:B9, FALSE, TRUE)
=UNIQUE(B2:B9; FALSE; TRUE)

The invoices booked once, the complement of the master result: useful as the clean population, wrong as a list of all invoices.

Common mistakes

SymptomCauseFix
A 0 in the list that is not in the dataA distinct list has a 0 in it that is not in the data.Filter the blanks out of the source first: FILTER(range, range<>"").
A row of values comes back unchangedUNIQUE on a row of values, such as B1:G1, returns the row unchanged, duplicates included.By default UNIQUE compares rows, and a single row is one row. Write UNIQUE(B1:G1, TRUE) so it compares the columns.
Customers missing from the resultUNIQUE(A2:A50, FALSE, TRUE) is used to get 'the unique customers', and some customers are missing.exactly_once TRUE drops every customer who appears more than once. For one of each, leave the third argument out.
Identical-looking values both appearTwo values look identical but both appear in the result.One has a trailing space or is a number stored as text. Clean the source with TRIM or VALUE; UNIQUE ignores case, but not spaces or data types.

Used in combinations

  • Duplicate invoice numbers across two exportsStack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.Excel 2024 or later
  • Gap test on invoice numbers with FILTER, SEQUENCE and XMATCHFind the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.Excel 2021 or later
  • Journal entries where debit does not equal creditList the entries whose debit and credit totals differ: take the unique entry numbers, total debit and credit per entry with SUMIFS, and keep the unequal ones with FILTER over HSTACK, all named in one LET.Excel 2024 or later
  • Mini trial balance from journal lines with HSTACK, UNIQUE and SUMIFSBuild a list of accounts with their net postings from raw journal lines: UNIQUE gives the accounts, SUMIFS totals each one, HSTACK puts both side by side and SORT orders them.Excel 2024 or later