UNIQUE Function (LibreOffice Calc)

UNIQUE extracts distinct entries from a Calc range. If A2:A7 contains Red, Blue, Red, Green, Green, Black, =COUNTA(UNIQUE(A2:A7)) returns 4 distinct colours. =UNIQUE(A2:A7;FALSE();TRUE()) instead asks for entries occurring exactly once. To display all returned values as a list, enter UNIQUE as an array formula; do not assume Excel-style automatic spill behaviour.

Inventory managers often want one row per supplier, category or postcode before producing a summary, without destroying the underlying detailed data. UNIQUE is helpful here because it creates a calculated list instead of changing the source range. A source with two repeated “Green” entries and two repeated “Red” entries has four distinct colours, but only “Blue” and “Black” appear exactly once. That distinction matters when identifying duplicate records versus listing unique category labels for a filter control.

=UNIQUE(Array [; By_col [; Uniqueness]])

Array — range or matrix containing candidates; entire rows can be compared for multi-column ranges

By_col — optional; FALSE() (default) compares rows, TRUE() compares columns

Uniqueness — optional; FALSE() (default) returns distinct entries; TRUE() returns only entries occurring once

LibreOffice Calc Help uses semicolons between parameters in its English-language syntax. A workbook configured for another locale may display a different function name or separator. Prefer cell references for real data and use the Function Wizard to check an unfamiliar argument.

Enter Red, Blue, Red, Green, Green and Black in A2:A7.

The following cells are used by this page’s separate native evaluation sheet (they are NOT screenshots). Blank cells not listed are intentionally empty.

Step-by-step

1. Enter Red, Blue, Red, Green, Green and Black in A2:A7.
2. Type =COUNTA(UNIQUE(A2:A7)) in F2 to check the distinct count is 4.
3. For a visible list, select a destination area of adequate size and enter =UNIQUE(A2:A7) as an array formula with Ctrl+Shift+Enter.
4. Use the third argument TRUE() when your real goal is the items with only one occurrence, and validate the sample first.

Formula, result and explanation

On a narrow screen, scroll this table sideways to see every column.

These exact formulas were evaluated by LibreOffice Calc 25.2.3.2, using an in-memory UNO-controlled Calc document and recalculation. The source sheet and cell-level results are provided in the evidence bundle; display as text may vary with number formatting and locale.

Combine UNIQUE with COUNTIF to audit categories: =COUNTIF(A2:A7;"Blue") returns 1 while =COUNTIF(A2:A7;"Red") returns 2. Those counts explain why Blue is selected by the Uniqueness=TRUE() mode and Red is not. For a multi-column source, Calc compares entire rows by default, not just one selected column: two rows count as duplicates only when the compared cells of both rows match. A scalar helper like INDEX(UNIQUE(A2:A7);2) can pick one member without displaying the full array.

• Array output needs adequate destination cells and array-formula entry (Ctrl+Shift+Enter); scalar wrappers were tested, not full array placement.
• Do not confuse distinct results (FALSE) with values seen exactly once (TRUE).
• Columns versus rows orientation changes the comparison unit in a matrix.
• Mixing text and numeric identifiers or whitespace can make seemingly identical keys different.

Troubleshooting sequence: First reproduce the small dataset above; verify the cells store numbers, dates or text as intended; check argument separators and missing quotes; then isolate each reference or intermediate step. When a function reports an error, investigate it before wrapping everything in IFERROR—silently replacing errors can hide defective source data.

This Calc function is available since LibreOffice 24.8. In Excel 365 and Google Sheets, UNIQUE commonly spills into adjacent cells automatically; Calc Help explicitly instructs array-formula entry, so document the destination-range workflow. Calc stores this as a Microsoft-extension function namespace rather than an OpenFormula 1.3 core function; round-trip behaviour with older ODF readers and third-party engines should be checked.

FILTER · SORT · COUNTIF · COUNTA · INDEX

• Primary vendor reference: LibreOffice Calc Help — UNIQUE. Current help-site rendering consulted 2026-10-11 (shown as LibreOffice 26.8 Help); note that the executed test version is older.
• Function availability: 24.8. Do not read this note as an assertion that every earlier Calc release supports every optional argument.
• Native testing: 3/3 listed scalar formulas passed on LibreOffice Calc 25.2.3.2 via UNO; results and workbook retained in evidence/.
• Not tested: full alternate-locale behavior, every error case, other office suites, round-trip XLSX/ODS conversion, screenshots, and (where relevant) interactive array placement / AutoFilter interaction. Any added advanced research formula not in the result table is illustrative only.

Selected example formulas were run in LibreOffice Calc 25.2.3.2 on Linux on October 12, 2026. The official Help may cover a newer version. Tests of selected formulas do not certify every locale, interactive array operation or file round trip. View the example-check record.

Official UNIQUE reference · Editorial standards