Data imported into Microsoft Excel from external text files, continuous API streams, barcodes, or survey exports often arrives as a single, uninterrupted vertical or horizontal list. When designing printable schedules, badge layouts, catalog grids, or multi-column reports, reorganizing that continuous one-dimensional stream into a structured two-dimensional grid historically required complex nested INDEX, MOD, and ROW formulas.
Microsoft introduced the WRAPROWS and WRAPCOLS dynamic array functions to simplify dimensional shaping. These functions wrap a single continuous row or column vector into a multi-column or multi-row matrix based on a specified element count, automatically filling empty trailing slots with a custom pad value.
Syntax and Parameters of WRAPROWS and WRAPCOLS
Both functions take an input vector and fold it into a two-dimensional grid:
=WRAPROWS(vector, wrap_count, [pad_with])
=WRAPCOLS(vector, wrap_count, [pad_with])
- vector: The one-dimensional range or array of elements to be wrapped (such as
A2:A37). - wrap_count: The maximum number of elements before starting a new row (for
WRAPROWS) or a new column (forWRAPCOLS). - pad_with (optional): The placeholder value (such as
"","N/A", or"-") used to fill remaining empty cells if the input vector does not divide evenly by the wrap count. Defaults to#N/Aif omitted.
Wrapping a Single Column into a Multi-Column Grid with WRAPROWS
Suppose you have a continuous vertical list of 24 participant names in column A2:A25 that you want to organize into tables seating 4 people per row:
- Select the top-left cell where your grid should begin (for example,
C2). - Enter the following
WRAPROWSformula:
=WRAPROWS(A2:A25, 4)
Excel evaluates the list, places the first 4 names across row 2 (columns C through F), wraps to row 3 for names 5 through 8, and continues until all 24 names are formatted into a clean 6-row by 4-column seating chart.
Handling Uneven Vectors with the pad_with Argument
If your vector length is not perfectly divisible by the wrap count (for example, wrapping 22 items into rows of 4), the final row will contain two missing positions:
- Without padding, the final two cells will display
#N/Aerrors. - To suppress the error and fill unused positions with clean empty text:
=WRAPROWS(A2:A23, 4, "")
- Alternatively, supply a descriptive placeholder string:
=WRAPROWS(A2:A23, 4, "Vacant")
Wrapping Data by Columns Using WRAPCOLS
While WRAPROWS fills elements horizontally across rows before wrapping downwards, WRAPCOLS fills elements vertically down columns before wrapping to the right:
- Consider a linear sequence of dates or sequential shift codes in
A2:A31. - To format them into weekly work shifts where each column represents a 7-day week:
=WRAPCOLS(A2:A31, 7, "")
Excel fills cells downward 7 rows in column C (Days 1–7), moves to column D for Days 8–14, and wraps across columns sequentially.
Pairing WRAPROWS with FILTER for Dynamic Catalog Grids
You can combine WRAPROWS with dynamic filtering to create automated, updating report cards:
- To filter an inventory ledger for active products only and wrap the results into a 3-column product catalog:
=WRAPROWS(FILTER(A2:A100, B2:B100="In Stock"), 3, "No Product")
Whenever items go out of stock or new products are added, the two-dimensional matrix recalculates and reorganizes automatically from a single cell.