How to use the WRAPCOLS function

What is the WRAPCOLS function?

The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of values per column.

Which Excel version has the WRAPCOLS function?

The WRAPCOLS function is available to Excel 365 users

Which category contains the WRAPCOLS function?

The WRAPCOLS function is in the "Array manipulation" category.

1. Syntax

WRAPCOLS(vector, wrap_count, [pad_with])

Back to top

2. Arguments

vector	Required. The source cell range or array.
wrap_count	Required. The number of values per column, the last row may be smaller.
[pad_with]	Optional. Value to pad with if there is an insufficient number of values in the last row. The default value is #N/A!

Back to top

3. Example

The picture above shows how the WRAPCOLS function rearranges values in a row to create a 2D range based on the number of values per column specified in the second argument: wrap_count.

Dynamic array formula in cell B8:

The source range is B2:M2 and contains 12 values. The formula in cell B8 creates an array based on 4 values per column, this returns an array containing 4 rows and 3 columns.

For example, the output array is 4 rows and 4 columns if the source range contains 15 values. The size of the array is determined by the number of cells in the source range the wrap_count number.

3.1 Explaining formula

Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.

Step 1 - WRAPCOLS function

WRAPCOLS(vector, wrap_count, [pad_with])

Step 2 - Populate arguments

vector - B2:M2
wrap_count - 4

Step 3 - Evaluate function

WRAPCOLS(B2:M2, 4)

becomes

WRAPCOLS({89, 68, 19, 37, 27, 84, 92, 63, 26, 98, 62, 45})

and returns

{89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}.

Back to top

4. Example - pad with

The WARPACOLS function builds an array based on the number of cells in the source range and the number specified in the wrap_cols argument. This may produce more values than the source range contains.

The WRAPCOLS function returns #N/A errors in empty containers in its default state. The #N/A errors may show up in empty cells on the last column, use the [pad_with] argument to populate empty cells and get rid of the #N/A error.

The following formula populates an array containing some empty cells, however, empty cells are padded with "A" in this example. This is not the case with the formula shown in the image above. Cells D8 and D9 contain #N/A errors.

Formula in cell B6:

The image below demonstrates how to populate empty cells with the text string "A".

Back to top

5. WRAPCOLS function alternative

The image above shows a formula that works in earlier Excel versions. This formula wraps the values in cell range B2:K2 to fit four values per row. Change the bolded number in the formula below to adjust the number of columns.

Formula in cell B6:

Change bolded numbers to the number of rows you want in the 2D range.

5.1 Explaining formula in cell B6

Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.

Step 1 - Calculate the number of columns in a given cell range

The COLUMN function returns the column number of the top-left cell of a cell reference.

COLUMN(reference)

COLUMN(A1) - 1

becomes

1-1 equals 0 (zero).

Step 2 - Calculate column number of relative cell reference

The ROW function returns the row number of a cell reference.

ROW(reference)

ROW(A1)

returns 1.

Step 3 - Multiply

The asterisk lets you multiply numbers in an Excel formula.

The parentheses control the order of calculation, we want to subtract before we multiply.

(COLUMN(A1)-1)*4

becomes

0*4 equals 0 (zero).

Step 4 - Calculate column number

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array, [row_num], [column_num], [area_num])

INDEX($B$2:$K$2,,ROW(A1)+(COLUMN(A1)-1)*4)

becomes

INDEX($B$2:$K$2,,1)

and returns 89 in cell B6.

Step 5 - Return #N/A for row numbers above condition

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(ROW(A1)>4,NA(),INDEX($B$2:$K$2,,ROW(A1)+(COLUMN(A1)-1)*4))

becomes

IF(ROW(A1)>4,NA(),89)

becomes

IF(FALSE, NA(), 89)

and returns 89.

Back to top

6. Example - values in random order

The image above demonstrates a formula that rearranges values in random order with four columns. The formula gets the values from B2:M2 and puts them in random order. Then the formula reshapes the array to 4 rows and as many columns as needed.

