You may need to reshape a list of values into a table for your analysis or reports. In this blog post, You will learn two advanced Excel functions WRAPCOLS and WRAPROWS. It allows you to reshape a one-dimensional array or range into a two-dimensional array by wrapping the values by columns or rows in Excel.
If you have the months Jan to Jul in the range of cells A1:A7 and reshape for your report.
Excel WRAPCOLS function
The WRAPCOLS function allows you to transform a row or column of values into a range with a specified number of values per column.
=WRAPCOLS (array, wrapCount, [padWith])
For example, if you want to convert the sample data into a two-dimensional array with 3 values per column.
Go to cell C1, type the formula =WRAPCOLS(A1:A7,3), and press Enter.
The months are arranged vertically, from top to bottom, based on the wrapCount value. Once the count reaches, pad with #N/A. You can specify the value in the padWith, if you need a different value for padding.
You can use the below formula to specify null value to the padWith.
=WRAPCOLS(A1:A7,3,””)
Excel WRAPROWS function
The WRAPROWS function allows you to transform a row or column of values into a range with a specified number of values per row.
= WRAPROWS (array, wrapCount, [padWith])
For example, if you want to convert the sample data into a two-dimensional array with 3 values per row.
Go to cell C6, type the formula =WRAPROWS(A1:A7,3), and press Enter.
The months are arranged horizontally, from left to right, based on the wrapCount value. Once the count reaches, pad with #N/A. You can specify the value in the padWith, if you need a different value for padding.
You can use the below formula to specify null value to the padWith.
=WRAPROWS(A1:A7,3,””)
Conclusion
The WRAPCOLS and WRAPROWS functions are available in both desktop and web Excel. It helps you to reshape your data for various purposes, such as analysis, presentation, or formatting.
I hope this blog post has helped you understand how to use the WRAPCOLS and WRAPROWS functions in Excel. If you have any questions or feedback, please leave a comment below.