Excel TOROW function

The TOROW function transforms an array or range to a single row.

Note: This function is only available in Excel for Microsoft 365 on the Insider channel.

TOROW function VS. TOCOL function

The TOROW function can be used to transform an array or a range to a single row, while the TOCOL function can be used to transform an array or a range to a single column.

Syntax

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

Arguments

Return value

It returns a single row.

Example

Here we take the table below as an example to demonstrate the different uses of the TOROW function.

#Example1: Transform a range to a single row and keep all values by default

The range above contains both blanks and error values. To transform the range to a single row and keep all values in the resulting row, please apply the following formula.

Select a cell, such as B11 in this case, enter the following formula and press the Enter key to get the new row.

=TOROW(B6:D8)

Notes:

#Example2: Transform a range to a single row and ignore certain types of values

To ignore all blanks, you just need to specify a number 1 for the ignore argument. The formula is as follows.

=TOROW(B6:D8,1)

To ignore error values, specify the number 2 for the ignore argument. The formula is as follows.

=TOROW(B6:D8,2)

To ignore both the blanks and errors, specify a number 3 for the ignore argument.

=TOROW(B6:D8,3)

#Example3: Transform a range to a single row by scanning values by column

By default, the TOROW function scans the array by rows. If you need to return a single row from the array scanned by column, you need to set the scan_by_column argument to TRUE.

Select a cell, say J6 in this case, enter the following formula and press the Enter key to get the new row.

=TOROW(B6:D8,3,TRUE)