Consolidating multi-row and multi-column tables into a single continuous list is one of the most frequent data transformation tasks in Microsoft Excel. Historically, flattening a two-dimensional grid—such as monthly attendance sheets, staff schedules, or inventory matrices—into a single vertical column required building unwieldy formula chains combining INDEX, INT, MOD, ROW, and COLUMNS, or taking data into Power Query. Excel modernised dynamic array transformations with the introduction of TOCOL and TOROW. These streamlined functions allow spreadsheet professionals to transform two-dimensional arrays into a single column or single row with optional filtering for blanks and errors.
Understanding the Syntax of TOCOL and TOROW
Both functions share identical arguments, differing solely in their output orientation (TOCOL yields a vertical column vector; TOROW yields a horizontal row vector).
The standard syntax for TOCOL:
=TOCOL(array, [ignore], [scan_by_column])The standard syntax for TOROW:
=TOROW(array, [ignore], [scan_by_column])Let’s examine the three core arguments:
array: The source table, range, or formula array to transform.ignore(optional): Governs how empty cells and calculation errors are handled:0: Keep all values (default).1: Ignore blank cells.2: Ignore error values (e.g.,#N/A,#VALUE!).3: Ignore both blank cells and errors simultaneously.
scan_by_column(optional): Controls scanning direction:FALSE(or omitted): Scans horizontally by row (Left to Right, then Top to Bottom).TRUE: Scans vertically by column (Top to Bottom, then Left to Right).
Basic Example: Flattening a Weekly Roster into a Vertical Column
Suppose you have a schedule where employees are listed across columns A2:E10, representing Monday through Friday. To compile all names into a single clean vertical list in column G:
Enter the following formula into cell G2:
=TOCOL(A2:E10)Excel scans across each row from left to right, spilling the complete set of names downward into a single vertical vector.
Filtering Out Blanks and Error Values Automatically
Real-world datasets often feature empty cells representing non-working days or sporadic formula errors. If cell range A2:E10 contains blank slots that you wish to purge:
Enter this formula:
=TOCOL(A2:E10, 1)The ignore=1 parameter instructs Excel to skip every empty cell, producing a tight, contiguous list containing only active entries.
If your source range also contains lookup errors (like #N/A) alongside empty cells, use mode 3:
=TOCOL(A2:E10, 3)Both empty cells and formula glitches are stripped simultaneously without requiring nested IFERROR wrappers.
Scanning by Column vs. Scanning by Row
By default, TOCOL processes records horizontally. If your table is organized chronologically down columns (e.g., Column A represents January, Column B represents February), you will want to read down Column A before moving to Column B.
To scan vertically by column, set the third parameter to TRUE:
=TOCOL(A2:E10, 1, TRUE)TOCOL and TOROW Argument Reference
| Argument | Supported Inputs | Default Behavior | Key Capability |
|---|---|---|---|
array |
Range or Dynamic Array | Required | Identifies input grid |
ignore |
0, 1, 2, 3 |
0 (Keep all) |
Filters out empty spaces and error badges |
scan_by_column |
TRUE / FALSE |
FALSE (By Row) |
Toggles horizontal vs vertical traversal |
Pairing TOCOL with UNIQUE and SORT
Because TOCOL outputs a native dynamic array, it chains seamlessly into other modern Excel functions:
To extract an alphabetised, de-duplicated roster from a chaotic multi-department schedule:
=SORT(UNIQUE(TOCOL(A2:E10, 1)))In a single line, Excel flattens the multi-column schedule, strips all blank cells, purges duplicate names, and sorts the remaining unique staff members alphabetically from A to Z.
Frequently Asked Questions
Which versions of Microsoft Excel support TOCOL and TOROW?
TOCOL and TOROW are supported in Microsoft 365 (Windows, Mac, and Web) and Excel 2024. Legacy editions such as Excel 2019 and Excel 2016 do not support dynamic array matrix functions.
Can I transpose the result of TOCOL into a row?
Yes, although using TOROW directly is more efficient than wrapping TRANSPOSE(TOCOL(...)).
What happens if the entire source array consists of blank cells?
If you set ignore=1 or ignore=3 and the source range contains only blank cells, Excel returns a #CALC! error because the function cannot produce an empty array. Wrap the formula in IFERROR(TOCOL(A2:E10, 1), "No Data") for defensive workbook design.