Practice free

TOROW function

TOROW flattens an array into a single row, the sideways version of TOCOL. You can choose to leave out blanks or errors, and the default scan order is across each row.

Example

Read it as a sentence: lay out the values in F1:F5 in one row, ignoring blanks, and show the first value, which gives 123.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

array (required)
Array or range to flatten.
ignore (optional)
0 keep all (default), 1 skip blanks, 2 skip errors, 3 skip both.
scan_by_column (optional)
TRUE to read down columns first.

Returns: A single row of the values.

One TOROW formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=TOROW({1,2;3,4}) 1 By row
=TOROW({1,2;3,4},,TRUE) 1 By column
=TOROW(Data!F1:F5,1) 123 Ignore blanks
=TOROW(Data!F1:F5,3) 123 Ignore both
=TOROW({1,2},-1) #VALUE! Bad ignore value

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Default scan order is across each row.
  • Set ignore to remove blanks or errors.

Where it works

Excel
TOROW arrived in Excel 2024. Older versions do not have it. 365, Excel for the web and Excel 2024; not in older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .