When working with large datasets in Google Sheets, you frequently encounter data spread across multiple columns and rows in a grid format. While grids are easy for humans to read, they are notoriously difficult to analyse using Pivot Tables or database functions, which typically require “flat” data (a single, continuous column or row).
Historically, transforming a 2D grid into a single column required complex, fragile formulas combining INDEX, MATCH, MOD, and ROW functions. Google Sheets has permanently solved this problem with two elegant, dedicated functions: TOCOL and TOROW.
How to Use TOCOL
The TOCOL function takes an array of data (a grid of multiple columns and rows) and flattens it into a single, continuous vertical column.
Basic Syntax
The syntax is straightforward:
=TOCOL(array, [ignore], [scan_by_column])
- array: The range of cells you want to flatten (e.g.,
A1:C5). - ignore (optional): An integer dictating how to handle empty cells or errors.
- scan_by_column (optional): A boolean (TRUE/FALSE) dictating the order in which the grid is read.
Example Usage
Imagine you have a grid of employee names spread across cells A1 through C5. To flatten this entire grid into a single column starting in cell E1, simply type:
=TOCOL(A1:C5)
By default, TOCOL reads the data row by row, from left to right. It will take A1, B1, C1, then A2, B2, C2, stacking them vertically one after the other.
Changing the Scan Direction
If you prefer the data to be stacked column by column (reading A1, A2, A3, then B1, B2, B3), you must set the scan_by_column argument to TRUE.
=TOCOL(A1:C5, 0, TRUE)
Note: We use 0 for the second argument as a placeholder to reach the third argument.
Handling Blanks and Errors
Grids of data often contain empty cells. By default, TOCOL includes these empty cells in the final output column, resulting in unsightly gaps.
You can use the ignore argument to automatically filter out unwanted data during the flattening process:
- 0: Keep all values (default).
- 1: Ignore empty cells (blanks).
- 2: Ignore errors (e.g., #N/A, #DIV/0!).
- 3: Ignore both blanks and errors.
To flatten a messy grid while automatically stripping out all blank cells, use:
=TOCOL(A1:C5, 1)
How to Use TOROW
The TOROW function works exactly like TOCOL, but instead of stacking the data vertically into a single column, it lays the data out horizontally into a single row.
=TOROW(A1:C5)
This will take the 2D grid and stretch it across a single row from left to right. Like TOCOL, it supports the exact same ignore and scan_by_column arguments.
To flatten a grid into a single row, ignoring blanks and errors, and reading the original grid column by column:
=TOROW(A1:C5, 3, TRUE)
Combining with SORT and UNIQUE
Because TOCOL and TOROW output clean, dynamic arrays, they are incredibly powerful when wrapped inside other functions.
For example, if you want to extract every unique name from a messy grid, ignore the blank cells, stack them into a single column, and sort them alphabetically, you can combine the functions like this:
=SORT(UNIQUE(TOCOL(A1:C5, 1)))
These functions eliminate the need for manual copy-pasting or complex data-wrangling formulas, allowing you to instantly prep messy grid data for serious analysis.