TOROW function

The TOROW function in Excel is used to convert a 2D array or range of cells into a single row. This function is particularly useful when you need to flatten a table-like dataset into a horizontal format.

Syntax

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

Parameters

1. array: The range or array that you want to convert into a row.
2. ignore (optional): Specifies which values to ignore. It can be:
   • 1 to ignore blank cells,
   • 2 to ignore errors,
   • 3 to ignore both blanks and errors.
3. scan_by_column (optional): Specifies whether the array should be read by columns (TRUE or 1) or by rows (FALSE or 0). The default is FALSE, meaning the function will scan by rows.

How It Works

• The array parameter is the source data, typically a 2D range (like A1:B3) that you want to flatten into a single row.
• The ignore parameter allows you to remove blank cells, error values, or both from the resulting row.
• The scan_by_column parameter determines whether the function processes data by rows (default) or by columns.

Examples

Example 1: Convert 2D Array to Row (Basic Usage)

Suppose you have the following dataset in the range A1:C2:

If you use the TOROW function like this:

=TOROW(A1:C2)

The output will be:

| A1 | B1 | C1 | A2 | B2 | C2 |

Example 2: Ignore Blank Cells

If your data contains blank cells:

To flatten the data and ignore blanks, use:

=TOROW(A1:C2, 1)

This will return:

| A1 | B1 | C1 | A2 | C2 |

Example 3: Convert and Scan by Column

If you want the data to be read column by column instead of row by row, set scan_by_column to TRUE:

=TOROW(A1:C2, , TRUE)

The output will now be:

| A1 | A2 | B1 | B2 | C1 | C2 |

Example 4: Ignore Errors

If your data contains errors (such as #DIV/0! or #N/A), and you want to remove them, you can use the ignore option. For example, if cell A2 contains an error:

To ignore errors and flatten the data into a row:

=TOROW(A1:C2, 2)

This will return:

| A1 | B1 | C1 | B2 | C2 |

Key Points

• TOROW simplifies transforming 2D ranges into a single row, useful for data analysis or when preparing lists.
• You can choose to ignore blank cells, errors, or both using the ignore parameter.
• The function gives flexibility in how data is processed: either row-by-row or column-by-column, using the scan_by_column parameter.

Summary

The TOROW function in Excel is a powerful tool for flattening 2D arrays into rows, with options to handle blanks, errors, and to control how data is read (by rows or columns). It is ideal for consolidating data into a continuous row for easier manipulation or presentation.