Excel: How to Find Unique Values from Multiple Columns

You can use the following formula to find unique values from multiple columns in Excel:


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:


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:

Excel find unique values from multiple columns

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

Leave a Reply

Your email address will not be published.