How to Use the TOROW and TOCOL Functions in Google Sheets to Flatten Multi-Column Arrays

In standard data analysis, tabular datasets frequently arrive arranged across multi-column, multi-row matrices—such as monthly revenue tables, multi-department rosters, or coordinate grids. Previously, flattening a two-dimensional grid into a single contiguous linear vector required awkward combinations of FLATTEN, SPLIT, and TRANSPOSE formulas, or custom Google Apps Script loops. Google Sheets provides two purpose-built modern array transformation functions: TOCOL (which transforms a 2D range into a single vertical column) and TOROW (which transforms a 2D range into a single horizontal row).

Understanding the Syntax and Parameters of TOCOL and TOROW

The TOCOL function scans a two-dimensional range or array and spills the contents into a single column. Its counterpart, TOROW, outputs the values across a single row.

The syntax for TOCOL is defined as:

=TOCOL(array, [ignore], [scan_by_column])

The syntax for TOROW is identical in argument structure:

=TOROW(array, [ignore], [scan_by_column])

The parameters operate as follows:

• array (Required): The input range or array expression to transform.
• ignore (Optional): An integer specifying which cell values to exclude from the result:
  • 0 (Default): Keep all values (preserves blanks and errors).
  • 1: Ignore blank cells.
  • 2: Ignore formula errors (e.g. #N/A, #VALUE!).
  • 3: Ignore both blanks and errors simultaneously.
• scan_by_column (Optional): A boolean value determining traversal direction:
  • FALSE (Default): Scans by row (reads horizontally left-to-right across row 1, then row 2, etc.).
  • TRUE: Scans by column (reads vertically top-to-bottom down column A, then column B, etc.).

Transforming a Multi-Column Staff Roster into a Clean Single Column

Suppose you have shift schedules where team members are listed across columns A2:D10, with sporadic empty cells where shifts were unfilled. To extract a consolidated list of active staff members:

=TOCOL(A2:D10, 1)

Setting the second argument to 1 instructs Google Sheets to filter out all blank cells automatically, returning a clean, gap-free vertical list.

Scanning Matrices by Column Instead of by Row

When working with time-series data arranged with categories on rows and sequential months across columns, reading down each column preserves chronological order across metrics:

=TOCOL(B2:M20, 1, TRUE)

Because scan_by_column is set to TRUE, Google Sheets finishes processing the entirety of column B before proceeding to column C.

Flattening Arrays Horizontally with TOROW for Header Generation

To dynamically convert a vertical list of survey questions or category tags into a horizontal header row across the top of a new reporting sheet:

=TOROW(A2:A50, 3)

Setting the ignore flag to 3 ensures that any erroneous entries or empty spacer rows are automatically stripped, generating an uninterrupted header sequence.

Combining TOCOL with UNIQUE and SORT for De-Duplication

You can seamlessly nest TOCOL inside modern array formulas to extract sorted, distinct values from disorganized multi-column data blocks:

=SORT(UNIQUE(TOCOL(A2:F100, 3)))

This single formula performs a four-step pipeline in a single calculation thread: it flattens the A2:F100 block into a single column, strips all blanks and error cells, removes duplicate values, and sorts the remaining items alphabetically.

Mastering TOCOL and TOROW eliminates messy workaround formulas and empowers Google Sheets users to reshape complex tabular data structures with concise elegance.