(Office Insider Beta only)
Windows: 2203 (Build 15104)
Mac: 16.60 (220304)

Returns the array in a single column.

Syntax

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

The TOCOL function syntax has the following arguments:

array       The array or reference to return as a column.

ignore       Whether to ignore certain types of values. By default, no values are ignored. Specify one of the following:

  • 0       Keep all values (default)

  • 1       Ignore blanks

  • 2       Ignore errors

  • 3       Ignore blanks and errors

scan_by_column       Scan the array by column. By default, the array is scanned by row. Scanning determines the sequence used to create the column.

Remarks

If scan_by_column is omitted or FALSE, the array is scanned by row; If TRUE, the array is scanned by column.

Errors

  • Excel returns a #VALUE! when an array constant contains one or more numbers that are not a whole number.

  • Excel returns a #NUM when array is too large.

Examples

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Returns a single column from the array in range A2:D4 scanned by row which is the default.

Data

Ben

Peter

Mary

Sam

John

Hillary

Jenny

James

Agnes

Harry

Felicity

Joe

Formulas

=TOCOL(A2:D4)

Returns a single column from the array in range A2:D4 scanned by row which is the default. Blank values return a 0.

Data

Ben

Peter

Mary

Sam

John

Hillary

Jenny

James

Agnes

Harry

Formulas

=TOCOL(A2:D4)

Returns a single column from the array in range A2:D4 scanned by row which is the default. Blank values are ignored.

Data

Ben

Peter

Mary

Sam

John

Hillary

Jenny

James

Agnes

Harry

Formulas

=TOCOL(A2:D4,1)

Returns a single column from the array in range A2:D4 scanned by column.

Data

Ben

Peter

Mary

Sam

John

Hillary

Jenny

James

Agnes

Harry

Formulas

=TOCOL(A2:D4,1,TRUE)

See Also

TOROW function

TAKE function

DROP function

EXPAND Function

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×