Practice free

TOCOL function

TOCOL flattens a block of cells into a single column. It is helpful for unpivoting data or feeding UNIQUE and COUNTIF, and it reads by row unless you tell it to scan by column.

Example

Read it as a sentence: stack the values in F1:F5 into one column and show the first value, which gives 0.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

array (required)
Array or range to flatten.
ignore (optional)
0 keep all (default), 1 skip blanks, 2 skip errors, 3 skip both.
scan_by_column (optional)
TRUE to read down columns first; default reads across rows.

Returns: A single column of the values.

One TOCOL formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=TOCOL({1,2;3,4}) 1 By row
=TOCOL({1,2;3,4},,TRUE) 1 By column
=TOCOL(5) 5 Scalar
=TOCOL(Data!F1:F5) 0 Blanks and error kept
=TOCOL(Data!F1:F5,1) 123 Ignore blanks
=TOCOL(Data!F1:F5,2) 0 Ignore errors
=TOCOL(Data!F1:F5,3) 123 Ignore both
=TOCOL({1,2},4) #VALUE! Bad ignore value

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Reading order is by row unless scan_by_column is TRUE.
  • Blanks come through as zeros unless you set ignore.

Where it works

Excel
TOCOL arrived in Excel 2024. Older versions do not have it. 365, Excel for the web and Excel 2024; not in older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .