Excel functions / Dynamic arrays
TOCOL function
Turns an array into a single column.
Syntax
=TOCOL(array, [ignore], [scan_by_column])
Arguments
array: The array to flatten.ignore(optional): 0 keeps everything (default), 1 leaves out blanks, 2 errors, 3 both.scan_by_column(optional): TRUE reads down each column; FALSE (default) reads along each row.
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
A grid as one column
A B C D E 1 Q1 Q2 Q3 10 2 10 20 30 20 3 40 50 60 30 4 40 5 50 6 60 In E1:
=TOCOL(A2:C3)→ 10; 20; 30; 40; 50; 60Read down the columns instead
A B C D E 1 Q1 Q2 Q3 10 2 10 20 30 40 3 40 50 60 20 4 50 5 30 6 60 In E1:
=TOCOL(A2:C3, 0, TRUE)→ 10; 40; 20; 50; 30; 60
Common mistakes
- Empty cells become zeros. Without 1 as the second argument, empty cells come through as 0.
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.