How to Use FLATTEN in Google Sheets: Comprehensive Guide

FLATTEN takes one or more ranges and returns all their values as a single column. It reads row by row, left to right, and ignores the original shape. It is the tool for turning a grid into a list, or for stacking several ranges into one.

Syntax

=FLATTEN(range1, [range2, ...])

range1: The range or array to flatten.
range2, ... [Optional]: More ranges to append to the result.

Values are read row by row: all of row 1 left to right, then all of row 2, and so on.

Basic example

With this grid in A1:C2:

   A    B    C
1  a    b    c
2  d    e    f

=FLATTEN(A1:C2)

Returns a single column: a, b, c, d, e, f.

To read column by column instead, transpose first:

=FLATTEN(TRANSPOSE(A1:C2))

Returns a, d, b, e, c, f.

Flatten several ranges

Pass multiple ranges to stack them in one list. They do not need to be the same shape:

=FLATTEN(A2:A10, C2:D10, F2:F10)

This is handy for combining several columns of tags or emails into one list.

Common uses

Unique values from a whole grid

=UNIQUE(FLATTEN(A2:E100))

Returns the distinct values across any number of columns: for example every unique tag from a table with five tag columns. Add FILTER to drop blanks:

=SORT(UNIQUE(FILTER(FLATTEN(A2:E100), FLATTEN(A2:E100) <> "")))

Count items across a grid

=COUNTA(FLATTEN(A2:E100))

Split text, then flatten

SPLIT spills across columns. Use FLATTEN to turn the pieces from many cells into one tidy list:

=FLATTEN(ARRAYFORMULA(SPLIT(A2:A10, ", ")))

Given a column of comma-separated tags, this returns every tag in one column.

Turn a grid into a list for charts or QUERY

A wide table (one column per month) is awkward to chart or query. FLATTEN turns the values into one column, which is the first step toward a record-per-row layout. See Mastering Array Formulas for related techniques.

Join everything into one cell

=TEXTJOIN(", ", TRUE, FLATTEN(A1:C5))

TEXTJOIN already accepts ranges, but FLATTEN lets you combine non-adjacent ranges or the result of other functions.

Behavior to know

Blanks are kept. Empty cells come through as empty cells in the output. Filter them with FILTER(..., ... <> "").
Data types are preserved. Numbers stay numbers, dates stay dates.
Order is row-major. If you expected column-major, use TRANSPOSE first.

Common errors

#REF!: Array result was not expanded. Cells below the formula are not empty. Clear them.
#N/A or unexpected blanks in the output. The source contains blank cells or errors, and FLATTEN passes them through. Clean the source or filter the result.
Too much data. Flattening whole columns such as A:E creates thousands of blank rows. Use bounded ranges or filter blanks.

FLATTEN vs. TRANSPOSE vs. curly braces

Curly braces {A1:A5; B1:B5} stack ranges but require them to have the same number of columns. FLATTEN accepts ranges of any shape. TRANSPOSE changes orientation but keeps the table shape. FLATTEN always returns one column.

Frequently asked questions

Does FLATTEN remove duplicates or blanks? No, use UNIQUE and FILTER for that.

Can I flatten a result that spills? Yes, nest it: =FLATTEN(SPLIT(A1, ",")).

What if I want a row instead of a column? Wrap it: =TRANSPOSE(FLATTEN(A1:C5)).

Related Functions

TRANSPOSE: Swap rows and columns.
UNIQUE: Remove duplicates from the list.
FILTER: Remove blanks or select specific values.
SPLIT: Split text into pieces.
TEXTJOIN: Join values into one text string.

FLATTEN