Complete Guide on FLATTEN function in Google Sheets for 2022

Complete Guide on FLATTEN function in Google Sheets for 2022
Reading Time: 4 minutes

What is the FLATTEN function in Google Sheets?

The Flatten function in Google Sheets flattens all the values from one or more ranges into a single column.

Why use the FLATTEN function in Google Sheets?

Let’s say you are working on a data range that looks something like this:

Data range of interest
Figure 1: The data range of interest

Here, there are 4 columns, Name, Age, Sex and Country of Origin. You want to club all these columns into a single column. You can perform this task using the FLATTEN function. 

Example of FLATTEN Function in Google Sheets
Figure 2: Example of FLATTEN Function in Google Sheets

Syntax

=FLATTEN(range1, [range2, …])

  • Range1: The first range to flatten. (for e.g. A1:B5)
  • Range2: [optional]The second range to flatten. 

Important Note: You can add any number of ranges to flatten. It is completely optional to add more than one range. 

How to Use the Flatten function in Google Sheets?

Flatten Rows to a Column

  • Entering the data as one big range or as separate columns affects how the FLATTEN function combines the data.
  • Let’s say you have a dataset that looks something like this:
The data range of interest
Figure 3: The data range of interest
  • You want to flatten every row of the data table and then create a single column.
  • Input the entire data range in range1, as shown below:

=FLATTEN(A2:D5)

  • In this case, every row is flattened, i.e. Values of every column of a particular row are transposed to a single column.
  • Elements A2, B2, C2 and D2 will be flattened to a single column.
  • Values of the next row will now be flattened to a single column and stacked below the elements of the previous row.
  • Elements of the cells A3, B3, C3 and D3 will be flattened and stacked below A2, B2, C2 and D2.
  • The output will look like this:
Example of FLATTEN Rows to Columns
Figure 4: Example of FLATTEN Rows to Columns

Flatten Columns to a Single Column

  • Entering the data as one big range or as separate columns affects how the FLATTEN function combines the data.
  • Let’s say you have a dataset that looks something like this:
The data range of interest
Figure 5: The data range of interest
  • You want to flatten every column of the data table and then create a single column.
  • Input every column of the data range as different ranges, as shown below:

=FLATTEN(A2:A5,B2:B5,C2:C5,D2:D5)

  • In this case, every column will be stacked upon each other.
  • Elements A2, A3, A4 and A5 will be flattened to a single column.
  • The values of the next column are stacked below the elements of the previous column.
  • Elements of B2, B3, B4 and B5 will be flattened and stacked below A2, A3, A4 and A5.
  • The output will look like this:
Example of Flatten columns to a single column
Figure 6: Example of Flatten columns to a single column

Conclusion

The FLATTEN function in Google Sheets is used to flatten data ranges to a single column. You can use the FLATTEN command along with the SPLIT and ARRAYFORMULA formula to perform advanced data analytics. If you want to know more about the FLATTEN function in Google Sheets, go through the official documentation here.

If you liked the article and want to know more about other topics in Google Sheets, click here.

See Also

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets

How to use Filter views in Google Sheets | Simple 2-min Guide 💻: Learn how to use Filter view in Google Sheets and its variations.