When you import data from other software or receive a spreadsheet from a colleague, the data is often spread across a wide horizontal matrix. For example, you might have a list of employee names in Column A, Column B, and Column C.
If you need to feed this data into a pivot table or use it as a dropdown menu source, a multi-column grid is completely useless. You need all those names stacked neatly into a single, continuous vertical column.
Google Sheets introduced a beautifully simple array formula to solve this exact problem: FLATTEN. In this guide, you will learn how to use the FLATTEN function to instantly convert 2D grids into 1D lists.
The FLATTEN Syntax
The FLATTEN function is arguably the simplest array formula in Google Sheets. It takes only one argument (or multiple optional arguments if you are stacking disjointed ranges).
=FLATTEN(range1, [range2, ...])- range1: The block of cells (the 2D grid) you want to convert into a single column.
Use Case 1: Crushing a Basic Grid
Imagine you have a grid of data spanning from cell A1 to C5 (15 cells in total, arranged 3 wide by 5 tall). You want all 15 of these values stacked in a single vertical list starting in Column E.
Click into cell E1 and type:
=FLATTEN(A1:C5)Google Sheets will read the grid from left to right, top to bottom. It takes the value in A1, then B1, then C1, then A2, B2, C2, and spills them straight down Column E into a perfect 15-row list.
Use Case 2: Combining Disjointed Ranges
You are not limited to flattening a single contiguous block. You can feed multiple, completely separated ranges into a single FLATTEN formula to stack them all on top of each other.
Suppose you have a list of names in A1:A10 and another list of names over in F1:F10. You want them combined into one master list.
=FLATTEN(A1:A10, F1:F10)The formula will output the 10 names from Column A, and immediately beneath them, output the 10 names from Column F, creating a 20-row master list.
Use Case 3: Removing Blank Cells with FILTER
The only downside to FLATTEN is that it flattens everything, including blank empty cells within your grid. If your 3×5 grid only contains 12 actual words and 3 blank cells, FLATTEN will output a 15-row list with three awkward blank spaces in the middle of it.
To fix this, you must nest the FLATTEN function inside a FILTER function to automatically strip out the blanks.
=FILTER(FLATTEN(A1:C5), FLATTEN(A1:C5) <> "")Let’s break down this advanced logic:
- The first
FLATTEN(A1:C5)generates the raw, 15-row list containing the blanks. - The
FILTERfunction looks at that raw list. - The condition
FLATTEN(...) <> ""tells the FILTER to only output rows where the cell is not equal (<>) to a blank string ("").
The result is a perfectly compressed, continuous vertical list containing only your actual data points. By mastering FLATTEN, you can rapidly reshape datasets to prepare them for advanced analysis.