INFO
This page was originally generated from official documentation.
Flattens all the values from one or more ranges into a single column.
Sample Usage
=FLATTEN(A1:B2)
=FLATTEN("top", A1:B2, "middle", B3:B4, "bottom")Syntax
=FLATTEN(range1, [range2, …])| Part | Description |
|---|---|
| range1 | The first range to flatten. |
| range2 | [optional] repeatable Additional ranges to flatten. |
Notes
- Values are ordered by argument, then row, then column. So, the entire first row of an input is added before the second row (also known as row-major order).
- Empty values are not skipped; the FILTER function can be used to remove those.
Examples
Flatten will append arguments in the order they are included in the formula. Arguments need not be range references.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 1 | 2 | Formula in D1: =FLATTEN(A1:B2, “sample middle”, B3:B4) | 1 |
| 2 | 3 | 4 | 2 | |
| 3 | 5 | 3 | ||
| 4 | 6 | 4 | ||
| 5 | sample middle | |||
| 6 | 5 | |||
| 7 | 6 |
A more complex example, using the CONCAT (&) operator and SPLIT to do a simple cross join or Cartesian product on two lists.
| A | B | C | D | E | |||
|---|---|---|---|---|---|---|---|
| 1 | A | 1 | **Formula in D1: =ArrayFormula(SPLIT(FLATTEN(A1:A3 & " | " & TRANSPOSE(B1:B2)), " | "))** | A | 1 |
| 2 | B | 2 | A | 2 | |||
| 3 | C | B | 1 | ||||
| 4 | B | 2 | |||||
| 5 | C | 1 | |||||
| 6 | C | 2 |
History
The FLATTEN function was originally undocumented and was first discovered on March 15, 2020 by a user named Andy on the Google Docs Editors Help Community forum. It has since been officially recognized, although it has been mostly deprecated by TOCOL.