How to Find Unique Values from Multiple Columns in Excel

To find unique values from multiple columns in Excel, you can use a combination of functions like UNIQUE, TRANSPOSE, and FLATTEN (available in Excel 365) or use Power Query (available in Excel 2016 onwards). Here are the two methods to achieve this:

Method 1: Using UNIQUE, TRANSPOSE, and FLATTEN functions (Excel 365)

1. Select a blank cell where you want to display the unique values.
2. Assuming you have data in columns A, B, and C, enter the following formula:

=UNIQUE(FLATTEN(A1:C10))

Replace "A1:C10" with the actual range of your data.

3. Press Enter to get the unique values from the multiple columns.

Example:

Assuming you have the following data in columns A, B, and C:

A   B   C
1   2   3
2   3   4
3   4   5

Enter the formula =UNIQUE(FLATTEN(A1:C3)) in an empty cell, and press Enter. You will get the following unique values:

1
2
3
4
5

Method 2: Using Power Query (Excel 2016 onwards)

1. Select the data range you want to extract unique values from.
2. Go to the "Data" tab on the ribbon.
3. Click "From Table/Range" in the "Get & Transform Data" section. This will open the Power Query Editor.
4. In the Power Query Editor, click "Append Queries" in the Home tab, then click "Append Queries as New." This will open the Append Queries window.
5. In the Append Queries window, select the three tables (original table and two copies) and click "OK." This will create a new query with all the data from the three columns stacked vertically.
6. Click "Remove Duplicates" in the "Home" tab to remove any duplicate values.
7. Click "Close & Load" in the "Home" tab to load the unique values into a new worksheet.