How to Use the FLATTEN Function in Google Sheets

Last Updated on November 1, 2023 by Jake Sheridan

In this tutorial, you will learn how to use the FLATTEN function in Google Sheets.

How to Use the FLATTEN Function in Google Sheets

The FLATTEN function is a powerful built-in Google Sheets function that can help you transform your spreadsheet data. With this function, you can take multiple columns of data and convert them into a single row.

This feature can be helpful if you are working with data from multiple sources and need to combine them into one list. For example, let’s say you have a list of customer data stored in two different tables. You can use the FLATTEN function to bring this data together into a single column

In this guide, we will explain how to use the FLATTEN function in Google Sheets and show some examples of when this function can be useful.

How to Combine All Values in a Range into a Single Column in Google Sheets

Here’s how to combine all values in a range into a single column in Google Sheets.

Step 1

First, we’ll use the FLATTEN function to combine three columns into a single column.

In the example above, we have three columns labeled ‘Group 1’, ‘Group 2’, and ‘Group 3’. We want to combine the members of each group into a single column.

Step 2

Select an empty cell and type the formula “=FLATTEN(“ to start the FLATTEN function.

Step 3

Type the range of cells you want to flatten into a single column.

Step 4

Evaluate the function by hitting the Enter key.

In our example, we were able to convert the range A2:C4 into a single column.

Step 5

We can also use the FLATTEN function to convert a range of rows into a single column.

Do note that flattening a range will result in an output sorted by row.

Step 6

If you want to return an output sorted by column, you can add each of the columns as its own argument.

Summary

This guide should be everything you need to start using the FLATTEN function in Google Sheets.

You may make a copy of this example spreadsheet to test it out on your own.