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.A5:G-Both cells must have a valid cell identifier: a letter and a number.Ī(Input_Excel_File, Output_Table, ) Name.Z3:B5-The second cell cannot be to the right (have a larger column letter) of the first cell.A20:C10-The first cell cannot be lower (have a larger row number) than the second cell.The following are examples of invalid cell ranges: E200:ALM20000-The values in columns E through ALM (1000th column), from row 200 through 20000.D5:X5-The values in columns D through X, for row 5.B3:B40-The values in column B, from rows 3 through 40.A2:C10-The values in columns A through C, from row 2 through 10.The following are examples of valid cell ranges: For example, if the cell range specified is B2:D10, the field names will be based on the values in cells B1, C1, and D1. The output field names are derived from cell values in row 1, regardless of the rows specified in the cell range. Cell ranges are inclusive, so a range of A2:C10 will include all values in columns A through C and all values in rows 2 through 10. For example, the cell B9 is the intersection of column B and row 9.Ī cell range defines a rectangle using the upper left cell and lower right cell, separated by a colon (:).

Each cell has an address based on its column and row. Columns are identified by letters (A, B, C, D), and rows are identified by numbers (1, 2, 3, 4). A cell is the intersection of a row and a column.

Excel row to column conversion