Restructuring raw data exports in Google Sheets frequently involves reshaping long, single-column lists into multi-column layouts. If you import a raw text export containing hundreds of customer records—where name, email, phone number, and city follow consecutively in a single vertical stream—formatting them into a standard 4-column table previously required complex index mathematics or cumbersome offset formulas. Google Sheets modernised array transformations with the introduction of WRAPCOLS and WRAPROWS. These dynamic array functions convert a one-dimensional row or column vector into a structured two-dimensional matrix with a single formula.
Understanding the Syntax of WRAPCOLS and WRAPROWS
Both functions take an input vector and fold (wrap) the data into a grid once a defined item count threshold is reached. The two functions differ solely in their packing direction:
- WRAPROWS: Fills cells horizontally across rows from left to right until reaching the wrap limit, then drops down to start a fresh row.
- WRAPCOLS: Fills cells vertically down columns from top to bottom until reaching the wrap limit, then shifts right to start a fresh column.
The standard syntax for WRAPROWS:
=WRAPROWS(range, wrap_count, [pad_with])The standard syntax for WRAPCOLS:
=WRAPCOLS(range, wrap_count, [pad_with])Let’s examine the three arguments:
range: The 1D source vector (e.g.,A2:A100orA2:Z2) you wish to wrap into a matrix.wrap_count: The maximum number of values allowed in each row or column before wrapping to the next line.pad_with(optional): The fallback value used to fill empty cells if the total number of items does not divide evenly by the wrap count. Defaults to#N/Aif omitted.
Step-by-Step Example: Reshaping Customer Records with WRAPROWS
Suppose you have an unformatted customer list in column A2:A41. Every 4 consecutive rows represent a complete client record: Name, Email, Telephone, and Country.
To convert this into a clean 4-column table, navigate to cell C2 and enter:
=WRAPROWS(A2:A41, 4, "")Google Sheets scans through column A, packs 4 consecutive values across columns C, D, E, and F, and wraps to row 3 to begin the next record. The complete dataset spills into a structured 10-row by 4-column database automatically.
Using WRAPCOLS to Build Columnar Schedules
If you have an unbroken chronological list of 35 daily staff assignments and want to divide them into 5 equal 7-day weekly work blocks where each week occupies its own vertical column:
Enter the formula into cell C2:
=WRAPCOLS(A2:A36, 7, "Off")WRAPCOLS reads down to item 7, fills column C, shifts to column D for items 8–14, and repeats across five vertical columns. If a week contains fewer than 7 days, empty slots are cleanly padded with "Off" rather than calculation error badges.
Handling Incomplete Datasets Safely with pad_with
When the total count of items in your source vector does not divide evenly by your wrap_count, Google Sheets pads the trailing cells with #N/A errors by default.
For example, if you wrap 10 items into rows of 3, the final row will contain 1 item followed by two #N/A badges. Passing a deliberate third parameter eliminates these unsightly errors:
- Pass an empty string to keep padding invisible:
=WRAPROWS(A2:A11, 3, "") - Pass a descriptive placeholder string:
=WRAPROWS(A2:A11, 3, "Pending") - Pass a numerical zero for financial sums:
=WRAPROWS(A2:A11, 3, 0)
Google Sheets Array Transformation Matrix
| Function | Input Dimensions | Output Orientation | Core Utility |
|---|---|---|---|
| WRAPROWS | 1D Vector (Row or Col) | 2D Matrix (Row-first) | Transforming consecutive records into tabular rows |
| WRAPCOLS | 1D Vector (Row or Col) | 2D Matrix (Column-first) | Building columnar weekly calendars and shift grids |
| TOCOL | 2D Matrix or Table | 1D Vertical Vector | Flattening multi-column data into single vertical list |
| TOROW | 2D Matrix or Table | 1D Horizontal Vector | Flattening multi-column data into single horizontal strip |
Pairing WRAPROWS with FILTER and SORT
You can chain WRAPROWS dynamically with data cleaning functions to remove blanks before reshaping:
To purge empty cells from an irregular source list before wrapping into 3-column rows:
=WRAPROWS(FILTER(A2:A100, A2:A100 <> ""), 3, "-")The FILTER function strips out all blank rows in column A first, ensuring that only populated entries are passed into WRAPROWS.
Frequently Asked Questions
Can WRAPCOLS and WRAPROWS accept a two-dimensional grid as input?
No. Both functions expect a one-dimensional array (either a single row or single column). If you have a 2D table that you want to re-wrap at a different column interval, flatten it first using TOCOL: =WRAPROWS(TOCOL(A2:D20), 6, "").
Do WRAPROWS and WRAPCOLS work in Microsoft Excel?
Yes. Both functions are fully supported in Microsoft 365, Excel for the Web, and Excel 2024 using identical formula syntax and argument ordering.
What happens if wrap_count is set to a negative number or zero?
Google Sheets will return a #VALUE! error. The wrap_count argument must always be a positive integer greater than or equal to 1.