What is the syntax for TOCOL in Excel?

The syntax for TOCOL is: =TOCOL(array, [ignore], [scan_by_column])

What is TOCOL in Excel?

TOCOL flattens a 2D array into a single column. A 3×3 array becomes 9 values stacked vertically. It's useful for combining multiple columns, creating vertical lists, and preparing data for UNIQUE, SORT, or other array functions.

How do I combine multiple columns into one with TOCOL?

Use =TOCOL(A1:C100) to stack columns A, B, C into one column. Add ignore=1 to remove blanks: =TOCOL(A1:C100, 1). This is great for consolidating data from multiple columns into a single list.

How do I get unique values from multiple columns?

Combine TOCOL with UNIQUE: =UNIQUE(TOCOL(A1:C100, 1)). This flattens all columns, removes blanks, then returns unique values. Perfect for creating dropdown lists from multi-column data.

Lookup & Reference

TOCOL Function in Excel

Returns an array as a single column.

Syntax

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

Arguments

array (required): Array to convert
ignore (optional): 0=keep all, 1=ignore blanks, 2=ignore errors, 3=ignore both
scan_by_column (optional): FALSE=by row (default), TRUE=by column

Examples

=TOCOL(A1:C3) - Convert 3x3 to single column - Result: 9 values in column

When to use TOCOL

Use TOCOL when you need to retrieve values from a table or range based on a key.
Confirm data types match (text vs number) and trim spaces before lookups.
Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common TOCOL mistakes

#N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
Lock table references with $ when copying formulas down a column.
Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

Requires Excel 365

Use cases

Flatten arrays
Data transformation
Combine ranges

Frequently asked questions

What is TOCOL in Excel? TOCOL flattens a 2D array into a single column. A 3×3 array becomes 9 values stacked vertically. It's useful for combining multiple columns, creating vertical lists, and preparing data for UNIQUE, SORT, or other array functions.
How do I combine multiple columns into one with TOCOL? Use =TOCOL(A1:C100) to stack columns A, B, C into one column. Add ignore=1 to remove blanks: =TOCOL(A1:C100, 1). This is great for consolidating data from multiple columns into a single list.
How do I get unique values from multiple columns? Combine TOCOL with UNIQUE: =UNIQUE(TOCOL(A1:C100, 1)). This flattens all columns, removes blanks, then returns unique values. Perfect for creating dropdown lists from multi-column data.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.