From rows to columns…

The TRANSPOSE function has been around for a very long time and allows you, when working with formulas containing arrays, to transpose them from row to column or vice versa. A similar effect can be obtained using the Paste Special option. Since recently, Excel offered to users through Microsoft 365 subscription, has functions TOROW and TOCOL with which we can easily convert rows into columns and columns into rows…

Syntax of the TOROW function:

TOROW(<array>, [<ignore options>], [<search by column>])

The simplest way to use this function is when we only specify an array. In that case the array, which was a column, will turn into a row. The second argument gives us the option to ignore empty cells, errors, or both. The last argument allows us to define the search direction if the first argument is a matrix.

For example. formula:

=TOROW(A3:A6)

transposes a sentence in a row into a column.

The TOCOL function has the syntax:

TOCOL (<string>, [<ignore options>], [<search by column>])

It works similarly to the previous function, except that it converts rows into columns. If we write the formula:

=TOCOL(A1:D1)

the values from the row will be converted into a column.