|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

TOROW

TOROW

  • Last updated on
Related to spreadsheet functions

Description

Use this function to an array as a single row.

 

Syntax

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

 

Inputs

This function accepts the following arguments:

Name Required Description Valid Input
array Yes The range or array to return as a column. A cell range, a formula that returns a cell range, or a reference to a cell containing a cell range.
ignore No Whether to ignore certain types of values. By default, no values are ignored.

One of the following values:

  • 0 = Keep all values (default)
  • 1 = Ignore blanks
  • 2 = Ignore errors
  • 3 = Ignore blanks and errors
scan_by_column No Whether to scan the array by column or by row. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.

If no value is provided, this is ignored.
To use this value, a value for ignore must be provided. A space counts as "0".

  • FALSE or 0 = Scan by row (default)
  • TRUE or 1 = Scan by column

 

Examples

Sample data

A B C D E
1 Name Age City Salary Position
2 John 35 New York 75000 Sales Lead
3 Alice 28 Chicago 65000 CSM
4 Mary 49 Ames 82500

Sample formula

Use case Formula Explanation and Result
Return the supplied array as a single row using the defaults. =TOROW(A2:E4)

This formula returns all the values in the array A2:D4 collected by row (the default setting).

For this data set, this formula returns the following:

John 35 New York 75000 Sales Lead Alice 28 Chicago
  65000 CSM Mary 49 Ames 82500 0

For the purposes of illustration, the row has been split into two. 
In a spreadsheet, this would be one continuous row.
Return the supplied array as a single row collected by column, but ignoring blank cells. =TOROW(A2:E4,1)

This formula returns all the values in the array A2:D4 collected by rows, and ignoring blanks (1).

For this data set, this formula returns the following: (note that the last row contains the contents of D4, and that E4, which is blank, is not included)

John 35 New York 75000 Sales Lead Alice 28
Chicago 65000 CSM Mary 49 Ames 82500

For the purposes of illustration, the row has been split into two. 
In a spreadsheet, this would be one continuous row.
Return the supplied array as a single row collected by column, but include blank cells. =TOROW(A2:E4, ,1)

This formula returns all the values in the array A2:D4 collected by columns. As no value is provided for ignore, blank cells are included as zeros.

For this data set, this formula returns the following: (note that the last row contains the contents of D4, and that E4, which is blank, is not included.)

John Alice Mary 35 28 49 New York Chicago
  Ames 75000 65000 82500 Sales Lead CSM 0

For the purposes of illustration, the row has been split into two. 
In a spreadsheet, this would be one continuous row.
Return a list of the names of all employees whose salary is greater than $70,000. =TOROW(FILTER(A2:A4, D2:D470000))

In this formula:

  • FILTER(A2:A4, D2:D470000): FILTER looks at the salary column (D2:D4) and checks for values greater than 70,000. It then returns a new, clean array containing only the corresponding names from the A2:A4 range. The non-matching cells are not included at all.

  • TOROW(...,1) takes the results of the FILTER function and converts that into a single row.
    The final output is a single row with the names of the employees who meet the salary criteria; in this case, John and Mary.

    John Mary

 

Notes

  • Empty cells are returned as "0". (See the first sample formula for an example.)
  • ignore only discards cell that are actually empty or actual errors. FALSE or TRUE values are treated as text, and so are included.

 

Tips

TOROW can be combined with other functions, including:

  • FILTER: To select specific columns from filtered data.
  • SORT: To rearrange columns.
  • UNIQUE: To extract unique values from selected columns.
  • SEQUENCE: To create dynamic column selections.
  • INDEX: For advanced column referencing.
  • MATCH: For dynamic column selection based on criteria.
  • TRANSPOSE: To reorient selected columns.
  • CHOOSEROWS: For more complex data selection.

 

Related functions

Here are the other array formula functions supported in Workiva spreadsheets. 

Was this article helpful?
0 out of 0 found this helpful
DescriptionUse this function to an array as a single row. SyntaxTOROW (array, [ignore], [scan_by_column]) InputsThis ...