Flattens all the values from one or more ranges into a single column.
Parts of a FLATTEN function
=FLATTEN(range1, [range2, …])
|
Part |
Description |
|
range1 |
The first range to flatten. |
|
range2 |
[optional] repeatable Additional ranges to flatten. |
Sample formulas
=FLATTEN(A1:B2)
=FLATTEN("top", A1:B2, "middle", B3:B4, "bottom")
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 |