To replace the missing values with zero-length strings (blanks), the formula is: To change the default behavior, you can provide a custom value for the optional pad_with argument.įor example, to transform the range B4:B21 into a 2D array with maximum 5 values wide, and pad the last row with dashes if there are not enough data to fill it, use this formula: In case there are insufficient values to fill all the columns/rows of the resulting range, WRAPROWS and WRAPCOLS will return #N/A errors to keep the structure of the 2D array. Pad missing values in the resulting array The image below shows how this looks like: To rearrange the same range by row, so that each row has a maximum of 4 values, the formula is: Whichever function you use, it is the wrap_count argument that determines the max number of values in each column/row.įor example, to transform the range B4:B23 into a 2D array, so that each column has a maximum of 10 values, use this formula: Set the maximum number of values per column or rowĭepending on the structure of your original data, you may find it suitable to be re-arranged into columns (WRAPCOLS) or rows (WRAPROWS). Now that you've got a grasp of the basic usage, let's take a closer look at a few more specific cases. How to convert column / row to range in Excel - examples change a 2D array to a single column or row, use the TOCOL or TOROW function, respectively. Further on in this tutorial, we will discuss the alternative solutions in detail. In earlier versions, you can use traditional more complex formulas to perform column-to-array and row-to-array transformations. After reaching the count, it starts a new row.īoth functions are only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web. The WRAPROWS function arranges the values horizontally, from left to right, based on the wrap_count value.

You enter the formula in the upper-left cell of the spill range, and it populates all other cells automatically. The default is #N/A.įor example, to transform the range B5:B24 into a 2D array having 5 values in each row, the formula is:

pad_with (optional) - the value to pad with the last row if there are insufficient items to fill it.

wrap_count (required) - the max number of values per row.

vector (required) - the source one-dimensional array or range.

Convert columns to rows in excel