Data imported from surveys, matrix reports, or inventory schedules often arrives arranged across multi-column, multi-row grids. However, modern database functions, pivot tables, and lookup operations (like XLOOKUP and FILTER) require data structured in normalized, single-column lists. Previously in Google Sheets, flattening a 2D grid into a single column required writing convoluted formulas combining FLATTEN, SPLIT, and TRANSPOSE. The native TOCOL function solves this by transforming any two-dimensional array into a single vertical column with a single calculation.
Understanding the Syntax of TOCOL
The TOCOL function accepts four parameters, offering fine-grained control over how blank cells, error values, and scanning directions are handled:
=TOCOL(array, [ignore], [scan_by_column])Argument breakdown:
array: The two-dimensional range, array, or dynamic formula output you want to transform into a single column.[ignore]: A numeric flag specifying which cell types to omit from the output:0(Default): Keep all values, including blanks and errors.1: Ignore blank cells.2: Ignore error values (#N/A,#VALUE!, etc.).3: Ignore both blank cells and errors.
[scan_by_column]: A boolean flag determining the order of extraction:FALSE(Default): Scans by row (left-to-right, then down).TRUE: Scans by column (top-to-bottom, then right).
Basic Example: Flattening a 3×4 Matrix
Suppose you have product SKU numbers spread across columns A, B, and C (range A2:C10), containing scattered empty cells where items are out of stock. To extract all SKUs into a single clean column starting in cell E2 while ignoring empty cells:
=TOCOL(A2:C10, 1)Google Sheets immediately spills a single vertical list down column E. By passing argument 1, any empty cells within A2:C10 are omitted from the output stream, leaving zero gaps in your list.
Scanning by Row vs Scanning by Column
Controlling the reading sequence is critical when maintaining chronological or grouped ordering:
| Scan Mode | Formula Example | Processing Order |
|---|---|---|
| Scan by Row (Default) | =TOCOL(A1:C3, 0, FALSE) |
A1, B1, C1, A2, B2, C2, A3, B3, C3 |
| Scan by Column | =TOCOL(A1:C3, 0, TRUE) |
A1, A2, A3, B1, B2, B3, C1, C2, C3 |
Pairing TOCOL with UNIQUE and SORT
One of the most practical workflows is consolidating scattered entries and removing duplicates in one step. For instance, to consolidate email addresses entered across multiple signup sheets (range A2:D50), remove blank cells, eliminate duplicate submissions, and sort alphabetically:
=SORT(UNIQUE(TOCOL(A2:D50, 1)))Filtering Out Calculation Errors
If your source matrix contains lookup formulas that produce intermittent #N/A or #DIV/0! errors, pass 3 into the ignore parameter:
=TOCOL(A2:D20, 3)This automatically strips both blanks and error cells, producing a sanitised list ready for charting or statistical evaluation.
Mastering the TOCOL function streamlines data wrangling in Google Sheets, enabling analysts to normalize sprawling grid layouts into structured columns with minimal formula overhead.