TOCOL Function. Sort range of cells into a single column.(Microsoft Excel)


Japanese version.

The TOCOL function is a new function added to Excel in September 2022 to sort a specified cell range into a single column.

While the WRAPROWS and WRAPCOLS functions sorted a single-row/single-column range of cells into multiple rows and columns, this function sorts a multi-row/multi-column range into a single column.

How it works

=TOCOL(array, ignore, scan_by_column)
arrayRequired argument.
Specify the target cell range.
ignore0Specifies whether to ignore certain values.
0: Do not ignore. (Show all)
1: Ignore blanks.
2: Ignore errors.
3: Ignore whitespace and errors.
scan_by_columnFALSESpecifies in which order the cell ranges should be ordered.
TRUE: row direction
FALSE: column direction


The first argument specifies the range to be sorted.

Argument 2:ignore

Specify the second argument if you wish to ignore certain values.

If omitted or 0, no values are ignored and all values are displayed.

If 1 is specified, blank cells are ignored.
In the non-ignore state, the blank cell is 0.

Specify 2 to ignore formula errors such as #DIV/0 and #N/A.

Specifying 3 ignores both formula errors and blank cells.

Argument 3:scan_by_column

Specifies the order in which the specified ranges are ordered.

If FALSE or omitted, the order is column order.

If TRUE is specified, the order is rows .



Microsoft Excel Functions Lookup & Reference