WRAPROWS and WRAPCOLS Functions in Microsoft Excel

Aug 31, 2023 | Excel Hints and Tips

These are a couple more of the new dynamic array functions.  They both take a list of data and transform it into a block of data with a maximum number of values in each row or column respectively.

This would be useful if you had a list of people and wanted to divide them into a certain number of teams quickly.

WRAPROWS

WRAPROWS wraps the list of data (it could be a single row or a single column) into a set number of columns.

Syntax

=WRAPROWS(vector, wrap count, pad with)

Vector – This is the range of cells to be wrapped

Wrap count – This is the number of columns to be created

Pad with – This is optional.  It specific text to be shown in cells that don’t make up a perfect grid.  If it is omitted then these will be shown as #N/A

Example

The example below takes the list in column A and wraps it into 3 columns.  Because nothing is entered for the pad with section #N/A is added in cell F5

Wraprows example image

 

WRAPCOLS

WRAPCOLS the list of data (it could be a single row or a single column) into a set number of rows.

Syntax

=WRAPCOLS(vector, wrap count, pad with)

Vector – This is the range of cells to be wrapped

Wrap count – This is the number of rows to be created

Pad with – This is optional.  It specific text to be shown in cells that don’t make up a perfect grid.  If it is omitted then these will be shown as #N/A

Example

The example below takes the list in column A and wraps it into 3 rows.  Because “-“ is entered for the pad with section ‘-‘ is added in cell H3

Wrapcols example image

 

Errors

We have already talked about the #N/A error that is displayed if the data does not fall into a perfect grid and nothing is added to the pad with section of the function.  Here are 2 other errors.

  • If the vector contains more than 1 row or more than 1 column then the error message #VALUE is returned
  • If the Wrap count is less than 1 then #NUM is returned

 

Other Dynamic Array Functions

WRAPCOLS and WRAPROWS functions are part of the ever-growing list of dynamic array functions which I have written about before.  If you have missed any of them the links to all of them are below with a summary of what the functions do.

RANDARRAY, SEQUENCE AND UNIQUE 

UNIQUE – extracts unique items from a range of cells

RANDARRAY – generates an array of random numbers

SEQUENCE – generates a list of sequential numbers

 

FILTER, SORT and SORTBY 

FILTER – filters data based in the criteria you define

SORT – sorts a range of cells by a specified column

SORTBY – sorts a range of cells by another range or array

 

TEXTSPLIT, TEXTBEFORE and TEXT AFTER 

TEXTSPLIT – splits strings by a specified delimiter across columns or/and rows

TEXTBEFORE – returns the text before a specified delimiter

TEXTAFTER – returns the text after a specified delimiter

 

TCOL and TOROW 

TOCOL – converts an array or range to a single column

TOROW – converts an array or range into a single row

 

TAKE and DROP 

TAKE – extracts a specified number of rows or columns from the start or end of an array

DROP – removes a certain number of rows or columns from an array

 

EXPAND 

EXPAND – grow an array to the specified number of rows and columns

CHOOSECOLS – returns the specified columns from an array

CHOOSEROWS – returns the specified rows from an array

 

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.