What UNIQUE Returns and Why It Helps
UNIQUE scans a range and keeps one copy of each value. The original data is untouched, and the list grows or shrinks when the source changes. This is the difference from Remove Duplicates: that tool deletes rows for good, while UNIQUE just shows you the distinct set. For a report that must stay current, UNIQUE is the safer bet.
The Syntax in Plain Terms
UNIQUE takes the array, an optional by-column flag, and an optional exactly-once flag. By default it looks down rows. The second argument set to TRUE makes it compare across columns instead. The third argument set to TRUE keeps only values that appear exactly once, which is a neat way to find singletons.
Type =UNIQUE( and highlight the column with repeats, like C2:C100 on the Sales sheet. Excel reads each cell top to bottom and remembers the values it has already seen.
Press Enter. The distinct values spill from your formula cell downward. A light blue border marks the live block. Do not type in those cells or you will get a #SPILL! error.
Use =UNIQUE(Sales[Region]) on a Table named Sales. Now any new region added to the table appears in the list automatically, with no edit to the formula.
The order is the order of first appearance in the source, not alphabetical. If you want A to Z, wrap with SORT: =SORT(UNIQUE(Sales[Region]), 1, 1).
Keep Only Values That Appear Once
The third argument flips UNIQUE from distinct to singletons. Set it to TRUE and you get only the values with a count of exactly one. I use this to catch typos or one-off entries that should not be in a master list.
Write =UNIQUE(C2:C100, FALSE, TRUE). Values that show up more than once are dropped, leaving only the lone entries. This is a fast typo check on a category column.
Stack ranges with =UNIQUE(VSTACK(A2:A50, B2:B50), , TRUE) to see values present in exactly one of the two lists. VSTACK puts them in one column first, then UNIQUE with exactly-once isolates the differences.
Wrap with =COUNTA(UNIQUE(Sales[Region])) to count how many distinct regions you have. This is the live version of a pivot table's distinct count, and it updates on its own.
I recommend wrapping UNIQUE in SORT whenever the list feeds a dropdown or a label, because the first-appearance order looks random to a reader. A common mistake is leaving it unsorted, then a teammate thinks regions are missing when they are just listed in a confusing order. The extra SORT costs nothing and reads far better.
Distinct Rows Across Several Columns
UNIQUE is not limited to one column. Hand it a multi-column range and it treats each row as a combined key, returning one row per unique combination. That is how you pull a clean list of region and product pairs from a long transaction table.
Write =UNIQUE(A2:D100) to keep one copy of each full row. Two rows count as the same only if every column matches. Use this before building a small reference table from a big export.
Select just the key columns, like =UNIQUE(CHOOSECOLS(Sales, 3, 2)), to get unique Region and Product pairs. CHOOSECOLS picks the two columns so UNIQUE compares only those.
Chain =SORT(UNIQUE(A2:D100), 1, 1) to order the distinct rows by the first column. The result is a tidy master list you can paste into a validation source or a lookup table.
Clean the Source Before You Dedupe
UNIQUE treats West and west and West as three different values, and a trailing space makes West and West look like two regions. Clean the source first so the distinct list reflects real categories instead of formatting accidents. I trim and standardize every category column before I trust a UNIQUE result.
Wrap the range in TRIM: =UNIQUE(TRIM(C2:C100)). TRIM removes leading, trailing, and doubled spaces, so West and West collapse to one value. The result is a clean distinct list.
Use =UNIQUE(UPPER(C2:C100)) to force every entry to capitals before deduping. This stops East and east from showing as two regions when only one should exist.
Write =UNIQUE(TRIM(UPPER(C2:C100))) to trim and uppercase in one pass. Apply this when the source comes from a messy export where casing and spacing are both unreliable.
Pair UNIQUE With COUNTIF for a Frequency List
A distinct list is more useful when you know how often each value appears. Drop UNIQUE into a helper column, then COUNTIF against the source, and you get a frequency table that stays live as rows are added.
In F2 type =UNIQUE(Sales[Region]) so the distinct regions spill down column F. This becomes the left side of your frequency table.
In G2 type =COUNTIF(Sales[Region], F2#) where F2# is the spilled list. Excel counts how many times each region appears. The # anchors the formula to the whole spill.
Wrap the pair in SORTBY: =SORTBY(UNIQUE(Sales[Region]), COUNTIF(Sales[Region], UNIQUE(Sales[Region])), -1) to list busiest regions first. One formula does the whole job.
Build a Live Dropdown Source
Data validation dropdowns normally point at a fixed range that goes stale. Point the validation at a UNIQUE spill instead, and the menu grows with your data. This is the cleanest way to keep a region picker honest.
Put =UNIQUE(Sales[Region]) in a hidden helper sheet, say Lists!A2. The spill there is your source. Keeping it off the main view avoids accidental edits.
Open Data Validation, choose List, and enter =Lists!A2#. The # makes the source the whole spill, so new regions appear in the dropdown without touching validation settings.
Add a row with a brand new region to Sales. Open the dropdown and confirm the new value is there. If it is missing, the validation range likely lacks the # anchor.
A common mistake is pointing validation at A2:A100 instead of A2#. The fixed range stops at row 100, so a region that first appears at row 101 never reaches the menu. This will break the picker silently. Always use the spill anchor when the source is a UNIQUE formula.
Dedupe Across Columns Instead of Rows
By default UNIQUE compares down rows, but the second argument set to TRUE flips it to compare across columns. This is useful when your distinct items sit in a wide grid rather than a single column, such as a matrix of category codes where you want each column checked on its own.
Write =UNIQUE(A2:D10, TRUE) to return distinct columns instead of distinct rows. Excel now treats each column as one item and keeps the first occurrence of every unique column pattern.
Add the third argument: =UNIQUE(A2:D10, TRUE, TRUE) keeps only columns that appear exactly once across the range. Use this when a wide import has duplicated header blocks you want to spot.
Why UNIQUE Throws #SPILL! and #NAME?
UNIQUE has few failure modes. The big two are a blocked spill area and an old Excel version that lacks the function. Both are quick to confirm and fix.
If you see #SPILL!, something sits in the cells below the formula. Delete the stray value, merged cell, or border, and the distinct list fills in.
UNIQUE needs Excel 365 or Excel 2021. On Excel 2019 or earlier you get #NAME?. Upgrade, or use Remove Duplicates plus a manual re-type for a static list.
Trim the source with =UNIQUE(TRIM(C2:C100)) if 'West' and 'West ' look like two values. Trailing spaces create fake duplicates that UNIQUE keeps separate.
UNIQUE vs the Remove Duplicates Button
Remove Duplicates deletes rows from the sheet and cannot reverse without undo or a backup. UNIQUE shows a live distinct list and never touches the source. For a one-off cleanup of a static export, the button is fine. For anything that stays connected to changing data, UNIQUE wins.
Cleaning the source first makes UNIQUE more reliable. Trim spaces, fix casing, and then dedupe, so the distinct list reflects real categories rather than formatting accidents.
Next Steps and a Quick Checklist
Start with one column and confirm the spill. Wrap it in SORT for a readable list. Chain with COUNTIF for frequencies or with FILTER to dedupe a subset. Point dropdowns at the spill with the # anchor so they stay live.
Natural next functions are SORTBY to order the distinct list by a hidden key, FILTER to dedupe after a condition, and SEQUENCE to generate practice IDs. Together they replace most manual list-building you do by hand.
