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

ArgumentRequired?What it does
arrayYesThe range or array to flatten.
ignoreOptional0 = keep all (default), 1 = skip blanks, 2 = skip errors, 3 = skip both.
scan_by_columnOptionalFALSE = 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)
ABCD
1AnaBenAna
2CaraDevBen
3Cara
4Dev
=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

Kyle Stott

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.