ID EN
…
#Dynamic array

TOCOL

Excel Functions

Transform array to single column

Syntax

EXCEL 
=TOCOL(array, ignore, scan_by_column)

Arguments

Parameter Description
array The array to transform.
ignore Setting to ignore blanks and errors.
scan_by_column Scan array by column. TRUE = by column, FALSE = by row (default).

Return Value

A single column of values

Details

The TOCOL function transforms an array or range into a single column. By default, TOCOL will scan values by row, left to right. However, TOCOL can also be configured to scan the array by column, top to bottom. TOCOL also provides options for skipping empty cells and errors. The TOCOL function takes three arguments: array, ignore, and scan_by_column. Array is the only required argument and represents the array or range to be transformed. The ignore argument controls what values TOCOL will optionally ignore. The options for ignore are as follows: The scan_by_column argument is a boolean value that controls how TOCOL reads values from the source array. By default, scan_by_column is FALSE and TOCOL will read values "by row" from left to right. At the end of each row, TOCOL will drop down and r

Examples

Basic usage

By default, the TOCOL function transforms a two-dimensional array into a single column, working through the array one row at a time. In the example be

EXCEL 
=TOCOL(B4:D5)
Ignore blanks and errors

The ignore argument in TOCOL can be set to ignore blanks and/or errors like this:

EXCEL 
=TOCOL(array) // default
=TOCOL(array,1) // ignore blanks
=TOCOL(array,2) // ignore errors
=TOCOL(array,3) // ignore blanks and errors
Scan by column

By default, TOCOL will read values "by row" from left to right. To read values instead by column, set scan_by_column to TRUE or 1. The worksheet below

EXCEL 
=TOCOL(B4:D5,,TRUE)

See Also

TOROW WRAPROWS WRAPCOLS TRIMRANGE