The Google Sheets UNIQUE formula creates a list of distinct values without changing your source data. Enter =UNIQUE(A2:A8) in an empty cell, and Sheets returns each distinct entry in first-seen order; combine it with FILTER when you need to exclude blank rows.
That sounds simple, but choosing the wrong input range can change the answer. A list of customers is not the same thing as a list of unique customer-and-project combinations, and neither is the same as a list of entries that occurred only once. This guide shows exactly how to choose among them.
What does the Google Sheets UNIQUE formula do?
Google documents the syntax as UNIQUE(range, by_column, exactly_once). The first argument identifies the cells to inspect. The optional second argument changes the comparison direction from rows to columns. The optional third argument changes the result from distinct entries to entries occurring exactly once. Both optional settings default to FALSE.
For ordinary vertical office lists, use just the range. If the source contains repeated project codes, =UNIQUE(A2:A8) returns each code once while retaining the order of its first appearance. It does not delete or edit a single source record, which makes it useful for a reporting tab linked to a frequently updated intake sheet.
Start with a realistic project-code example
Illustrative example: An operations coordinator receives seven request rows and needs a clean list of projects for a weekly reviewer. The raw sheet has a header in A1, with these values in A2:A8: PRJ-101, PRJ-204, PRJ-101, PRJ-305, PRJ-204, PRJ-410, PRJ-305.
| Source row | Project code | First occurrence? |
|---|---|---|
| 2 | PRJ-101 | Yes |
| 3 | PRJ-204 | Yes |
| 4 | PRJ-101 | No |
| 5 | PRJ-305 | Yes |
| 6 | PRJ-204 | No |
| 7 | PRJ-410 | Yes |
| 8 | PRJ-305 | No |
On a separate Summary tab, place =UNIQUE('Requests'!A2:A8) in A2. The four output cells read PRJ-101, PRJ-204, PRJ-305, and PRJ-410, in that order. The reviewer can compare those four project codes with the seven source requests, knowing that duplicate request rows still exist for audit and follow-up.
Small tip: Put the formula on a separate summary tab, not immediately underneath the source list. A formula result can expand into adjacent cells; keeping the output away from manual entries avoids collisions and prevents a colleague from mistaking the generated list for an editable intake column.
Exclude blanks before building the unique list
If your intake range contains empty rows, filter those out before requesting unique values. Use =UNIQUE(FILTER(Requests!A2:A,Requests!A2:A<>"")) for a column that will grow over time. FILTER keeps only populated cells; UNIQUE then reduces repeated project codes. This is more useful than a raw open-ended UNIQUE result when the output feeds a dropdown source or a weekly summary.
Practical tip: Add a visible label above the formula such as “Live project list — updates from Requests.” If a manager needs a frozen Friday list, copy the resulting cells and paste values into a dated archive tab. Otherwise Monday’s newly entered projects can silently alter what looks like last week’s report.
Keep unique rows when two columns define the record
Suppose each request includes a project code in A and an owner in B. A project may legitimately appear under two owners. If the review question is “Which project-owner pairs exist?”, use =UNIQUE(A2:B8). Sheets compares whole rows across both columns, so PRJ-101 / Maya and PRJ-101 / Leon are distinct combinations even though the project code repeats.
Do not use a two-column range when you only want distinct project codes. That would retain one row for every different owner, and the resulting project column could appear to contain duplicates. Equally, do not reduce to project code alone if the reviewer needs to identify which owner is responsible for each combination. Decide what constitutes a duplicate before selecting the range.
For an illustrative handoff, imagine Maya owns the initial review of PRJ-101 and Leon owns a later approval. The project-only summary has one PRJ-101 entry, while the project-owner summary has two rows. Both results are correct for their respective questions. The coordinator sends the project-only list to the weekly portfolio reviewer and the pair-level list to the person assigning follow-ups.
Find entries that occurred exactly once
There is an important difference between “show each value once” and “show values that appeared once in the source.” To request the latter, use =UNIQUE(A2:A8,FALSE,TRUE). With the seven-row project example above, the output is only PRJ-410, because the other three codes each occur twice.
The FALSE argument keeps the default row comparison, and TRUE activates the exactly-once rule. This option is useful for spotting one-off categories or requests that never repeated, but it is unsuitable for building a complete list of all active projects. If the report is intended to cover every project, use the ordinary one-argument formula instead.
Easy-to-miss detail: A one-time occurrence is not automatically an exception. PRJ-410 may be a perfectly valid new project. Use the exactly-once output as a review queue, not as proof that a source row is wrong; the reviewer should compare the code against the request register before taking action.
Sort the output without changing source order
UNIQUE preserves first-seen order, which is useful when you want the summary to reflect intake sequence. For an alphabetical review list, wrap the result in SORT: =SORT(UNIQUE(FILTER(A2:A,A2:A<>""))). The original source order stays untouched while the summary becomes easier to scan or search.
For project codes with numeric suffixes, remember that text sorting follows text order rather than necessarily the numeric sequence a person expects. PRJ-10 may sort before PRJ-2. If numeric order matters, standardize code widths such as PRJ-002 and PRJ-010 at the point of entry, or maintain a separate numeric sort key. Do not silently rewrite existing identifiers merely to improve display order.
Why do apparent duplicates remain?
Google’s documentation specifically warns that visually similar entries can contain different hidden text, including trailing spaces. For example, the source values PRJ-101 and PRJ-101 may look identical in narrow cells but are not the same text. Inspect the source rather than assuming UNIQUE is malfunctioning.
For ordinary leading and trailing spaces, use a separate helper column with =TRIM(A2), fill it down, and run UNIQUE over the cleaned helper values. Check the cleaned list against the source before replacing anything. If identifiers are imported from external systems, some unusual spaces or characters may need a more targeted cleanup than TRIM alone.
Also check whether numbers are stored as text and whether formats are consistent. A project code such as 0012 should generally remain text if leading zeros are part of the identifier. Converting it into a number can change its meaning. The correct fix is to standardize the data type according to the field’s business definition, not simply force everything into numeric format.
What if the formula cannot expand?
UNIQUE returns an array of results into cells below or beside the formula. If those destination cells already contain manual data, the output cannot occupy the expected space. Move the formula to a clear region or clear only the intended output cells after checking whether they contain important work. Do not delete the original request data to make room for the summary.
Small tip: When a formula stops expanding after a colleague adds a note, inspect the first obstructed output cell before editing the formula. The calculation may be correct; the obstacle may simply be a value left in a destination cell from an earlier manual report.
Build a reviewer-friendly handoff
A useful workflow has three tabs: Requests for raw entries, Summary for formulas, and Archive for approved weekly snapshots. In Requests, keep a stable request ID, project code, owner, and received date. In Summary, use a distinct project list and, when needed, a separate project-owner list. In Archive, store the approved values with a snapshot date and reviewer name.
In the illustrative example, the coordinator initially emailed seven raw rows. The reviewer had to notice that three projects appeared twice and could easily have counted seven requests as seven projects. After the change, the coordinator sends four distinct project codes plus the seven-row source link. The reviewer checks PRJ-410, confirms that it is a new project rather than an error, and approves the four-project summary for the Friday report.
This separation matters because a formula output is a current view, not a permanent record of what somebody approved. If the underlying requests change, the formula updates. Recording the snapshot date and the reviewer decision makes later reconciliation possible without pretending that the live summary represents the historical state.
For related spreadsheet workflows, see the Google Forms timesheet guide, the no-code request tracker, and the Excel silent-formula-error checklist. Each addresses a different part of collecting, summarizing, or checking office data.
Final checklist before sharing
Confirm that the selected range represents the business definition of a duplicate; that blank rows are handled; that output cells are empty; and that the result is labelled as live or archived. Check a few source rows manually, especially where owner names, hidden spaces, or leading-zero codes could change the grouping. Then give the reviewer the source link and the precise question the summary answers.
The Google Sheets UNIQUE formula is most reliable when it supports a clear review decision: distinct projects, distinct project-owner pairs, or values occurring exactly once. Choose the right question first, then use the corresponding formula and preserve the original records for traceability.