A flat list of 12 months becomes a 3×4 calendar grid. A single column of 50 products reshapes into a 5×10 display table. WRAPROWS and WRAPCOLS perform this transformation in one formula. They take a one-dimensional array and wrap it into a two-dimensional grid by specifying how many items appear in each row or column. Both are Excel 365 functions that replaced a cumbersome INDEX+MOD+INT construction that previously required array entry and careful row/column arithmetic.
This guide covers both functions fully with eight practical examples. You will learn how to reshape a product list into a display grid, build a dynamic calendar, handle remainders with custom padding, create a comparison table from alternating columns, and combine WRAPROWS with SORT and FILTER for formatted dynamic outputs. Each example also explains the pad argument — the key that controls what appears in the incomplete final row or column.
What Are WRAPROWS and WRAPCOLS?
WRAPROWS takes a one-dimensional array and wraps it into rows of a specified width. After each wrap_count items, it starts a new row. WRAPCOLS does the same but fills columns instead — after wrap_count items it starts a new column. Together they cover the two directions in which you might want to tile a flat list into a grid.
The key distinction is fill direction. WRAPROWS fills horizontally: it places items left-to-right across a row, then drops to the next row. WRAPCOLS fills vertically: it places items top-to-bottom in a column, then moves to the next column. If the total number of items does not divide evenly by the wrap count, the last row or column has fewer items. The pad argument fills those empty positions with a value of your choice — typically a blank string, zero, or NA().
What Is the WRAPROWS and WRAPCOLS Syntax?
| Argument | Required? | Description |
|---|---|---|
| vector | Required | A one-dimensional array (single row or single column). Multi-dimensional arrays return an error. Use TOCOL or TOROW first to flatten a 2D range. |
| wrap_count | Required | The number of items per row (WRAPROWS) or per column (WRAPCOLS). Must be a positive integer. This determines the grid width or height. |
| pad_with | Optional | The value to fill any empty positions in the final partial row/column. Default is #N/A. Use "" for blank, 0 for zero, or NA() to keep the default error-style padding. |
Examples 1–8: WRAPROWS and WRAPCOLS in Practice
The core use is converting a single column of values into a grid of specified width. WRAPROWS produces one row per group. WRAPCOLS produces one column per group. Both are one-formula replacements for the old INDEX+MOD+INT approach.
When the item count does not divide evenly by wrap_count, the last row or column is shorter than the others. The pad_with argument fills those empty positions. Without it, the default padding is #N/A. For most display purposes, use an empty string "" for blank padding. For formulas that need to calculate on the grid, use 0 or a specific sentinel value.
A calendar is a flat sequence of dates wrapped into rows of seven. WRAPROWS handles this in one formula. The starting point is a SEQUENCE of date values. Wrapping that sequence into rows of 7 produces the seven-column weekly layout. Additionally, padding with blank strings fills the last partial week without showing errors.
A single-column product list is easy to sort and filter but hard to display in a compact catalogue format. WRAPCOLS reshapes it into multiple columns, filling top-to-bottom so each column reads vertically like a traditional catalogue. This is specifically useful for price lists, inventory tables, and option menus where vertical scanning is more natural than horizontal.
Examples 5–8: Advanced WRAPROWS and WRAPCOLS
Combining FILTER with WRAPROWS produces a filtered grid that updates automatically as data changes. This is particularly useful for dashboards where filtered results need to be shown in a compact tiled layout rather than a long vertical list. The wrap count controls how many items appear per row.
WRAPROWS with wrap_count = 2 on an interleaved sequence creates a side-by-side pairing of two lists. This is useful for creating before/after tables, comparing two score sets, or pairing names with values. The interleaving requires combining both arrays into a single flat vector first, then wrapping into pairs.
Survey data often arrives as a long flat list of responses: question 1 January, question 2 January, question 3 January, question 1 February, and so on. WRAPROWS reshapes this into a readable month-by-question grid. The wrap count equals the number of questions per month.
Hard-coding the wrap count requires manual updates when the data size changes. A dynamic wrap count calculated from the data length and a target column count adjusts automatically. This is the right approach for any report where the number of items grows over time.
Common Issues and How to Fix Them
WRAPROWS returns #VALUE! with a 2D range
WRAPROWS and WRAPCOLS require a one-dimensional vector. Passing a multi-column range directly returns #VALUE!. First flatten the range using TOCOL (column-major order) or TOROW (row-major order), then pass the result to WRAPROWS or WRAPCOLS. For example, =WRAPROWS(TOCOL(A2:C10), 3) flattens the 3-column range into a single column before wrapping.
The last row has unwanted #N/A errors
The default pad_with value is #N/A, which fills incomplete rows with errors. This is intentional — it signals that a position has no data. However, it causes problems when the result feeds into formulas like SUM or AVERAGE. Always specify pad_with explicitly: use "" for blank cells or 0 for numeric zero. Using IFERROR on the result is a workaround but less clean than setting pad_with correctly from the start.
Frequently Asked Questions
-
What is the difference between WRAPROWS and WRAPCOLS?+WRAPROWS fills a grid left-to-right and wraps to the next row after each wrap_count items — like text on a page. WRAPCOLS fills top-to-bottom and wraps to the next column after each wrap_count items — like newspaper columns. The same flat list of items produces different grids depending on which function you use. Choose WRAPROWS when each row represents a logical group (e.g. one week of days). Choose WRAPCOLS when each column represents a logical group (e.g. one alphabetical section of a product catalogue).
-
How do I control what fills the empty positions in the last row?+Use the third argument (pad_with). The default is #N/A. For blank cells, use "". For zeros, use 0. For a custom label like "—", pass that text string. The pad_with value appears only in the positions that have no corresponding source value — it does not affect any complete rows or columns. If you are building a visual display grid, "" is the most common choice. If the grid feeds into calculations, use 0 to avoid errors in SUM and AVERAGE formulas.
-
Can I use WRAPROWS with a 2D range?+Not directly — WRAPROWS requires a one-dimensional vector. However, you can first flatten a 2D range using TOCOL (reads column by column) or TOROW (reads row by row), then pass the result to WRAPROWS. For example, =WRAPROWS(TOCOL(A2:C10), 3) flattens the 3×9 range into a 27-item column, then wraps it into 9 rows of 3. The order of flattening matters, so choose TOCOL or TOROW based on which reading order you want.
-
How do I build a dynamic calendar using WRAPROWS?+Generate all dates in the month using SEQUENCE(DAY(EOMONTH(D1,0)), 1, D1) where D1 is the first day of the month. Then wrap into rows of 7 with a blank pad: =WRAPROWS(SEQUENCE(DAY(EOMONTH(D1,0)),1,D1), 7, ""). Format the result cells as "d" (day number only) for a clean calendar display. Add column headers for Mon through Sun above the formula cell. Change D1 to a different month and the calendar updates automatically.