FLATTEN puts the values from one or more ranges into a single column. It processes each argument in order, reading across each row before moving to the next row.
FLATTEN function syntax
=FLATTEN(range1, [range2, ...])- range1: the first range, array or value to flatten.
- Additional arguments are appended in the order supplied.
- Blank positions are preserved. Leave enough empty cells for the entire output.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| List A | List B |
|---|---|
| Pen | Pad |
| File | Pen |
| Clip |
Flatten a rectangular range
Enter this formula in A8. The first row contributes Pen then Pad; the second contributes File then Pen; the third contributes Clip and a blank position.
=FLATTEN(A2:B4)Result: Pen; Pad; File; Pen; Clip; (empty text).
Stack complete columns instead
Passing the columns separately changes the order. All of A2:A4 comes first, followed by all of B2:B4. VSTACK is another option when you want to append ranges without flattening their widths.
=FLATTEN(A2:A4,B2:B4)Result: Pen; File; Clip; Pad; Pen; (empty text).
Remove empty entries from the result
FILTER keeps entries whose result is not empty text. It removes the preserved blank position, leaving five values in their original flattened order.
=FILTER(FLATTEN(A2:B4),FLATTEN(A2:B4)<>"")Result: Pen; Pad; File; Pen; Clip.
Return distinct items from the grid
UNIQUE removes repeated individual values after flattening. The blank-removal filter runs first, so the result contains only Pen, Pad, File and Clip.
=UNIQUE(FILTER(FLATTEN(A2:B4),FLATTEN(A2:B4)<>""))Result: Pen; Pad; File; Clip.
Turn one row into a vertical list
This returns Pen above Pad. TRANSPOSE gives the same orientation change for a single row; for a larger rectangle, TRANSPOSE swaps dimensions instead of making one column.
=FLATTEN(A2:B2)Result: Pen; Pad.
Include labels and mixed value types
Arguments can include literal text and numbers as well as ranges. This produces a label, the first source row, and the numeric value 25.
=FLATTEN("Supplies",A2:B2,25)Result: Supplies; Pen; Pad; 25.
Other Google Sheets articles you may also like