If you are dealing with several non-contiguous ranges, then you can first combine the ranges vertically into a single array with the help of the VSTACK function, and then use TOCOL to transform the combined array into a column.Īssuming the first range is A2:C4 and the second range is A8:C9, the formula takes this form: Notice that, in both cases, the returned arrays are the same size, but the values are arranged in a different order.

To process values by column, set this argument to TRUE or 1. With the default scan_by_column argument (FALSE or omitted), the TOCOL function scans the array horizontally by row. =TOCOL(A2:C5, 3) Scan array horizontally or vertically To exclude both, blanks and errors, use 3 for the ignore argument: To ignore errors, set the 2nd argument to 2: In the resulting array, empty cells are represented by zeros, which may be quite confusing, especially if the original array has 0 values. Transform array to column ignoring blanks and errorsĪs you may have noticed in the previous example, the default TOCOL formula keeps all the values from the source array, including blank cells and errors. To gain more understanding of the possibilities of the TOCOL function and what tasks it can cover, let's take a look at some formula examples. How to use TOCOL function in Excel - formula examples The result is placed in cell E2, from which it spills into the below cells.

Please notice the semicolon-separated rows and comma-delimited columns: Technically, the range A2:C5 is first converted into a two-dimensional array. In terms of Excel, the result is called a spill range. The formula is entered only in one cell (E2 in this example) and spills into the below cells automatically. For example, to place a two-dimensional array consisting of 3 columns and 4 rows into a single column, the formula is: The TOCOL formula in its simplest form requires just one argument - array. TOCOL is a new function, which is supported in Excel for Microsoft 365 (for Windows and Mac) and Excel for the web.īasic TOCOL formula to transform range into column change rows to columns, utilize the TRANSPOSE function.

To transpose an array from horizontal to vertical or vice versa, i.e.

To do the opposite column-to-array transformation, use either the WRAPCOLS function to wrap by column or the WRAPROWS function to wrap by row.

To convert an array into a single row, use the TOROW function.

Excel row to column conversion