FILTER returns a multi-column table. WRAPROWS needs a single column. UNIQUE produces a column you want to feed into TEXTJOIN. These transitions — from 2D array to flat vector — are the domain of TOCOL and TOROW. TOCOL flattens any range or array into a single column. TOROW does the same into a single row. Both give you control over which cells to include and the reading direction (row-by-row or column-by-column). They are the essential pre-processing step before functions that require 1D input.
This guide covers both functions fully with eight practical examples. You will learn how to flatten a table for UNIQUE deduplication and stack multiple columns. You will also convert rows to columns for SORT, clean blanks and errors, and build a master list from multiple named ranges. Each example also shows the old workaround so you see exactly what TOCOL and TOROW replace.
What Are TOCOL and TOROW?
TOCOL reads a 2D range and outputs a single vertical column. TOROW does the same but outputs a single horizontal row. Both functions flatten the source array in a specified reading order and can optionally filter out blanks, errors, or both. The ignore argument controls what to exclude before flattening.
The scan argument controls the reading direction. By default, both functions read left to right across each row, then down to the next. Setting scan = TRUE switches to column-by-column reading: top to bottom, then right to the next column. Specifically, the scan direction determines the order of values in the output.
What Is the TOCOL and TOROW Syntax?
| Argument | Required? | Description |
|---|---|---|
| array | Required | The 2D or 1D range or array to flatten. Can be a cell range, named range, or any dynamic array expression. |
| ignore | Optional | 0 (default) = keep all values. 1 = ignore blanks. 2 = ignore errors. 3 = ignore both blanks and errors. |
| scan_by_column | Optional | FALSE (default) = read row-by-row (left-right, then down). TRUE = read column-by-column (top-bottom, then right). Determines the order of values in the output. |
Examples 1–8: TOCOL and TOROW in Practice
The core use is converting a multi-column range into a single column (or row) for further processing. TOCOL reads row-by-row by default — all values from row 1, then row 2, and so on. TOROW does the same but outputs horizontally. Both preserve the original values exactly.
The ignore argument removes unwanted values during the flatten operation. This is much cleaner than running IFERROR or filtering blanks after the fact. Specifically, ignore = 1 drops blank cells, ignore = 2 drops error cells, and ignore = 3 drops both. The remaining values are packed together in reading order without gaps.
TOCOL on a multi-column range stacks all columns into a single list. This is the standard way to feed a 2D table into functions that require a 1D input, like UNIQUE for deduplication across multiple columns, or WRAPROWS for reshaping. Choosing scan = FALSE stacks columns left-to-right (rows first); scan = TRUE stacks column-by-column (columns first).
Many functions work with columns but not rows, or the other way around. TOCOL on a single row converts it to a column. TOROW on a single column converts it to a row. This is a simpler alternative to TRANSPOSE in many situations, especially when combined with ignore to clean blanks at the same time.
Examples 5–8: Advanced TOCOL and TOROW Patterns
WRAPROWS requires a 1D input. TOCOL is the standard way to flatten a multi-column table before passing it to WRAPROWS. This combination converts a 2D table into a flat list, then reshapes the list into a grid of specified width. The ignore argument additionally removes blanks during flattening so the resulting grid has no empty slots from sparse source data.
TOCOL alone handles one contiguous range. To combine multiple non-contiguous ranges into a single column, first stack them vertically with VSTACK, then pass to TOCOL with ignore = 1 to remove blanks. This is the standard pattern for building a master dropdown list from several separate named ranges or tables.
TEXTJOIN expects a one-dimensional range or array. When you want to join values from a 2D table into a single string, TOROW (or TOCOL via ARRAYTOTEXT) flattens the table first. TOROW with ignore = 1 also removes blanks before joining, so the resulting string has no trailing commas or double-separator artefacts from empty cells.
UNIQUE on a single column removes duplicates within that column. UNIQUE across multiple columns removes duplicate rows. But what if you want to deduplicate individual values that appear in any cell of a 2D range? TOCOL flattens the range first, then UNIQUE deduplicates the resulting list. This pattern builds a comprehensive unique-value list from any 2D source, regardless of how the data is arranged.
Common Issues and How to Fix Them
TOCOL changes the order I expected
The output order depends on the scan_by_column argument. By default (scan = FALSE), TOCOL reads row-by-row — all values from row 1 left-to-right, then row 2, and so on. Setting scan = TRUE switches to column-by-column — all values from column A top-to-bottom, then column B, and so on. If the order is unexpected, toggle the scan argument and test on a small range first.
TOCOL returns #VALUE! or a wrong result
The array argument must be a range or array expression, not a text string. Additionally, TOCOL and TOROW are Excel 365-only functions; they return #NAME? in older versions. Also verify the ignore argument: 0 keeps everything, 1 removes blanks, 2 removes errors, 3 removes both. An incorrect ignore value is a common source of missing or extra values in the output.
Frequently Asked Questions
-
What does TOCOL do in Excel?+TOCOL flattens a 2D range into a single vertical column. It reads the source values in a specified order (row-by-row by default, or column-by-column if scan=TRUE) and stacks them into one column. The ignore argument excludes blank cells (1), errors (2), or both (3). TOCOL is available in Excel 365 only. Its primary use is converting 2D ranges into 1D arrays for UNIQUE, WRAPROWS, WRAPCOLS, and TEXTJOIN.
-
What is the difference between TOCOL and TRANSPOSE?+TRANSPOSE rotates an array — rows become columns and columns become rows. It does not flatten; a 3×4 array becomes a 4×3 array. TOCOL flattens — it always produces a single column regardless of the source dimensions. A 3×4 array becomes a 12×1 column. Additionally, TOCOL supports the ignore argument for filtering blanks and errors — TRANSPOSE does not. Specifically, use TRANSPOSE when you need to rotate a 2D structure. Use TOCOL when you need a flat 1D vector from any shape of array.
-
How do I get unique values across multiple columns using TOCOL?+Wrap TOCOL around the multi-column range with ignore=1 to remove blanks, then wrap UNIQUE around the result: =SORT(UNIQUE(TOCOL(A2:E20, 1))). TOCOL flattens all columns into one list first, and UNIQUE then deduplicates the entire list regardless of which column each value came from. This gives every distinct value across the range in a sorted list. Use this pattern to build dynamic dropdown lists that cover values from multiple columns.
-
Is there a TOCOL equivalent in Google Sheets?+Google Sheets has a FLATTEN function that behaves similarly to TOCOL with ignore=0: it flattens a 2D range into a single column, reading row-by-row. However, FLATTEN does not have a built-in ignore argument for removing blanks — you need to wrap it with FILTER: =FILTER(FLATTEN(A2:C10), FLATTEN(A2:C10) <> "") to exclude blank cells. Google Sheets does not have TOROW, but you can use TRANSPOSE(FLATTEN(...)) to get a flat row equivalent.