You can use the following formula to find unique values from multiple columns in Excel:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$11"")*(COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
This particular formula finds the unique values in the cell range A2:C11 and returns a list of the unique values in column E.
The following example shows how to use this formula in practice.
Example: Find Unique Values from Multiple Columns in Excel
Suppose we have the following three lists of basketball team names in Excel:
To find the unique values from each of the three columns, we can use the following formula:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$11"")*(COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
We can type this formula into cell E2 and press Ctrl+Shift+Enter:
We can then click and drag this formula down to other cells in column E until blank values start appearing:
From the output we can see that there are 11 unique team names across the three columns of team names.
Note: To use this formula with more columns, simply modify the A2:C11 cell range in the formula to include more columns.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel