Convert column / row to range in Excel: WRAPCOLS & WRAPROWS functions

Excel WRAPCOLS function

The WRAPCOLS function in Excel transforms a row or column of values into a two-dimensional array based on the specified number of values per row.

The syntax has the following arguments:

Where:

• vector (required) - the source one-dimensional array or range.
• wrap_count (required) - the max number of values per column.
• pad_with (optional) - the value to pad with the last column if there are insufficient items to fill it. If omitted, the missing values will be padded with #N/A (default).

For example, to change the range B5:B24 to a 2-dimensional array with 5 values per column, the formula is:

=WRAPROWS(B5:B24, 5)

You enter the formula in any single cell and it automatically spills into as many cells as needed. In the WRAPCOLS output, the values are arranged vertically, from top to bottom, based on the wrap_count value. After the count has been reached, a new column is started.

Excel WRAPROWS function

The WRAPROWS function in Excel converts a row or column of values into a two-dimensional array based on the number of values per row that you specify.

The syntax is as follows:

Where:

• vector (required) - the source one-dimensional array or range.
• wrap_count (required) - the max number of values per row.
• pad_with (optional) - the value to pad with the last row if there are insufficient items to fill it. The default is #N/A.

For example, to transform the range B5:B24 into a 2D array having 5 values in each row, the formula is:

=WRAPROWS(B5:B24, 5)

You enter the formula in the upper-left cell of the spill range, and it populates all other cells automatically. The WRAPROWS function arranges the values horizontally, from left to right, based on the wrap_count value. After reaching the count, it starts a new row.

How to convert column / row to range in Excel - examples

Now that you've got a grasp of the basic usage, let's take a closer look at a few more specific cases.

WRAPCOLS alternative for Excel 365 - 2010

In older Excel versions where the WRAPCOLS function is not supported, you can build your own formula to wrap the values from a one-dimensional array into columns. This can be done by using 5 different functions together.

WRAPCOLS alternative to convert a row into 2D range:

WRAPCOLS alternative to convert a column into 2D range:

Where n is the maximum number of values per column.

In the image below, we use the following formula to turn a one-row range (D4:J4) into a three-row array.

=IFERROR(IF(ROW(A1)>3, "", INDEX($D$4:$J$4, , ROW(A1) + (COLUMN(A1)-1)*3)), "")

And this formula changes a one-column range (B4:B20) into a five-row array:

=IFERROR(IF(ROW(A1)>5, "", INDEX($B$4:$B$20, ROW(A1) + (COLUMN(A1)-1)*5)), "")

The above solutions emulate the analogous WRAPCOLS formulas and produce the same results:

=WRAPCOLS(D4:J4, 3, "")

and

=WRAPCOLS(B4:B20, 5, "")

Please keep in mind that unlike the dynamic array WRAPCOLS function, the traditional formulas follow the one-formula-one-cell approach. So, our first formula is entered in D8 and copied 3 rows down and 3 columns to the right. The second formula is entered in D14 and copied 5 rows down and 4 columns to the right.

How these formulas work

At the heart of both formulas, we use the INDEX function that returns a value from the supplied array based on a row and column number:

As we are dealing with one-row array, we can omit the row_num argument, so it defaults to 1. The trick is to have col_num calculated automatically for each cell where the formula is copied. And here's how we do this:

ROW(A1)+(COLUMN(A1)-1)*3)

The ROW function returns the row number of the A1 reference, which is 1.

The COLUMN function returns the column number of the A1 reference, which is also 1. Subtracting 1 turns it into zero. And multiplying 0 by 3 gives 0.

Then, you add up 1 returned by ROW and 0 returned by COLUMN and get 1 as a result.

This way, the INDEX formula in the upper-left cell of the destination range (D8) undergoes this transformation:

INDEX($D$4:$J$4, ,ROW(A1) + (COLUMN(A1)-1)*3))

changes to

INDEX($D$4:$J$4, ,1)

and returns the value from the 1st column of the specified array, which is "Apples" in D4.

When the formula is copied to cell D9, the relative cell references change based on a relative position of rows and columns while the absolute range reference remains unchanged:

INDEX($D$4:$J$4,, ROW(A2)+(COLUMN(A2)-1)*3))

turns into:

INDEX($D$4:$J$4,, 2+(1-1)*3))

becomes:

INDEX($D$4:$J$4,, 2))

and returns the value from the 2nd column of the specified array, which is "Apricots" in E4.

The IF function checks the row number and if it's greater than the number of rows you specified (3 in our case) returns an empty string (""), otherwise the result of the INDEX function:

IF(ROW(A1)>3, "", INDEX(…))

Finally, the IFERROR function fixes a #REF! error that occurs when the formula is copied to more cells than really needed.

The second formula that converts a column into 2D range works with the same logic. The difference is that you use the ROW + COLUMN combination to figure out the row_num argument for INDEX. The col_num parameter is not needed in this case since there is just one column in the source array.

WRAPROWS alternative for Excel 365 - 2010

To wrap the values from a one-dimensional array into rows in Excel 2019 and earlier, you can use the following alternatives to the WRAPROWS function.

Transform a row into 2D range:

Change a column to 2D range:

Where n is the maximum number of values per row.

In our sample data set, we use the following formula to convert a one-row range (D4:J4) into a three-column range. The formula lands in cell D8, and then is copied across 3 columns and 3 rows.

=IFERROR(IF(COLUMN(A1)>3, "", INDEX($D$4:$J$4, , COLUMN(A1)+(ROW(A1)-1)*3)), "")

To reshape a 1-column range (B4:B20) into a 5-column range, enter the below formula in D14 and drag it across 5 columns and 4 rows.

=IFERROR(IF(COLUMN(A1)>5, "", INDEX($B$4:$B$20, COLUMN(A1)+(ROW(A1)-1)*5)), "")

In Excel 365, the same results can be achieved with the equivalent WRAPCOLS formulas:

=WRAPROWS(D4:J4, 3, "")

and

=WRAPROWS(B4:B20, 5, "")

How these formulas work

Essentially, these formulas work like in the previous example. The difference is in how you determine the row_num and col_num coordinates for the INDEX function:

INDEX($D$4:$J$4,, COLUMN(A1)+(ROW(A1)-1)*3))

To get the column number for the upper left cell in the destination range (D8), you use this expression:

COLUMN(A1)+(ROW(A1)-1)*3)

that changes to:

1+(1-1)*3

and gives 1.

As a result, the below formula returns the value from the first column of the specified array, which is "Apples":

INDEX($D$4:$J$4,, 1)

So far, the result is the same as in the previous example. But let's see what happens in other cells…

In cell D9, the relative cell references change as follows:

INDEX($D$4:$J$4,, COLUMN(A2)+(ROW(A2)-1)*3))

So, the formula transforms into:

INDEX($D$4:$J$4,, 1+(2-1)*3))

becomes:

INDEX($D$4:$J$4,, 4))

and returns the value from the 4th column of the specified array, which is "Cherries" in G4.

The IF function checks the column number and if it's greater than the number of columns you specified, returns an empty string (""), otherwise the result of the INDEX function:

IF(COLUMN(A1)>3, "", INDEX(…))

As a finishing touch, IFERROR prevents #REF! errors from appearing in "extra" cells if you copy the formula to more cells than actually needed.

WRAPCOLS or WRAPROWS function not working

If the "wrap" functions are not available in your Excel or result in an error, it's most likely to be one of below reasons.

Practice workbook for download

WRAPCOLS and WRAPROWS functions - examples (.xlsx file)