Table of Contents
To find unique values from multiple columns in Excel, you can use the COUNTIFS function or the Advanced Filter. COUNTIFS allows you to quickly count the number of unique values from multiple columns by using multiple criteria. The Advanced Filter allows you to extract the unique values from multiple columns and paste them into a new sheet or into a new range of cells. Both methods are quick and easy ways to find unique values from multiple columns in Excel.
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.