Transforming a long, continuous single-column list of items into a structured multi-column table—or converting a wide horizontal row of data into a neat grid—historically required complex formula workarounds combining INDEX, SEQUENCE, ROW, and COLUMN math. In modern Microsoft Excel, dynamic array functions provide native, elegant tools for reshaping data: WRAPROWS and WRAPCOLS. These functions allow you to wrap a one-dimensional array into a structured two-dimensional matrix with a single formula.
Syntax and Function Parameters
Both functions share an identical parameter structure:
=WRAPROWS(vector, wrap_count, [pad_with])
=WRAPCOLS(vector, wrap_count, [pad_with])
Parameters include:
- vector: The one-dimensional range or array of values to wrap (either a single vertical column or a horizontal row).
- wrap_count: The maximum number of values in each row (for
WRAPROWS) or each column (forWRAPCOLS). - [pad_with]: An optional value used to fill empty cells if the vector length is not an exact multiple of the
wrap_count. Defaults to#N/Aif omitted.
Converting a Flat List into a Multi-Column Grid with WRAPROWS
Suppose you import raw database output in column A (cells A2:A25), where every four consecutive cells represent a complete record: Name, Department, City, and Sales. To reshape this linear stream into a structured 4-column table:
- Click in an empty cell on your worksheet (e.g., cell
C2). - Enter the following formula:
=WRAPROWS(A2:A25, 4, "") - Press Enter. Excel automatically takes the first four items, places them horizontally across columns C, D, E, and F, then wraps to the next row below, spilling the entire dataset into a 4-column matrix.
- Providing
""as thepad_withargument ensures that any trailing empty slots render as blank cells rather than displaying#N/Aerrors.
Organizing Data into Fixed-Height Columns with WRAPCOLS
If you want to arrange a continuous list of 30 product codes into a multi-column print directory where each vertical column contains exactly 10 items before spilling into the next column to the right:
=WRAPCOLS(A2:A31, 10, "-")
Excel places items 1 through 10 in the first column, items 11 through 20 in the second column, and items 21 through 30 in the third column.
Combining WRAPROWS with SORT and UNIQUE
The true power of WRAPROWS emerges when combined with other dynamic array calculations. For example, to extract a sorted, unique list of product categories and display them across a compact 3-column dashboard badge layout:
=WRAPROWS(SORT(UNIQUE(A2:A100)), 3, "None")
In this expression:
UNIQUE(A2:A100)extracts distinct category entries.SORT(...)sorts the unique values alphabetically.WRAPROWS(..., 3, "None")wraps the sorted entries horizontally across 3 columns, padding any final empty cell with “None”.
Mastering WRAPROWS and WRAPCOLS streamlines data cleanup and reporting workflows in Microsoft Excel, replacing cumbersome matrix formulas with clean, self-updating dynamic arrays.