Excel functions / Dynamic arrays

TOCOL function

Turns an array into a single column.

Syntax

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

Arguments

When to use it

TOCOL turns a range into a single column, reading row by row (or column by column with TRUE as the third argument). With 1 as the second argument it skips empty cells. It flattens a grid so that one function can work on every value.

Examples

  1. A grid as one column

    ABCDE
    1Q1Q2Q310
    210203020
    340506030
    440
    550
    660

    In E1: =TOCOL(A2:C3) → 10; 20; 30; 40; 50; 60

    Try this example

  2. Read down the columns instead

    ABCDE
    1Q1Q2Q310
    210203040
    340506020
    450
    530
    660

    In E1: =TOCOL(A2:C3, 0, TRUE) → 10; 40; 20; 50; 30; 60

    Try this example

Common mistakes

TOCOL in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try TOCOL in the playground

Related functions