Browse

UNIQUE in Zoho Sheet: Distinct lists versus once-only values

Statistical Intermediate Zoho Sheet

Build a deduplicated region or agent list with Zoho Sheet UNIQUE and understand occurs_once and spill behavior.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked

Quick answer: A repeated order ledger can have the same sales region on dozens of rows. UNIQUE extracts the distinct region names without requiring a manual Find/Remove Duplicates operation.

This is a Zoho Sheet-specific tutorial, written against Zoho’s official online function reference checked on October 10, 2026. The vendor help does not identify a minimum Zoho application release for this function; a historical introduction date has not been verified. No example in this guide was run in a signed-in Zoho Sheet account. Expected outputs are independently checked against the supplied sample data.

Syntax and parameters

=UNIQUE(range; [col]; [occurs_once])
Parameter Meaning
range Input cells or array containing values to compare.
col FALSE by default compares rows; TRUE compares columns, as documented by Zoho.
occurs_once FALSE (default) returns distinct entries; TRUE keeps only entries occurring once.

Reproduce the example with real data

Download the eight-row CSV dataset and import it into a blank Zoho Sheet starting at cell A1; this creates a header on row 1 and data in rows 2–9. Leave the output area outside A1:F9. If IDs or numbers were changed during import, correct their data types before continuing.

These are representative records from the downloadable dataset (the formula ranges reference all eight records):

Row A: Order ID B: Region C: Status D: Amount E: Agent F: Units
2 O-101 East Paid 120 Ana 12
3 O-102 West Pending 75 Bo 5
4 O-103 East Paid 210 Ana 7
5 O-104 North Paid 90 Cy 0
6 O-105 West Paid 150 Bo 3
7 O-106 East Pending 60 Dana 8
8 O-107 West Paid 125 Ana 12
9 O-108 North Pending 80 Cy 4

Enter the formula in a vacant cell, for example H2:

=UNIQUE(B2:B9)

Expected result: Distinct region set: East, West, North (three values; display order not guaranteed here).

Walkthrough: East appears three times, West three times and North twice. The normal UNIQUE result gives one entry for each distinct value, not only single-occurrence values.

A second example

=UNIQUE(E2:E9;FALSE;TRUE)

Expected result: Dana. Ana occurs three times, Bo twice, Cy twice and Dana once. Setting occurs_once to TRUE excludes every name that appears more than once.

More advanced use

=COUNTA(UNIQUE(E2:E9))

Expected result: 4. There are four distinct agents. COUNTA counts the entries in the resulting unique list; confirm spreadsheet spill support in the destination.

To create a region list for data validation, generate UNIQUE into a separate area, then inspect whether new imported region spellings appear unexpectedly. A distinct list is both a reporting tool and a simple data-quality indicator.

Common errors and troubleshooting

  • occurs_once=TRUE is not synonymous with removing duplicates: repeated entries disappear entirely.
  • If the source contains extra spaces, East and East can act as different values; clean imports before deduplicating.
  • Leave room for the spilled results or place them on a dedicated reporting tab.
  • Zoho’s export help warns that some predefined functions, including UNIQUE, may not be supported when exporting. Validate the exported workbook before relying on it outside Zoho.

Compatibility and version notes

UNIQUE is also used in current Excel and other dynamic-array environments. The broad idea is portable, but confirm the argument order, row/column comparison semantics, available versions and export limitations before copying the formula.

The examples deliberately follow Zoho’s semicolon-separated syntax. Spreadsheet software may use different argument separators based on product and locale. This guide covers the current documented Zoho function, not an unverified assertion about support in older releases.

Zoho also publishes an export limitations guide explicitly naming this function among features that may be unavailable in the exported file. Reopen and check exported results.

Official reference and verification

Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.

  • No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.