Back to functions
Lookup & Reference2026-11-161 related article

TOCOL Function in Excel

Turn a range or array into one column, choosing the reading order and whether to omit blanks or errors.

Syntax

TOCOL(array, [ignore], [scan_by_column])
Table of Contents

Arguments

array

Required

The range or array to turn into a single column.

ignore

Optional

Use 0 to keep all values (default), 1 to omit blanks, 2 to omit errors, or 3 to omit blanks and errors.

scan_by_column

Optional

FALSE or omitted reads across each source row. TRUE reads down each source column.

What it returns

A single-column array in the chosen scan order, keeping repeated values unless they are excluded by the ignore setting.

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:

RowBCD
4111213
5212223

Leave F4:F9 empty and enter this in F4:

=TOCOL(B4:D5)
Formula

The expected six-row result is:

11
12
13
21
22
23
Example

Excel 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)
Formula

The expected result is:

11
21
12
22
13
23
Example

TRUE 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)
Formula

The 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.

Related functions

Related articles

Deep dives, troubleshooting guides, and practical examples that use TOCOL.

Official documentation