Quick Navigation
TOCOL Function
Summary
The TOCOL function transforms any array or range into a single column, making it perfect for data reshaping, dynamic lists, and preparing data for other functions that require single-column input.
Syntax
TOCOL(array, [ignore], [scan_by_column])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array/Range |
Yes | Input array or cell range to transform into single column |
| ignore | Number |
No | Controls which values to skip: 0=all, 1=blanks, 2=errors, 3=both |
| scan_by_column | Boolean |
No | TRUE reads data column-by-column instead of row-by-row |
Using the TOCOL Function
TOCOL is essential for modern Excel dynamic array workflows. It quickly reshapes multi-column data into a single column for use with functions like UNIQUE, SORT, FILTER, or for creating dropdown lists and data validation sources.
Common TOCOL Examples
Basic Row-by-Row Conversion
=TOCOL(A2:D4)Transforms a 3x4 table into single column reading left-to-right, top-to-bottom (Ben, Peter, Mary, etc.)
Ignore Blank Cells
=TOCOL(A2:D4,1)Same table but skips empty cells, producing cleaner output list
Column-by-Column Scan
=TOCOL(A2:D4,,TRUE)Reads first column top-to-bottom, then second column, etc. (Ben, John, Agnes, etc.)
Skip Blanks and Errors
=TOCOL(A2:D5,3)Ignores both blanks and any error values in the range
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: ignore parameter contains non-whole number
Solution: Use only 0, 1, 2, or 3 for ignore parameter
#NUM!
Cause: Input array too large for Excel
Solution: Reduce range size or use smaller input
#VALUE!
Cause: scan_by_column not TRUE/FALSE
Solution: Use TRUE, FALSE, 1, or 0 only
Notes
- Available only in Excel 365 and Excel 2021
- Blanks default to 0 unless ignored
- Maximum output size limited by Excel's array constraints
- Perfect companion for UNIQUE, SORT, FILTER functions
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021