FLATTEN function in Google Sheets

FLATTEN turns a block of cells, or several ranges, into a single column.

Many functions want their data in one column: UNIQUE, SORT, COUNTIF and QUERY among them. Real data is often spread over several columns, or across a grid. FLATTEN stacks it all into a single column so that the other functions can get to work.

Syntax

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

Argument	What it is
`range1, range2, ...`	The ranges or values to stack into one column.

Examples

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=FLATTEN(C2:E3)
Result:
78
85
91
45
52
60
A block of two rows and three columns becomes one column. It is read row by row: all of the first row, then the second.
=FLATTEN(A2:A4,A7:A9)
Result:
Asha
Ravi
Meena
Joy
Neha
Imran
Two separate ranges stacked into one list.
=FLATTEN(C1:E1)
Result:
Maths
Science
English
A row turned into a column.
=COUNTA(FLATTEN(C2:E9))
Result: 23
Counting the filled cells of a block: 24 cells, one of them empty.
=SORT(FLATTEN(C2:E3),1,FALSE)
Result:
91
85
78
60
52
45
Once in a single column, the values can be sorted, filtered or made unique.
=UNIQUE(FLATTEN(B2:B9,B2:B9))
Result:
8A
8B
Stacking a range on itself and removing repeats, to show that duplicates are kept by FLATTEN and removed by UNIQUE.

How it works

FLATTEN reads each range row by row, left to right and then down, and writes the values one under another. Given several ranges, it finishes the first before starting the second.

Empty cells are not skipped. They become empty entries in the result. To drop them, filter afterwards:

=FILTER(FLATTEN(A2:D10), FLATTEN(A2:D10)<>"")

Stacking lists

The simplest use is joining lists that sit in different places into one. =FLATTEN(A2:A20, D2:D20, G2:G20) makes a single column from three.

The same can be done by writing ranges inside curly brackets with semicolons, ={A2:A20; D2:D20}. FLATTEN is easier to read, and it also copes with ranges of different widths.

Unique values across several columns

Suppose students list up to three hobbies, in three columns, and you want every different hobby mentioned. UNIQUE on the block would compare whole rows. Flatten it first:

=SORT(UNIQUE(FLATTEN(B2:D30)))

Add FILTER to remove the blank entry that comes from empty cells.

Counting across a grid

To find how often each value occurs anywhere in a block, flatten it and count. QUERY does both steps:

=QUERY(FLATTEN(B2:D30), "select Col1, count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc", 0)

Because the flattened data has no letters of its own, its single column is called Col1.

Unpivoting a table

A report with months across the top is good to read and awkward to analyse. Analysis wants one row per value: name, month, amount. FLATTEN builds that long form. With names in A2:A5, months in B1:D1 and figures in B2:D5:

names repeated: =FLATTEN(A2:A5 & T(B1:D1)), wrapped in ARRAYFORMULA, repeats each name once per month;
months repeated: =ARRAYFORMULA(FLATTEN(T(A2:A5) & B1:D1));
the values: =FLATTEN(B2:D5).

The three columns line up row for row. The T() function returns empty text for a non-text value and is used here only to stretch one range to the shape of the other. This trick takes a little practice, and is worth it for anyone who regularly receives wide tables.

Combining with SPLIT

When a column holds comma-separated lists, SPLIT inside ARRAYFORMULA spreads them across cells and FLATTEN stacks the result:

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

That gives every item of every list in one column, ready for UNIQUE or a count.

Common mistakes

Expecting FLATTEN to remove blanks or duplicates. It keeps everything.
Expecting column-by-column order. It goes along each row first.
Typing in the cells below the formula, which blocks the result and gives #REF!.
Flattening a very large block and then running a heavy function on it, which can make the sheet slow.

In Excel

Excel has no FLATTEN. Microsoft 365 has TOCOL, which does the same job and can skip blanks and read by column. Older versions of Excel need a longer formula with INDEX.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.