TOCOL in Google Sheets, Flatten a Range
By Szabó Gergő · Updated
TOCOL walks a range and writes every cell into one column. Skip blanks if you only want values.
Syntax and arguments
=TOCOL(array_or_range, [ignore], [scan_by_column])- array_or_range
- Block to flatten.
- ignore
- 0 keep all, 1 skip blanks, 2 skip errors, 3 skip both.
- scan_by_column
- TRUE walks down each column. FALSE walks by row.
TOCOL examples
01
One column of values
Block A2:C10.
=TOCOL(A2:C10,1)Stacks A:C and drops blanks.
02
Unique across a grid
Need distinct values from a block.
=UNIQUE(TOCOL(A2:C10,1))Distinct values from a block, not one column.
03
Scan by column
Want A then B then C.
=TOCOL(A2:C4,0,TRUE)Walks down A, then B, then C.
Common mistakes
Using FLATTEN out of habit
TOCOL adds ignore and scan-by-column options. Prefer it for new files.
Keeping errors you wanted dropped
Use ignore 2 or 3.
Expecting a row
TOROW writes one row. TOCOL writes one column.
TOCOL FAQ
TOCOL or FLATTEN?
FLATTEN is older Sheets. TOCOL adds ignore and scan-by-column options.
Does Excel have TOCOL?
Excel 365 has TOCOL with the same idea.
Can I go the other way?
TOROW writes one row. WRAPCOLS and WRAPROWS rebuild a grid.