What TOCOL does
TOCOL puts each value from a grid into its own cell in one vertical list. By default, it reads across the first source row, then across the next. You can switch to reading down each column without changing the vertical shape of the result.
Practical examples
Read across rows into one list
Enter this grid in B4:D5:
| Row | B | C | D |
|---|---|---|---|
| 4 | 11 | 12 | 13 |
| 5 | 21 | 22 | 23 |
Leave F4:F9 empty and enter this in F4:
=TOCOL(B4:D5)FormulaThe expected six-row result is:
11
12
13
21
22
23ExampleExcel reads B4, C4, and D4 before moving to B5, C5, and D5.
Finish each source column before reading the next
With the same grid, leave H4:H9 empty and enter this in H4:
=TOCOL(B4:D5,0,TRUE)FormulaThe expected result is:
11
21
12
22
13
23ExampleTRUE changes the reading order to B4, B5, C4, C5, D4, D5. The middle argument, 0, keeps every entry. Compare the second result: it is now 21 instead of 12.
Omit a blank and an error while keeping zero
On a separate sheet, enter 11 in B4, leave C4 truly empty, and enter 13 in D4. Enter 21 in B5, numeric 0 in C5, and the error value #N/A in D5. In an empty cell, enter:
=TOCOL(B4:D5,3)FormulaThe expected result is the four-row column 11, 13, 21, 0. The setting 3 omits the blank and the error. The numeric zero remains a valid entry.
Common mistakes and notes
Scan direction doesn't control output orientation
scan_by_column changes how TOCOL reads its input. The output is still one column. Use TOROW for a horizontal list or TRANSPOSE to swap axes while keeping a grid.
A default blank result can appear as zero
Microsoft's examples show that a truly blank source cell becomes zero when the ignore argument is omitted. Use 1 to omit blanks while retaining errors, or 3 to omit both. A formula returning empty text and a cell containing a space are different source contents; don't treat every empty-looking cell as an unused cell.
Ignoring errors removes entries
Modes 2 and 3 shorten the list by excluding source errors. They don't repair those errors. Check the source before choosing an option that removes them from view.
Keep headings and repeated values in mind
TOCOL includes headings if they are inside array, and it preserves duplicates. Keep header cells outside the selected range when they aren't list entries. The multiple-column list tutorial walks through the scan order and blank-handling choices.