The Problem with Linear Data
Data imported from external systems, CSV files, or API pulls often arrives in a single, incredibly long column or row. For example, if you paste a list of 100 names into Excel, they will likely occupy cells A1 down to A100. While this is structurally sound, a single column stretching across 100 rows is terrible for creating printable reports or readable dashboards.
Historically, transforming a single 1D column into a neat 2D grid (like a 10×10 table) required complex, nested INDEX math or clunky VBA scripts. Microsoft solved this by introducing the WRAPROWS and WRAPCOLS dynamic array functions.
These functions take a single line of data and “wrap” it into a grid based on the exact dimensions you specify.
How to Use the WRAPROWS Function
The WRAPROWS function takes a 1D array and begins filling it into a new grid row by row, left to right. When it hits the maximum column width you define, it “wraps” around and starts a new row.
Syntax: =WRAPROWS(vector, wrap_count, [pad_with])
- vector: The single row or column of data you want to reformat.
- wrap_count: The maximum number of columns you want in your new grid.
- pad_with (Optional): The value to display if there is leftover empty space at the end of the grid.
Example: Creating a 5-Column Grid
Assume you have a list of 20 employee names running down column A (from A1 to A20). You want to reorganize this into a compact, printable grid that is exactly 5 columns wide.
In a new cell, type:
=WRAPROWS(A1:A20, 5)
Excel will instantly generate a 5×4 grid. It places the first 5 names in row 1, the next 5 names in row 2, and so on.
How to Use the WRAPCOLS Function
WRAPCOLS does the exact same thing, but it fills the grid column by column, top to bottom. It asks for the maximum number of rows before it wraps to a new column.
Syntax: =WRAPCOLS(vector, wrap_count, [pad_with])
Using the same list of 20 names, if you want a grid that is exactly 4 rows deep, you would use:
=WRAPCOLS(A1:A20, 4)
Excel will fill the first 4 names down the first column, then move to the top of the second column for the next 4 names, creating a 5-column wide by 4-row deep grid.
Handling Leftover Data with Padding
Both functions work perfectly when your data divides evenly into the grid dimensions. However, what happens if you have 22 names, and you ask WRAPROWS to make a 5-column grid?
Excel will create a 5×5 grid, but the very last row will only have 2 names in it. The remaining 3 spaces in the grid will display a very ugly #N/A error, because there is no data left to fill them.
This is where the [pad_with] argument is critical. You can tell Excel to fill those empty spaces with a blank space, a dash, or specific text instead of an error.
=WRAPROWS(A1:A22, 5, "")
By adding the double quotation marks at the end, Excel will neatly leave the final three cells of the grid completely blank, ensuring your dashboard remains perfectly clean and professional.