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.
Matt King’s sheet, which was escalated to Google, can be found here.