How to Use the FLATTEN Function in Google Sheets
=FLATTEN(range1, [range2, ...]). Values are read row by row within each range.
What FLATTEN does
Many functions want one list, but your data sits in a grid. FLATTEN turns a block of cells, or several blocks, into a single column. Typical uses are counting unique items across several columns, or feeding a grid into UNIQUE or TEXTJOIN.
FLATTEN syntax
=FLATTEN(range1, [range2, ...])
Per Google's help page, the values are ordered by argument, then row, then column: the entire first row of a range is added before its second row. Blank cells are kept, so use FILTER to remove them. The arguments do not all have to be ranges. Text strings can be included.
A step-by-step example
Here are two columns of numbers in cells A1:B4.
| A | B | |
|---|---|---|
| 1 | Q1 | Q2 |
| 2 | 1 | 2 |
| 3 | 3 | 4 |
| 4 | 5 | 6 |
=FLATTEN(A2:B4)
This returns 1, 2, 3, 4, 5, 6 in one column. Here is why: the values are read across each row first, so the first row gives 1 and 2, the second gives 3 and 4, and the third gives 5 and 6.
Several ranges: the order changes
=FLATTEN(A2:A4, B2:B4)
This returns 1, 3, 5, 2, 4, 6. The order follows the arguments, so the whole first range comes out before the second.
Common mistakes
- Blocked output cells. The result spills downward into empty cells.
- Expecting column-by-column order. A single range is read row by row. Pass each column as its own range to read down columns.
- Forgetting that blanks are kept. Filter them out if you do not want them.
Try it yourself
Use the table above (A1:B4). Type your formula into the box, starting with =, then press Check.
Exercise 1: Flatten the block
Write a formula that puts all the values in A2:B4 into one column.
Exercise 2: Column by column
Write a formula that returns column A first and then column B, as one column: A2:A4, then B2:B4.
What to learn next
- TRANSPOSE: swap rows and columns.
- SEQUENCE: generate a list of numbers.
- UNIQUE: remove duplicates from a list.
Syntax and rules in this guide were checked against Google's FLATTEN help page. See all guides.