HomeGoogle DocsSpreadsheetWRAPCOLS Function in Google Sheets

WRAPCOLS Function in Google Sheets

Published on

WRAPCOLS is an array function in Google Sheets specifically for transforming a row or column into multiple columns.

Of course, we can use multiple OFSSETs, QUERY, or a VLOOKUP and SEQUENCE combo to transform values in a row or column into a two-dimensional array.

Most of us were following one of those approaches, right?

But one of the best and easiest ways is using the WRAPCOLS function.

The other one is WRAPROWS, which we learned in my recent tutorial here.

How do they differ in functionality?

WRAPCOLS returns the results in columns, whereas the WRAPROWS in rows.

Syntax and Arguments

Syntax: WRAPCOLS(range, wrap_count, [pad_with])

Arguments in the WRAPCOLS Function:

range:- The array or range to wrap. It can be in a single column or row.

wrap_count:- A number representing the maximum number of cells for each column in the output.

If wrap_count isn’t a whole number (e.g., 5.5), the function will round it down to the nearest whole number (5 here).

pad_with – It’s an optional argument in the WRAPCOLS array function to replace #N/A in blank cells with a given value.

For example, if there are ten cells in the range and the wrap_count is 6, the formula will return #N/A in the eleventh and twelfth cells.

How to Use the WRAPCOLS Function in Google Sheets

In the following example, Q1, Q2, Q3, and Q4 month-wise sales values are entered in a column under corresponding labels.

WRAPCOLS examples in Google Sheets

I’ve used the below WRAPCOLS formula in cell D3 to transform that data into multiple columns. So we will get each quarter’s data in different columns.

=wrapcols(B3:B18,4)

In the formula the range is B3:B18 and the wrap_count is 4.

The WRAPCOLS function is one of the new functions in Google Sheets.

So, I was using the below combination formula for this purpose.

WRAPCOLS Alternative:

=ArrayFormula(vlookup(transpose(sequence(4,4,row(B3))),{row(B3:B18),B3:B18},2,0))

Important:- We have used a column range in the above formula. But it can be a row also. There won’t be any changes except the range reference in the formula.

Pad_With Argument

=wrapcols(A1:A5,3)
Pad_With Argument

The range in the above WRAPCOLS function contains five elements.

Since the wrap_count is 3, the third cell in the second column is blank. So the formula returns #N/A!

To replace it with “no value,” use the following formula with the pad_with value.

=wrapcols(A1:A5,3,"no value")

WRAPCOLS Function and Sorting a Two-Dimensional Array

We have the sales values under Q1, Q2, Q3, and Q4 columns in D3:G6 as per screenshot # 1 above.

How do we sort the monthly sales under each column using a single piece of code?

Sorting a Two-Dimensional Array in Asc Order

Let me code the formula in a few steps that may help you understand its functioning.

We will use the WRAPCOLS function in the final step only.

WRAPCOLS function in Sorting a Two-Dimensional Array

Copy the field labels in D3:G3 and paste them into D10:G10.

We have a 3 x 4 (three rows and four columns) matrix leaving the header row. Use the following MAKEARRAY formula to generate a similar-size matrix.

=makearray(3,4,lambda(r,c,c+0))

Flatten it.

=flatten(makearray(3,4,lambda(r,c,c+0)))

Flatten the values except for the field labels.

=flatten(D4:G6)

Combine the above two formulas to make a two-column array.

={flatten(makearray(3,4,lambda(r,c,c+0))),flatten(D4:G6)}

The first column represents field labels, and the second column the actual values.

Sort it based on column 1 (field labels) in Asc order and column 2 (sales figures) also in Asc order.

=sort({flatten(makearray(3,4,lambda(r,c,c+0))),flatten(D4:G6)},1,1,2,1)

Extract the second column using the INDEX function.

=index(sort({flatten(makearray(3,4,lambda(r,c,c+0))),flatten(D4:G6)},1,1,2,1),0,2)

Then use the WRAPCOLS to wrap the above one-dimensional array into a two-dimensional array.

=wrapcols(index(sort({flatten(makearray(3,4,lambda(r,c,c+0))),flatten(D4:G6)},1,1,2,1),0,2),3)

That’s the formula in cell D11.

Sorting a Two-Dimensional Array in Desc Order

Making only one change in the above formula is enough to sort the sales values in descending order.

The 1,1,2,1 parameters belong to the SORT function, and the last 1 represents the second column sort order.

Use 0 (zero) to replace it, and voila!

=wrapcols(index(sort({flatten(makearray(3,4,lambda(r,c,c+0))),flatten(D4:G6)},1,1,2,0),0,2),3)

That’s all about how to use the WRAPCOLS function in Google Sheets. Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here