Get Unique Values From Multiple Columns in Excel

To get unique values from multiple columns in Excel, first turn the columns into one list, then remove the repeats. In Microsoft 365 and Excel 2024, one formula does both: =UNIQUE(TOCOL(A2:C5,1)). This guide explains why UNIQUE alone doesn't do it, shows how to sort, count and combine separate lists, and gives two methods for older versions of Excel.

The examples use a small shift rota in A1:C5. Some names appear in several weeks, and B4 is empty.

Five different people worked: Ana, Ben, Cara, Dev and Eli. The goal is a formula that lists those five, once each.

Why UNIQUE alone doesn't work on several columns#

UNIQUE compares whole rows when you give it more than one column. So =UNIQUE(A2:C5) returns all four rows unchanged, because no two rows are exactly the same. Ana still appears three times.

That is useful when you want unique combinations (more on that below), but not when you want one list of values. The fix is to flatten the columns into a single column first. The UNIQUE function guide covers the other arguments, and the article on the UNIQUE function covers the single-column basics.

How to get unique values from multiple columns with TOCOL#

TOCOL turns any range into one column. UNIQUE then removes the repeats.

=UNIQUE(TOCOL(A2:C5,1))

Type it in an empty cell, such as E2, and the result spills down into the cells below (it fills them automatically):

• TOCOL(A2:C5,1) reads the range into one column. The 1 tells it to skip blank cells. Without it, the empty B4 becomes a 0 in your list.
• UNIQUE(...) keeps the first appearance of each name.

TOCOL reads row by row by default: Ana, Ben, Ana, then Ben, Cara, Dev, and so on. To read column by column instead, add TRUE as the third argument: =UNIQUE(TOCOL(A2:C5,1,TRUE)) returns Ana, Ben, Cara, Eli, Dev, the order in which each name first appears going down Week 1, then Week 2, then Week 3.

TOCOL needs Microsoft 365 or Excel 2024. For Excel 2021 and older, see the last method below.

How to sort the unique list#

Wrap the formula in SORT for an A to Z list:

=SORT(UNIQUE(TOCOL(A2:C5,1)))

For Z to A, add the sort order: =SORT(UNIQUE(TOCOL(A2:C5,1)),,-1). The two commas skip SORT's second argument, which picks the column to sort by. The SORT function guide explains every argument, and the article on a sorted unique list shows more ways to sort the results.

How to combine columns that aren't next to each other#

TOCOL needs one block of cells. If your lists are in columns that aren't side by side, or on different sheets, join them with VSTACK first. VSTACK stacks ranges on top of each other.

=SORT(UNIQUE(TOCOL(VSTACK(A2:A5,C2:C5),1)))

This combines Week 1 and Week 3 only and returns Ana, Ben, Cara, Dev, Eli. Across sheets, the pattern is the same:

=SORT(UNIQUE(TOCOL(VSTACK(North!A2:A50,South!A2:A50),1)))

Keep TOCOL with the 1 around VSTACK: it removes the blanks at the bottom of each range. VSTACK needs Microsoft 365 or Excel 2024. The guide to combining data from multiple sheets covers VSTACK and Power Query in more detail.

How to count unique values across multiple columns#

To count instead of list, wrap the formula in COUNTA:

=COUNTA(UNIQUE(TOCOL(A2:C5,1)))

The result is 5. Make sure TOCOL has the 1, or the blank cell is counted as an extra value. The article on counting unique values shows other counting methods, including ones for older Excel.

How to get unique rows (combinations) instead#

Sometimes "unique from multiple columns" means unique combinations, such as every Region and Product pair that appears in a sales list. That is what UNIQUE does on its own when you give it several columns:

=UNIQUE(A2:B5) returns three rows: North Laptop, South Laptop and North Monitor. Row 4 is dropped because it repeats row 2 exactly. UNIQUE and SORT need Excel 2021 or Microsoft 365. The modern Excel functions lesson practises spill formulas like this.

Another formula: TEXTJOIN and TEXTSPLIT#

You can also join every value into one long piece of text and split it back into a column. This is the method in the exercise on this page:

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",TRUE,A2:C5),,",")))

TEXTJOIN joins the names with commas and skips blanks (that is the TRUE). TEXTSPLIT splits at each comma, and the two commas before "," mean "split into rows, not columns". It returns the same five names. Like TOCOL, TEXTSPLIT needs Microsoft 365 or Excel 2024, so TOCOL is usually the simpler choice. The join-and-split trick has limits: it breaks if a value contains a comma, numbers come back as text, and TEXTJOIN can't return more than 32,767 characters.

How to do it in Excel 2021 and older versions#

Without TOCOL, VSTACK or TEXTSPLIT, stack the values by hand and then remove the repeats:

1. Copy each column and paste it under the first one, so all values sit in one column.
2. Select that column and choose Data > Remove Duplicates, then click OK.
3. Sort it with Data > Sort A to Z if you like.

This works in every version, but the list does not update when the data changes. In Excel 2021 you can keep it live instead: keep the stacked column as a helper column and point =SORT(UNIQUE(...)) at it, so new names added to the helper column appear in the list. The comparison of UNIQUE vs Remove Duplicates explains when each one fits.

Common problems#

• A 0 in the list. TOCOL is missing the 1, so a blank cell became 0.
• #SPILL! The cells below the formula aren't empty. Clear them so the list has room.
• Near-duplicates. "Ana" and "Ana " (with a trailing space) are different to UNIQUE. Clean them with TRIM: =UNIQUE(TRIM(TOCOL(A2:C5,1))).
• Capitals. UNIQUE isn't case-sensitive, so "ana" and "Ana" count as one, and the first spelling wins.

Practise it#

Try the exercise on this page: it uses the join-and-split method on the shift rota, and you can check that five names come back. Then practise the UNIQUE function in a live spreadsheet, where you type each formula and see the spill result checked instantly.