This tutorial explains how to use the TOROW function in Google Sheets, along with a real-life application of this function.

The purpose of the TOROW function is to transform a range of cells into a single row.

This function provides a straightforward alternative to using combinations of FLATTEN, TRANSPOSE, and FILTER to convert a table or array into a single row—methods that can be cumbersome for many new Google Sheets users.

As such, the TOROW function offers a simpler solution for data manipulation.

TOROW Function Syntax and Arguments

Syntax: TOROW(array_or_range, [ignore], [scan_by_column])

Arguments:

• array_or_range: The array or cell range you want to return as a row.
• ignore: Specifies whether to ignore blanks (1), errors (2), or both (3). By default (0), no values are ignored. You can choose from 0, 1, 2, or 3 accordingly.
• scan_by_column: Set to TRUE to scan by column or FALSE (default) to scan by row.

TOROW Function Examples in Google Sheets

Consider the values in the range B2:D6, which I’ve generated using the following SEQUENCE formula in B2: =SEQUENCE(5,3).

In cell G2, I used a TOROW formula to scan these values by column:

=TOROW(B2:D6, 0, TRUE)

In cell G5, the formula scans the values by row:

=TOROW(B2:D6, 0, FALSE)

Understanding the difference between these formulas is key to using the TOROW function effectively. In both cases, the ignore parameter is set to 0, meaning that values are returned as they are.

Alternative Solutions

You can ignore the following formulas, as they illustrate the complexity of previous methods:

G2 (flatten by column):

=TRANSPOSE(FLATTEN(TRANSPOSE(B2:D6)))

G5 (flatten by row):

=TRANSPOSE(FLATTEN(B2:D6))

One drawback of these alternatives is that you often need to use additional functions like FILTER and IFERROR to ignore blanks, errors, or both.

TOROW Real-life Use: Inserting Blank Columns Between Data

Sometimes, manipulating values in a single row or column format can simplify data management in Google Sheets. The TOROW function can transform a range of cells into a single row. After manipulation, you can use WRAPROWS to convert the single rows of values back into a range of cells.

In the example below, you can learn how to insert blank columns between data using a combination of the TOROW and WRAPROWS functions in Google Sheets.

Formula in Cell D11:

=LET(
   data, D3:K7,
   IFERROR(
      WRAPROWS(
         TOROW(
            VSTACK(TOROW(data, 0, FALSE), INDEX(TOROW(data / 0, 0, FALSE))),
            0, TRUE
         ),
        COLUMNS(data) * 2
      )
   )
)

How This Formula Works

1. Flatten the data: The first step is to flatten the data into a row by scanning the values row-wise (set scan_by_column to FALSE).
   TOROW(data, 0, FALSE)
   Output: {"Name,Sun,Mon,Tue,Wed,Thu,Fri,Sat,B,P,P,P,P,P,P,P,C,P,P,P,P,P,D,P,P,P,P,P,P,P,E,P,P,P,P,P,P"}
2. Generate error cells: The next step is to create error cells that match the number of cells in the data range.
   INDEX(TOROW(data / 0, 0, FALSE))
   Output: {"VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #DIV/0! #DIV/0! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #DIV/0! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!"}
3. Vertically stack the outputs: The outputs from step #1 and step #2 are combined using the VSTACK function:
   VSTACK(..., ...)
4. Flatten the new array: Use another TOROW to flatten this new array into a row, this time scanning by column (set scan_by_column to TRUE).
   TOROW(VSTACK(...), 0, TRUE)
   This will place one error value after every value.
5. Wrap the output into an array: Finally, use WRAPROWS to create an array.
   • Syntax: WRAPROWS(range, wrap_count, [pad_with])
   • Note that wrap_count is calculated as the number of columns in the data multiplied by 2.

This method allows you to insert blank columns between data using the WRAPROWS and TOROW functions in Google Sheets.

The REDUCE Connection of the TOROW Function

You can use TOROW(, 1) or TOROW(, 3) as part of a REDUCE function, particularly as the initial value of the accumulator, like this:

=REDUCE(TOROW(, 1)…

Instead of specifying:

=REDUCE("", …

Using TOROW(, 1) ensures that a truly blank cell is used. This is beneficial because when you horizontally stack the accumulator, it keeps the results tidy. If you use an empty string "" instead, it results in an empty column at the front, with an empty cell in the first row and #N/A in the subsequent rows.

Example: Dynamically Combine Multiple Sheets Horizontally in Google Sheets

Resources

• TOCOL Function in Google Sheets (How To)
• How to Use the FLATTEN Function in Google Sheets
• How to Partially Flatten a Multi-Column Array in Google Sheets
• How to Flatten Every Other Column in Google Sheets