The `WRAPCOLS` function in Excel is used to wrap the contents of a given range into a specified number of columns. This function is particularly useful for reorganizing data in a row-based format into a column-based format when you have a fixed number of columns you want to maintain.
Here is how you use the `WRAPCOLS` function:
Syntax
WRAPCOLS(vector, wrap_count, [pad_with])
- vector: This is the range of cells or array that you wish to wrap into columns.
- wrap_count: This specifies the number of values you want in each column after wrapping.
- pad_with (optional): This allows you to specify what value to use to fill in any empty cells if there are not enough data values to complete the last column.
Example
Suppose you have a dataset in a single row (e.g., A1:A10) and you want to transform this data into a column format with three rows each:
=WRAPCOLS(A1:A10, 3)
1 4 7 10
2 5 8
3 6 9
- Dataset: `1, 2, 3, 4, 5, 6, 7, 8, 9, 10` (Located in A1:A10)
- Formula:
- Result: This will output the data wrapped into a column format:
Using the `pad_with` argument
If your data doesn’t perfectly fill up the last column and you want to specify a value to fill the empty spots (for example, with “N/A”):
=WRAPCOLS(A1:A10, 4, "N/A")
This will output:
1 5 9 N/A
2 6 10 N/A
3 7 N/A N/A
4 8 N/A N/A
Keep in mind that `WRAPCOLS` is available in Excel for Microsoft 365 and Excel 2021 onward. Ensure your version of Excel supports this function.
Unlock Your Potential
Excel
Access
Power BI