Dynamic array formula in cell B6:

This formula is great for putting given values in random order and to a given array size. The formula returns a new order every time the worksheet recalculates or Function key F9 is pressed.

The formula returns a #SPILL! error if the destination cells contain at least one value. Remove the value and the formula is able to display all values.

6.1 Explaining formula in cell B8

Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.

Step 1 - Calculate the number of cells in cell ref

The COLUMNS function returns the number of columns in a given cell range.

COLUMNS(array)

COLUMNS(B2:M2)

returns 12.

Step 2 - Create random decimal values

The RANDARRAY function returns a table of random numbers across rows and columns.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(, COLUMNS(B2:M2))

becomes

RANDARRAY(, 12)

and returns

{0.732441255476154, 0.953587479053817, ... , 0.0191675082920368}

Step 3 - Sort values based on random decimals

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(B2:M2, RANDARRAY(, COLUMNS(B2:M2)))

becomes

SORTBY(B2:M2, {0.732441255476154, 0.953587479053817, ... , 0.0191675082920368})

becomes

SORTBY({89, 68, 19, 37, 27, 84, 92, 63, 26, 98, 62, 45}, {0.732441255476154, 0.953587479053817, ... , 0.0191675082920368})

and returns

{98, 68, 45;27, 19, 26;62, 92, 89;84, 37, 63}.

Step 5 - Wrap values

WRAPCOLS(SORTBY(B2:M2, RANDARRAY(, COLUMNS(B2:M2))), 4)

becomes

WRAPCOLS({98, 68, 45;27, 19, 26;62, 92, 89;84, 37, 63}, 4)

and returns

{98, 68, 45;
27, 19, 26;
62, 92, 89;
84, 37, 63}

Press F9 to recalculate values, this creates a new random order.

Back to top

7. Example - multiple source ranges

The picture above shows a formula that joins values from multiple non-adjacent source ranges and then rearranges the array to a 2D range based on given rows. In this case 4 rows per column.

Dynamic array formula in cell B6:

This formula makes it really easy to consolidate data from multiple worksheets and show them in an array with given number of rows per column.

The image shows three non-adjacent cell ranges B2:M2, B4:G4, and B6:K6. The output array is shown in cell B6, the Excel 365 dynamic array formula spills values to cells below and to the right as far as needed,

7.1 Explaining formula

Step 1 - Stack values horizontally

The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

HSTACK(array1, [array2],...)

HSTACK(B2:M2, B4:G4, B6:K6)

becomes

HSTACK({89, 68, 19, 37, 27, 84, 92, 63, 26, 98, 62, 45}, {49, 65, 30, 59, 10, 10}, {52, 30, 55, 58, 33, 58, 31, 93, 27, 97})

and returns

{89, 68, 19, 37, 27, 84, 92, 63, 26, 98, 62, 45, 49, 65, 30, 59, 10, 10, 52, 30, 55, 58, 33, 58, 31, 93, 27, 97}.

Step 2 - Wrap values to rows

WRAPCOLS(HSTACK(B2:M2, B4:G4, B6:K6), 4)

becomes

WRAPCOLS({89, 68, 19, 37, 27, 84, 92, 63, 26, 98, 62, 45, 49, 65, 30, 59, 10, 10, 52, 30, 55, 58, 33, 58, 31, 93, 27, 97}, 4)

and returns

{89, 27, 26, 49, 10, 55, 31;
68, 84, 98, 65, 10, 58, 93;
19, 92, 62, 30, 52, 33, 27;
37, 63, 45, 59, 30, 58, 97}

Back to top

Useful resources

How to Use the WRAPCOLS Function in Excel
WRAPCOLS function - Microsoft support

'WRAPCOLS' function examples

The following article has a formula that contains the WRAPCOLS function.

Functions in 'Array manipulation' category

The WRAPCOLS function function is one of 11 functions in the 'Array manipulation' category.

Excel function categories

Excel categories

Leave a Comment