Flattens all the values from one or more ranges into a single column.

Parts of a FLATTEN function

=FLATTEN(range1, [range2, …])

PartDescription
range1The 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

Make a copy

Flatten will append arguments in the order they are included in the formula. Arguments need not be range references.

ABCD
112Formula in D1: =FLATTEN(A1:B2, “sample middle”, B3:B4)
234
35
46
5
6
7

A more complex example, using the CONCAT (&) operator and SPLIT to do a simple cross join or Cartesian product on two lists.

ABCDE
1A1**Formula in D1: =ArrayFormula(SPLIT(FLATTEN(A1:A3 & "" & TRANSPOSE(B1:B2)), "
2B2A
3CB
4B
5C
6C