How to Use the FLATTEN Function in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 5 min read · includes 2 free exercises

In short: FLATTEN puts all the values from one or more ranges into a single column. The formula is =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.

AB
1Q1Q2
212
334
456
=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

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.

Hint: one range argument.

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.

Hint: pass two ranges.

What to learn next

Syntax and rules in this guide were checked against Google's FLATTEN help page. See all guides.