Tabular data entry often organizes information across two-dimensional matrix grids—such as weekly employee shift rosters, quarterly regional sales sheets, or multi-column product code tables. However, feeding this data into downstream lookup formulas like XLOOKUP, summary pivot tables, or chart series requires transforming expansive 2D grids into clean, single-dimension vertical columns or continuous horizontal rows. Historically, flattening matrices in Google Sheets required tedious SPLIT(FLATTEN(...)) workarounds. Google Sheets now provides native array transformation functions: TOCOL and TOROW.
Syntax and Parameters of TOCOL
The TOCOL function transforms an array or range of cells into a single vertical column:
=TOCOL(array, [ignore], [scan_by_column])array: The source matrix range to transform (e.g.,A2:D50).[ignore]: Optional integer specifying which values to omit during flattening: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]: Boolean determining traversal direction:FALSE(Default): Scans horizontally by row (reads row 1 left-to-right, then row 2, etc.).TRUE: Scans vertically by column (reads col 1 top-to-bottom, then col 2, etc.).
Basic TOCOL Example: Flattening Grids and Stripping Blanks
To convert a messy attendance matrix spanning columns A through F into a single list of employee names, omitting all absent blank cells:
=TOCOL(A2:F20, 1)The output spills down a single vertical column containing only populated names without empty gap rows.
Syntax and Applications of TOROW
The TOROW function flattens multi-row ranges into a single horizontal row:
=TOROW(array, [ignore], [scan_by_column])Its arguments mirror TOCOL, accepting the same ignore modes and scan direction parameters.
Example: Consolidating Multi-Day Schedules into a Continuous Header Row
# Flatten a 5x5 task grid into a continuous horizontal sequence, ignoring errors and blanks
=TOROW(B2:F6, 3)Scanning by Column vs Scanning by Row
Controlling the scan direction ensures chronological data sequencing:
# Process data column-by-column (Monday through Friday sequentially)
=TOCOL(B2:F20, 1, TRUE)Setting scan_by_column to TRUE reads all Monday entries first before proceeding to Tuesday, maintaining temporal order.
Combining with UNIQUE and SORT for Clean Master Lists
Pairing TOCOL with modern dynamic array functions produces automated deduplicated lists from unstructured departmental matrices:
# Extract all unique project tags from multi-column project plans, sorted alphabetically
=SORT(UNIQUE(TOCOL(Projects!D2:H100, 1)))By eliminating convoluted regex or string manipulation workarounds, TOCOL and TOROW make array restructuring fast, readable, and computationally efficient.