# How to get unique values from multiple columns in Excel

Stack the columns into one with TOCOL, then remove the repeats: `=SORT(UNIQUE(TOCOL(B2:D6,1)))`.

**Excel:** `=SORT(UNIQUE(TOCOL(B2:D6,1)))` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Week | Mon | Wed | Fri |  | Everyone on the rota |
| Week 1 | Ana | Raj | Lee |  |  |
| Week 2 | Kim | Ana |  |  |  |
| Week 3 | Raj | Mo | Ana |  |  |
| Week 4 | Lee |  | Sam |  |  |
| Week 5 | Mo | Kim | Raj |  |  |

Formula in F2: `=SORT(UNIQUE(TOCOL(B2:D6,1)))`

Result (spills 6 × 1): Ana; Kim; Lee; Mo; Raj; Sam

## 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.

## Other ways

- `=SORT(UNIQUE(VSTACK(B2:B6,C2:C6,D2:D6)))`: 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' 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/unique-values-from-multiple-columns