FLATTEN in Excel vs Google Sheets: same name, different job
Google Sheets' FLATTEN turns a range into one column; Excel's new FLATTEN spreads lists inside cells into the grid. What each does, and the Excel formula to use instead.
Both Google Sheets and Excel now have a function called FLATTEN, and they don't do the same thing. If you
copy a Sheets formula into Excel, or follow a Sheets tutorial in Excel, the result won't be what you expect.
Google Sheets FLATTEN | Excel FLATTEN | |
|---|---|---|
| Works on | Ranges | Lists and arrays held inside cells |
| Returns | One column | The items spread across the grid |
| Available | All of Google Sheets | Excel's Beta Channel only (October 2026) |
Google Sheets: a range into one column
FLATTEN(range1, [range2, ...])Sheets' FLATTEN takes one or more ranges and returns every value in a single column, reading each row left
to right before moving down. With A1:B2 holding 1, 2 and 3, 4:
=FLATTEN(A1:B2)returns 1, 2, 3, 4 down one column. Blank cells are kept, not skipped
(Google Docs Editors Help).
Excel: lists inside cells
In September 2026 Excel gained cells that can hold lists and arrays (several values in one cell), and a
FLATTEN function that works on them:
FLATTEN(array, [pad_value], [levels])It removes one or more levels of nesting, so each cell's list spreads out into the grid. pad_value fills
the gaps when some lists are shorter, and levels sets how many levels to remove.
It's only in Excel's Beta Channel for now: Windows Version 2610 (Build 20520.20000) or later and Mac Version 16.114 (Build 26092111) or later (Microsoft 365 Insider blog).
The Excel equivalent of Sheets' FLATTEN: TOCOL
To turn a range into one column in Excel, the way Sheets' FLATTEN does, use TOCOL. It's in Excel for
Microsoft 365 and Excel 2024 (Windows and Mac):
TOCOL(array, [ignore], [scan_by_column])=TOCOL(A1:B2)returns 1, 2, 3, 4, reading row by row like Sheets. Two options Sheets' FLATTEN doesn't have:
- ignore:
1skips blanks,2skips errors,3skips both. - scan_by_column:
TRUEreads down each column instead of across each row.
=TOCOL(A1:D10, 1)turns A1:D10 into one column without the blank cells.
Ask in the app you're using
This is a common way for formulas to go wrong: the same function name, two apps, two behaviors. When you ask GPTExcel about a function and don't say which app, it asks. When the function is newer than the AI, it checks Microsoft's or Google's documentation first and tells you if it's Beta-only.