Transforming complex multi-column matrices, matrix rosters, or grid-based tables into a single continuous column or row is one of the most common data wrangling tasks in Google Sheets. Prior to recent formula updates, reshaping two-dimensional tables into a one-dimensional array required cumbersome workarounds involving FLATTEN, SPLIT, and nested JOIN formulas that struggled with empty cells and error handling. With the introduction of the TOCOL and TOROW functions, Google Sheets users can now flatten multi-dimensional tables into a single column or row with dedicated parameters for ignoring blanks, skipping errors, and controlling scan direction.
Understanding the Syntax of TOCOL and TOROW
Both functions share nearly identical signatures and logic, differing only in whether their output array is vertical or horizontal.
The TOCOL Function Syntax
=TOCOL(array, [ignore], [scan_by_column])The TOROW Function Syntax
=TOROW(array, [ignore], [scan_by_column])Argument Explanations
- array: The source range or two-dimensional array of cells to be transformed (e.g.,
A2:D10). - [ignore] (Optional): A numeric flag that controls which cell types are omitted from the output:
0(Default): Keep all values (including blanks and error codes).1: Ignore all empty/blank cells.2: Ignore cells containing errors (such as#N/A,#VALUE!, or#DIV/0!).3: Ignore both blank cells and errors simultaneously.
- [scan_by_column] (Optional): A boolean flag indicating how the matrix is traversed:
FALSE(Default): Scans by row (reads left to right across row 1, then row 2, and so forth).TRUE: Scans by column (reads top to bottom down column A, then column B, and so forth).
Transforming a Multi-Column Grid into a Single Column
Suppose you have staff member names distributed across columns A, B, and C from rows 2 to 6, with sporadic blank cells where attendance was not logged. To consolidate all names into a single vertical roster without blank spaces, enter the following formula:
=TOCOL(A2:C6, 1)By specifying 1 as the ignore argument, TOCOL automatically strips out all empty gaps and outputs a clean, continuous list of names starting at the formula cell.
Flattening Data While Skipping Errors and Blanks
When working with lookup formulas that return occasional #N/A errors alongside blank cells across a financial grid, pass 3 to eliminate both issues simultaneously:
=TOCOL(B2:F20, 3)This ensures your consolidated list contains strictly valid entries, making it immediately ready for pivot tables, summary calculations, or validation dropdowns.
Controlling Scan Direction (Row vs Column Priority)
The third argument controls how values are sequenced:
- Reading Across Rows:
=TOCOL(A1:C3, 1, FALSE)Values will be placed in the order: A1, B1, C1, A2, B2, C2, A3, B3, C3.
- Reading Down Columns:
=TOCOL(A1:C3, 1, TRUE)Values will be placed in the order: A1, A2, A3, B1, B2, B3, C1, C2, C3.
Flattening Data Horizontally with TOROW
If you need to prepare categorical data for header rows or horizontal timeline summaries, use TOROW:
=TOROW(A2:D10, 1)This spills your multi-row table horizontally across adjacent columns in a single row.
Combining TOCOL with UNIQUE and SORT
Because TOCOL outputs a clean dynamic array, it pairs smoothly with other array manipulation functions:
=SORT(UNIQUE(TOCOL(A2:E50, 3)))In one concise formula, this extracts every value from a 50-row by 5-column range, removes all blank cells and calculation errors, deduplicates repeating entries, and sorts the final list alphabetically.
Adopting TOCOL and TOROW in your Google Sheets formulas modernises your data pipelines, eliminating convoluted legacy workarounds with clean, readable, and highly performant syntax.