TOCOL Function
Flattens a block of cells into one column.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2024
Returns
A single spilled column
Want to practice TOCOL in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox
Overview
TOCOL takes a grid and stacks every value into a single column. You can skip blanks and errors, and choose whether to read across rows or down columns first.
It's ideal for turning a messy 2D layout into a clean list you can sort, count or de-duplicate.
Syntax
=TOCOL(array, [ignore], [scan_by_column])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array to flatten. |
| ignore | Optional | 0 = keep all (default), 1 = skip blanks, 2 = skip errors, 3 = skip both. |
| scan_by_column | Optional | FALSE = read row by row (default), TRUE = read column by column. |
Examples
Example 1 — A grid into a list
Flatten a 2×2 block, reading across each row.
D1fx=TOCOL(A1:B2)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ana | Ben | Ana | |
| 2 | Cara | Dev | Ben | |
| 3 | Cara | |||
| 4 | Dev |
=TOCOL(A1:B2) Explain
Example 2 — A unique, clean list from a roster
Shift rosters have names spread across several columns, with blanks.
=SORT(UNIQUE(TOCOL(B2:F20, 1))) Explain
Use ignore 1 whenever your source has blanks, or they'll appear as zeros.
Common mistakes
- Zeros in the result. Blank cells come through as 0 unless you set ignore to 1 or 3.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.