Skip to content

TOROW Function

The TOROW function in Excel is used to convert an array or range into a single row. It takes a multi-dimensional array and returns all values arranged in a single horizontal row. This is particularly useful for flattening data, preparing lists for other functions, or transforming tabular data into a row format.

Key Features of TOROW:

  • Converts any array or range into a single row.
  • Allows control over the scan order (by row or by column).
  • Can ignore blanks, errors, or both during conversion.
  • Works seamlessly with other dynamic array functions.
  • Returns a spilled array that resizes automatically.

Syntax:

TOROW(array, [ignore], [scan_by_column])
  • array: The array or range to convert to a single row.
  • ignore (optional): Specifies what to ignore. Default is 0.
  • 0 - Keep all values (default)
  • 1 - Ignore blanks
  • 2 - Ignore errors
  • 3 - Ignore blanks and errors
  • scan_by_column (optional): Specifies how to read the array. Default is FALSE.
  • FALSE - Scan the array by row (left to right, then down). This is the default.
  • TRUE - Scan the array by column (top to bottom, then right).

How TOROW Works:

  1. Takes the input array and reads each value in the specified order.
  2. By default, scans by row (left to right across each row, then moves to the next row).
  3. If scan_by_column is TRUE, scans by column (top to bottom in each column, then moves to the next column).
  4. Optionally filters out blanks and/or errors based on the ignore parameter.
  5. Returns all values arranged into a single horizontal row.

Examples:

1. Basic Conversion (Scan by Row):

Given a range A1:C2 with data: 

1   2   3
4   5   6


=TOROW(A1:C2)

Result: 

1   2   3   4   5   6

(Values are read left to right, row by row)

2. Scan by Column:

Using the same range A1:C2:

=TOROW(A1:C2, 0, TRUE)

Result: 

1   4   2   5   3   6

(Values are read top to bottom, column by column)

3. Ignore Blanks:

Given a range with blanks: 

A   B
    C
D


=TOROW(A1:B3, 1)

Result: 

A   B   C   D

(Blank cells are removed from the result)

4. Ignore Errors:

Given a range with errors: 

1       2
#N/A    3
4       #VALUE!


=TOROW(A1:B3, 2)

Result: 

1   2   3   4

(Error values are excluded from the result)

5. Ignore Both Blanks and Errors:

=TOROW(A1:C3, 3)

Result: 

Returns only non-blank, non-error values in a single row

6. Convert Array Constant:

=TOROW({1,2,3;4,5,6;7,8,9})

Result: 

1   2   3   4   5   6   7   8   9

7. Combine with UNIQUE:

=UNIQUE(TOROW(A1:C10))

Result: 

Returns unique values from the entire range as a single row

8. Flatten for TEXTJOIN:

=TEXTJOIN(", ", TRUE, TOROW(A1:C3))

Result: 

Joins all values from the range into a comma-separated string

Notes:

  • TOROW is available in Excel 365 and Excel 2021 or later versions.
  • The function returns a dynamic spilled array.
  • When ignore is set to filter values, the resulting array may be smaller than the original.
  • If the input array is already a single row, TOROW returns it unchanged (unless filtering is applied).
  • Empty results return a #CALC! error if all values are filtered out.

Applications:

  • Data Flattening: Convert multi-row data into a single list for analysis.
  • List Preparation: Prepare data for functions that require single-row input.
  • Data Cleaning: Remove blanks or errors while reshaping data.
  • Dynamic Reporting: Combine data from multiple rows into a unified row format.
  • Array Manipulation: Transform array shapes for use with other dynamic array functions.
  • TOCOL: Converts an array to a single column (vertical equivalent of TOROW).
  • WRAPCOLS: Wraps a row or column into multiple columns.
  • WRAPROWS: Wraps a row or column into multiple rows.
  • VSTACK: Vertically stacks arrays on top of each other.
  • HSTACK: Horizontally stacks arrays side by side.

Tip: Use TOROW combined with UNIQUE and SORT to quickly generate a sorted list of unique values from a multi-row range: =SORT(UNIQUE(TOROW(A1:C10, 1))).