Dynamic arrays function
Excel TOCOL function
TOCOL takes a range of any shape and returns all its values in one column. It reads across each row by default, can read down each column instead, and can skip blank cells, errors or both. It is the quick way to flatten a roster, a matrix or several columns into one list.
=TOCOL(array, [ignore], [scan_by_column])Arguments
array- The range or array to flatten.
ignore- Optional. 0 keeps everything (default), 1 skips blanks, 2 skips errors, 3 skips blanks and errors. A kept blank shows as 0.
scan_by_column- Optional. FALSE reads row by row (default); TRUE reads down each column in turn.
TOCOL examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Shift | Mon | Tue | Wed |
| 2 | Early | Ana | Ben | |
| 3 | Late | Cy | Ana | |
| 4 | Night | Dee | Ben | Eli |
Result: spills 7 rows × 1 column
Ana Ben Cy Ana Dee Ben Eli All seven names in one column, read across each row, with the two empty slots left out.
Result: spills 7 rows × 1 column
Ana Cy Dee Ben Ben Ana Eli TRUE reads down each column instead, so the list goes day by day: Ana, Cy, Dee for Monday first.
Result: spills 5 rows × 1 column
Ana Ben Cy Dee Eli A sorted list of everyone on the rota, each name once: Ana, Ben, Cy, Dee and Eli.
Result: spills 9 rows × 1 column
Ana Ben 0 Cy 0 Ana Dee Ben Eli Without ignore, the empty slots are kept and show as 0, which is rarely what you want. Use 1 to skip them.
How to use TOCOL
- Click a cell with empty space below it.
- Type =TOCOL( and select the range.
- Add ,1 to skip blank cells (or ,3 to skip blanks and errors).
- Add ,TRUE to read down the columns instead of across the rows, then press Enter.
Common TOCOL mistakes
Zeros where cells were empty
TOCOL keeps blanks as 0 unless you pass 1 or 3 as the second argument.
The wrong order
TOCOL reads row by row. For a column-by-column list, set the third argument to TRUE.
Older Excel
TOCOL needs Excel for Microsoft 365, Excel 2024 or Excel for the web. Older versions show #NAME?.
TOCOL FAQ
How do I convert a table into one column in Excel?
Use =TOCOL(range,1). It lists every value, skipping blanks. Wrap it in UNIQUE to remove duplicates and SORT to order it.
How do I stack several columns into one in Excel?
=TOCOL(B2:D10,1,TRUE) puts column B first, then C, then D. VSTACK(B2:B10,C2:C10,D2:D10) does the same for columns that are not next to each other.
What is the difference between TOCOL and TOROW?
They take the same arguments. TOCOL returns one column and TOROW one row.
Guides that use TOCOL
Related functions
TOROWTurns a range into one row, spillsUNIQUERemoves duplicates, spillsVSTACKStacks arrays one under anotherWRAPROWSWraps a list into rows, spills
Updated