# How to use the WRAPROWS function

The WRAPROWS function rearranges values from a single row to a 2D cell range.

Dynamic array formula in cell B8:

The WRAPROWS function is available to Excel 365 users and is in the "Array manipulation" category.

#### Table of Contents

## 1. WRAPROWS Function Syntax

WRAPROWS(*vector*, *wrap_count*, [*pad_with*])

## 2. WRAPROWS Function Arguments

vector |
Required. The source cell range or array. |

wrap_count |
Required. The number of values per row, 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! |

## 3. WRAPROWS Function example

The picture above shows how the WRAPROWS function rearranges values to fit the requirement specified in the second argument: *wrap_count*.

Dynamic array formula in cell B8:

### 3.1 Explaining formula

#### Step 1 - WRAPROWS function

WRAPROWS(*vector*, *wrap_count*, [*pad_with*])

#### Step 2 - Populate arguments

*vector - *B2:E4

*wrap_count - 4*

#### Step 3 - Evaluate function

WRAPROWS(B2:M2, 4)

becomes

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

and returns

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

## 4. WRAPROWS Function example - pad with

#N/A errors show up in empty cells on the last row, use the [pad_with] argument to populate empty cells.

Formula in cell B6:

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

Formula in cell B6:

## 5. WRAPROWS function alternative

This formula wraps the values in cell range B2:K2 to fit four columns. Change the bolded number in the formula below to adjust the number of columns.

Formula in cell B6:

**4**)

### 5.1 Explaining formula in cell B6

#### 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) returns 1.

#### Step 2 - Calculate column number of relative cell reference

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

ROW(reference)

ROW(A1)-1

becomes

1 - 1 equals 0 (zero).

#### Step 3 - Multiply

The asterisk lets you multiply numbers in an Excel formula.

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

(ROW(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,,COLUMN(A1)+(ROW(A1)-1)***4**)

becomes

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

and returns 89 in cell B6.

## 6. WRAPROWS function - values in random order

The image above shows a formula that rearranges values in random order with four columns.

Dynamic array formula in cell B6:

### 6.1 Explaining formula in cell B8

#### 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})

and returns

#### Step 4 - Create random values

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

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

RANDARRAY(, ROWS(B2:E4)*COLUMNS(B2:E4))

becomes

RANDARRAY(, 12)

and returns 12 random decimal values:

{0.464680665246457, 0.768212043461448, ... ,0.281771048521311}

#### Step 6 - Sort values in random order

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})

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

#### Step 5 - Wrap values

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

becomes

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

and returns

{63,45,27,68;

84,26,92,62;

98,37,89,19}

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

## 7. WRAPROWS Function - multiple source ranges

The graphic above demonstrates a formula that merges values from cell ranges B2:M2, B4:G4, and B6:K6, then wraps the values to a 2D range with max four values wide.

Dynamic array formula in cell B6:

### 8.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

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

becomes

WRAPROWS({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, 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}

## Functions in this article

### Functions in 'Array manipulation' category

The WRAPROWS function function is one of many functions in the 'Array manipulation' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form