What it does – Combines values from one or more ranges into one column.
Syntax=FLATTEN(range1, [range2, ...])range1: The first range to flatten.range2, …: [optional] Additional ranges to flatten.
Sample Usage=FLATTEN(A2:D5)
//Flattens the values in the range A2:D5 in one column.
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:
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.
How to Use the Flatten function in Google Sheets?
Let’s see how you can use Flatten function in google sheets step by step:
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:
- 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:
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:
- 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:
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 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.