You can use the following formulas to create a unique list of values across multiple columns in Google Sheets:
Formula 1: Create Unique List of Values (Display Across Multiple Columns)
=UNIQUE(A2:C20)
Formula 2: Create Unique List of Values (Display in One Column)
=UNIQUE(FLATTEN(A2:C20))
The following examples show how to use each formula in practice.
Example 1: Create Unique List of Values (Display Across Multiple Columns)
Suppose we have the following dataset that contains information for various basketball players:
We can use the following formula to return only the unique rows across both columns:
=UNIQUE(A2:B11)
The following screenshot shows how to use this formula in practice:
Notice that the formula only returns the unique rows from the original dataset.
Example 2: Create Unique List of Values (Display in One Column)
Suppose we have the following dataset that contains the positions of players on various basketball teams:
We can use the following formula to find the unique values across all three columns and display the results in one column:
=UNIQUE(FLATTEN(A2:C6))
The following screenshot shows how to use this formula in practice:
Notice that the formula only returns the unique positions across each of the three columns.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
How to Use COUNTIF with Multiple Ranges in Google Sheets
How to Use SUMIF with Multiple Columns in Google Sheets
How to Sum Across Multiple Sheets in Google Sheets
