TOCOL in Excel: Turn a Grid into One Clean Column
Flatten an Excel grid into a column with TOCOL, control row or column scan order, and handle real blanks, zeros and errors explicitly.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: TOCOL rearranges a range into one column. Its optional arguments control which values are omitted and whether Excel reads across rows or down columns first.
Availability and version history
- First introduced: Preview announced March 16, 2022, for Windows Beta 2203 (build 15104.20004) and Mac 16.60 (build 22030400); web and Microsoft 365 Current Channel deployment reported September 29, 2022. Official introduction.
- Current documented status: the dedicated help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac. Availability depends on the installed build.
- Evidence checked: October 14, 2026. Official TOCOL help. Examples below were independently calculated, not executed in Excel.
Syntax and defaults
=TOCOL(array,[ignore],[scan_by_column])
ignore is 0 by default, keeping all entries. Use 1 to omit blanks, 2 to omit errors, or 3 to omit both. scan_by_column is FALSE by default, so the function reads across each row before moving to the next row; TRUE reads down each column instead. TOCOL reference
The output always has a column orientation. Changing scan order changes the sequence inside that column; it does not turn the result into a row. The source cells remain in place.
Example 1: flatten a numeric grid by rows
Enter this grid in A1:C2 with no headings:
10 20 30
40 50 60
In E1 enter:
=TOCOL(A1:C2)
Expected values in E1:E6 are 10, 20, 30, 40, 50, 60, from top to bottom. Excel reads the first source row left to right, then the second source row. There are six source cells and six returned values.
This is useful when each source row is a complete record sequence that must remain together. If the rows contain different fields rather than interchangeable items, flattening them may destroy the field relationships, so consider the meaning of the data before reshaping it.
Example 2: scan down columns instead
=TOCOL(A1:C2,0,TRUE)
Expected vertical sequence: 10, 40, 20, 50, 30, 60. Values from the first column come first, followed by the second and third columns.
The explicit zero keeps all entries. Compare the two results before using the output for a downstream lookup or export: both contain the same numbers, but their order differs.
Prepare blanks, a real zero and an error
On a separate sheet, set these six cells individually:
| Cell | Entry |
|---|---|
| A1 | Pen |
| B1 | Leave genuinely empty |
| C1 | Box |
| A2 | Pad |
| B2 | Numeric 0 |
| C2 | Formula =NA() |
Do not type the words “Leave genuinely empty” into B1. Do not use a formula returning "" in that cell: this example is specifically about a physically empty cell. C2 should display #N/A.
Example 3: preserve everything, including the error
=TOCOL(A1:C2)
Expected vertical sequence: Pen, 0, Box, Pad, 0, #N/A. The genuinely empty B1 is represented as zero in the result, while the real zero from B2 is also retained. That means the flattened result alone no longer distinguishes those two source cases.
The returned error remains an error value, not the text "#N/A". This may be desirable when a downstream report needs to flag incomplete source data.
Example 4: omit blanks and errors
=TOCOL(A1:C2,3)
Expected vertical sequence: Pen, Box, Pad, 0. The true blank and the error are removed; the numeric zero is not blank and remains.
Use ignore=1 if errors must remain visible, or ignore=2 if only errors should be removed. Ignoring errors does not repair them. If they indicate a failed import or missing lookup, keep a separate validation check rather than silently discarding them.
Advanced example: clean while preserving column groups
=TOCOL(A1:C2,3,TRUE)
Expected sequence: Pen, Pad, 0, Box. The function scans A1:A2, then B1:B2, then C1:C2, omitting the blank and error along the way.
This can help consolidate separately maintained category columns into a single list. Remember that the category headings are not attached automatically; flattening values alone does not preserve a separate category identifier.
Errors, size and spill space
Keep sufficient empty cells below the formula. A blocked output area is a spill-placement problem, not a reason to change the scan order. Avoid unnecessarily selecting entire worksheets or columns for a small consolidation; the official help also documents an oversized-array #NUM! error.
The help includes a #VALUE! warning for certain non-whole-number array constants. These examples use worksheet ranges, so that array-constant wording should not be generalized into a claim that normal decimal values in worksheet cells are unsupported.
Verification: Inputs are fictional. Expected values and array shapes were independently calculated from these inputs; no native Excel execution or screenshot is claimed. See the editorial testing standards.