TOCOL function

Quick answer

TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column.

Syntax: =TOCOL(array, [ignore], [scan_by_column])
Most common use: =TOCOL(A2:C3,1) returns Ana (Row by row, blanks skipped)

Its ignore argument can skip blanks, errors or both. Use it to stack a grid of names into one list and then feed that list to UNIQUE, SORT or COUNTA.

Syntax

=TOCOL(array, [ignore], [scan_by_column])

Argument	Required?	What it is
`array`	Required	The range or array to flatten.
`ignore`	Optional	Optional: 0 keeps everything (default), 1 skips blanks, 2 skips errors, 3 skips both.
`scan_by_column`	Optional	Optional: TRUE reads down each column first; FALSE (default) reads across each row.

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

Cell	Formula	Result	What it shows
E2	=TOCOL(A2:C3,1)	Anaspills 4 × 1	Row by row, blanks skipped
F2	=TOCOL(A2:C3,1,TRUE)	Anaspills 4 × 1	Column by column instead
G2	=COUNTA(UNIQUE(TOCOL(A2:C3,1)))	3	3 different people on the rota

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table

Graded instantly, then re-checked on changed data.

In F2, list every name from the rota in B2:D5 in a single column, reading across each row and skipping the empty cells.

What you see	Why, and how to fix it
Zeros where blanks were	Blank cells come through as 0. Set ignore to 1 to skip them.
#SPILL!	The cells below aren't empty.
The order is wrong	TOCOL reads across each row by default. Set scan_by_column to TRUE to read down each column.

How do I turn a range into one column in Excel?

=TOCOL(A2:D10) stacks every row into one column. Add 1 as the second argument to skip blanks.

How is TOCOL different from TRANSPOSE?

TRANSPOSE swaps rows and columns but keeps the grid; TOCOL turns any shape into a single column.

Updated October 28, 2026Checked by running: every formula on this page was run by our formula engine on October 30, 2026.