Google Sheets: Create Unique List Across Multiple Columns


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

Leave a Reply

Your email address will not be published.