How to get unique values from multiple columns in Excel
Formula (Excel and Google Sheets)
=SORT(UNIQUE(TOCOL(B2:D6,1)))Stack the columns into one with TOCOL, then remove the repeats: =SORT(UNIQUE(TOCOL(B2:D6,1))).
- Result on the example
- a 6-row list that spills from F2 in
F2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE compares rows, so on a block of three columns it would look for repeated rows, not names. TOCOL first reads the block into a single column, and its second argument, 1, skips the empty cells. UNIQUE then removes the repeated names and SORT puts them in order.
=SORT(UNIQUE(TOCOL(B2:D6,1)))Result{6 rows × 1 col} "Ana", "Kim", "Lee", "Mo", …
B2:D65 rows × 3 cols: "Ana", "Raj", "Lee", "Kim", …A range: 5 rows × 3 columns.
TOCOL(B2:D6,1){13 rows × 1 col} "Ana", "Raj", "Lee", "Kim", …TOCOL: An array or range as one column, optionally skipping blanks and errors.- array
B2:D65 rows × 3 cols: "Ana", "Raj", "Lee", "Kim", … - ignore
1
- array
UNIQUE(TOCOL(B2:D6,1)){6 rows × 1 col} "Ana", "Raj", "Lee", "Kim", …UNIQUE: Distinct rows of a range. Spills.- array
TOCOL(B2:D6,1){13 rows × 1 col} "Ana", "Raj", "Lee", "Kim", …
- array
SORT(UNIQUE(TOCOL(B2:D6,1))){6 rows × 1 col} "Ana", "Kim", "Lee", "Mo", …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
UNIQUE(TOCOL(B2:D6,1)){6 rows × 1 col} "Ana", "Raj", "Lee", "Kim", …
- range
In Google Sheets
The same formula works in Google Sheets: =SORT(UNIQUE(TOCOL(B2:D6,1))). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 6 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORT(UNIQUE(VSTACK(B2:B6,C2:C6,D2:D6))) | spills 7 × 1Different result | VSTACK stacks the columns one under another, but keeps the blanks, which show up as a 0. Adds a 0 for the empty cells. |
| =SORT(UNIQUE(FLATTEN(B2:D6)))Google Sheets | spills 7 × 1Different result | Google Sheets' FLATTEN does the same as TOCOL, but keeps the blanks, which sort to the end as an empty cell. An empty cell at the end of the list. |
Common mistakes
UNIQUE(B2:D6)on its own returns unique rows of three names, not a list of names.- TOCOL needs Excel 2024 or Microsoft 365. Google Sheets has it too, and FLATTEN as an older alternative.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get unique values from multiple columns
F2, list everyone who appears anywhere in the rota B2:D6, once each, in A to Z order, without blanks.Questions
How do I count the unique names across the columns?
Count the list: =ROWS(UNIQUE(TOCOL(B2:D6,1))).
Does the order matter, row by row or column by column?
Only before sorting. TOCOL reads row by row; add TRUE as the third argument to read column by column.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.