About the FLATTEN Function
Overview of FLATTEN
| Convert Data from a Range into a Single ColumnGoogle Sheets Function | ||
|
=FLATTEN( range_or_value ) Summary The FLATTEN function converts a specified range or array into a single column of data arranged vertically, processed column by column. |
||
|
When to Use FLATTEN
- When you want to consolidate data vertically into a single column.
- When you need to process matrix data as a single list.
- When organizing or filtering data (in combination with other functions).
How to Use FLATTEN
The following table demonstrates the basic usage of the FLATTEN function.
| A | B | C | |
|---|---|---|---|
| 1 | Data1 | Data2 | Data3 |
| 2 | 1 | 4 | 7 |
| 3 | 2 | 5 | 8 |
| 4 | 3 | 6 | 9 |
Enter =FLATTEN(A2:C4) in cell D1 or another cell.
Result
The range A2:C4 will be flattened into a single column, processed column by column, producing the following:
- 1
- 4
- 7
- 2
- 5
- 8
- 3
- 6
- 9
Advanced Use Cases
The following are examples of using the FLATTEN function with additional processing:
Example 1: Removing Duplicates
Combine the FLATTEN function with UNIQUE to create a list of unique values from a flattened dataset.
| A | B | C | |
|---|---|---|---|
| 1 | Data1 | Data2 | Data3 |
| 2 | 1 | 1 | 3 |
| 3 | 2 | 4 | 3 |
| 4 | 3 | 4 | 5 |
=UNIQUE(FLATTEN(A2:C4))
Result
- The FLATTEN function produces: 1, 1, 3, 2, 4, 3, 3, 4, 5
- UNIQUE removes duplicates, resulting in: 1, 3, 2, 4, 5
Example 2: Removing Blank Cells
Use the FLATTEN function with FILTER to remove blank cells from the flattened data.
=FILTER(FLATTEN(A2:C4), FLATTEN(A2:C4) <> “”)
- Result: Data with blank cells removed is returned as a single column.
Points to Note
- The FLATTEN function processes all values in the range column by column and outputs them in a single column.
- Blank cells are included in the result unless additional filtering is applied.
- Errors may occur if the input includes out-of-range values or invalid data.
Conclusion
- The FLATTEN function is a highly convenient tool for consolidating data ranges into a vertical column.
- Data is processed column by column, so pay attention to the order.
- When combined with other functions, it can significantly enhance data organization and processing efficiency.