Excel like a finance pro.
← The libraryPractice · 3 questions →
Dynamic array function
TOCOL
Flattens an array into a single column.
OccasionalDifficulty 1400 · AdvancedUsage rank #112 of 520
When to use it
Flattens an array into a single column, row by row (or column by column with the last argument). Options skip blanks and errors.
The shape of it
- Syntax
=TOCOL(array, [ignore], [scan_by_column])
Worked examples
Flatten a block
=TOCOL(A1:C2) → {A1;B1;C1;A2;B2;C2}
Row by row.
Skip blanks
=TOCOL(A1:C3,1) → non-blank values
Ignore mode 1 drops blanks.
By column
=TOCOL(A1:C2,,TRUE) → {A1;A2;B1;B2;C1;C2}
Column by column.
Worth knowing
- Unpivot a grid into a list: TOCOL for values, then SEQUENCE math for the labels.
- Ignore mode 2 drops errors, 3 drops both.
- Distinct values across a grid: UNIQUE(TOCOL(range,1)).
Where it goes wrong
- Requires Microsoft 365.
- Blanks become 0 unless ignored.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.