Excel TOCOL Function

The Excel TOCOL function returns an array as a single column. It’s handy for combining data across multiple columns and rows into a single column.

TOCOL Function Syntax

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

array : The array or reference to return as a column.

ignore : [Optional] Use the values listed below to specify whether to ignore certain types of values. By default, no values are ignored.

• 0 : Keep all values (default)
• 1 : Ignore blanks
• 2 : Ignore errors
• 3 : Ignore blanks and errors

scan_by_column : [Optional] Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values in the function result are ordered by row or by column.

Note: The TOROW function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Microsoft 365. Excel 2021 and earlier Excel versions will not get TOROW.

Watch the Video

Download the Example Workbook

Excel TOCOL Function Examples

We can use TOCOL to combine the data in cells C24:G27 into a single column:

Notice the blanks have returned zero values. We can hide these with the ignore argument:

And with the scan_by_column argument, we can change the order of the result, to scan the input array by column. Meaning that in the output array data is stacked column by column, rather than row by row which is the default:

Related Excel Array Shaping Functions