TOCOL

Description

Use this function to an array as a single column.

Syntax

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

Inputs

This function accepts the following arguments:

Name Required Description Valid Input

array Yes The range or array to return as a column. A cell range, a formula that returns a cell range, or a reference to a cell containing a cell range.

ignore

Whether to ignore certain types of values. By default, no values are ignored.

One of the following values:

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

scan_by_column

Whether to scan the array by column or by row. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.

If no value is provided, this is ignored. To use this value, a value for ignore must be provided. A space counts as "0".

FALSE or 0 = Scan by row (default)
TRUE or 1 = Scan by column

Examples

Sample data

	A	B	C	D	E
1	Name	Age	City	Salary	Position
2	John	35	New York	75000	Sales Lead
3	Alice	28	Chicago	65000	CSM
4	Mary	49	Ames	82500

Sample formula

Use case Formula Explanation and Result

Return the supplied array as a single column using the defaults.

=TOCOL(A2:E4)

This formula returns all the values in the array A2:D4 collected by row (the default setting).

For this data set, this formula returns the following:

John
35
New York
75000
Sales Lead
Alice
28
Chicago
65000
CSM
Mary
49
Ames
82500
0

Return the supplied array as a single column collected by row, but ignore blank cells.

=TOCOL(A2:E4,1)

This formula returns all the values in the array A2:D4 collected by rows, and ignoring blanks (1).

For this data set, this formula returns the following: (note that the last row contains the contents of D4, and that E4, which is blank, is not included)

John
35
New York
75000
Sales Lead
Alice
28
Chicago
65000
CSM
Mary
49
Ames
82500

Return the supplied array as a single column collected by column, but include blank cells.

=TOCOL(A2:E4, ,1)

This formula returns all the values in the array A2:D4 collected by columns. As no value is provided for ignore, blank cells are included as zeros.

For this data set, this formula returns the following: (note that the last row contains the contents of D4, and that E4, which is blank, is not included.)

John
Alice
Mary
35
28
49
New York
Chicago
Ames
75000
65000
82500
Sales Lead
CSM
0

Return a list of the names of all employees whose salary is greater than $70,000.

=TOCOL(FILTER(A2:A4, D2:D4>70000))

In this formula:

FILTER(A2:A4, D2:D4>70000): FILTER looks at the salary column (D2:D4) and checks for values greater than 70,000. It then returns a new, clean array containing only the corresponding names from the A2:A4 range. The non-matching rows are not included at all.
TOCOL(…,1) takes the results of the IF function and converts it into a single column.
The final output is a single column with the names of the employees who meet the salary criteria; in this case, John and Mary.

John

Mary

Notes

Column numbers start from 1, not 0. So "A" is column 1.
Empty cells are returned as "0". (See the first sample formula for an example.)
ignore only discards cell that are actually empty or actual errors. FALSE or TRUE values are treated as text, and so are included.

Tips

TOCOL can be combined with other functions, including:

FILTER: To select specific columns from filtered data.
SORT: To rearrange columns.
UNIQUE: To extract unique values from selected columns.
SEQUENCE: To create dynamic column selections.
INDEX: For advanced column referencing.
MATCH: For dynamic column selection based on criteria.
TRANSPOSE: To reorient selected columns.
CHOOSEROWS: For more complex data selection.

Related functions

Here are the other array formula functions supported in Workiva spreadsheets.

Support

Community

Workiva Support Center