WRAPROWS and WRAPCOLS in Excel: Turn a List Into a Grid

WRAPROWS in Excel takes one long list and wraps it into a grid, a set number of values per row, the way text wraps onto the next line. WRAPCOLS does the same job but fills down each column first. This guide shows the syntax of both, how to handle lists that don't divide evenly, how to sort before you wrap, and what to use if your Excel doesn't have them yet.

What do WRAPROWS and WRAPCOLS do?#

WRAPROWS and WRAPCOLS reshape a one-dimensional list (a single row or column) into a two-dimensional grid. They are dynamic array functions: you type the formula in one cell and the result spills into the cells next to and below it. If dynamic arrays are new to you, read how dynamic arrays and spilling work first.

=WRAPROWS(vector, wrap_count, [pad_with])
=WRAPCOLS(vector, wrap_count, [pad_with])

Both functions need Microsoft 365, Excel for the web or Excel 2024. In Excel 2021 or earlier they return #NAME?.

How to use WRAPROWS: a step-by-step example#

To wrap a list into rows, select an empty cell, give WRAPROWS the list and the number of values you want in each row, and press Enter. Say A2:A13 holds twelve names for a seating plan, and you want tables of three:

1. Click an empty cell with space to the right and below, for example C2.
2. Type =WRAPROWS(A2:A13,3) and press Enter.

The result spills into C2:E5: four rows of three names. The first row holds names 1 to 3, the second holds names 4 to 6, and so on.

If any cell in C2:E5 already has something in it, you get #SPILL! instead. Clear the cells in the way and the grid appears.

How WRAPCOLS is different#

WRAPCOLS fills down each column before it moves right, so wrap_count sets the number of rows instead of the number of columns. With the same twelve names:

=WRAPCOLS(A2:A13,3)

This gives 3 rows and 4 columns. Column 1 holds Amara, Ben and Chloe. Column 2 holds Dev, Elif and Farid. Use WRAPCOLS when you want people to read down the columns, like a phone list split into newspaper-style columns.

A quick way to remember: WRAPROWS builds full rows of wrap_count values, and WRAPCOLS builds full columns of wrap_count values. If you only need to flip a range on its side, rows to columns, see how to transpose data in Excel instead.

What if the list doesn't divide evenly?#

When the number of values isn't a multiple of wrap_count, the last row or column has gaps, and Excel fills them with #N/A. Ten names wrapped by three leave two empty spots:

=WRAPROWS(A2:A11,3)

The fourth row shows Jonas, #N/A, #N/A. To hide the errors, add the third argument:

=WRAPROWS(A2:A11,3,"")

Now the gaps are empty text. You can pad with anything: 0, "-" or "Spare". The pad value only fills the end of the grid. It never replaces a real blank cell inside the list; a blank inside the list shows as 0.

How to sort or filter a list before you wrap it#

You can wrap the result of another function, so sort first and wrap second. This puts the names in A to Z order across the grid:

=WRAPROWS(SORT(A2:A13),3)

SORT returns the sorted list and WRAPROWS shapes it. The SORT function guide covers sorting descending and by another column. The same idea works with other list functions:

• A unique list in a grid: =WRAPROWS(SORT(UNIQUE(A2:A40)),4). See how to make a sorted unique list.
• Random groups: sort the names randomly, then wrap them into teams of four. The guide to randomizing a list shows the random sort.
• A numbered grid: =WRAPROWS(SEQUENCE(20),5) gives 1 to 20 in four rows of five, although =SEQUENCE(4,5) does that on its own. More in the SEQUENCE function guide.

Common WRAPROWS errors and how to fix them#

Most problems come from the shape of the source or the space for the result.

1. #VALUE! The vector is more than one row or column, such as A2:B13. Point it at a single column, or stack the table into one column first with TOCOL (Microsoft 365 and Excel 2024).
2. #NUM! wrap_count is less than 1, for example 0 or a blank cell.
3. #SPILL! Something blocks the cells where the grid needs to go. Clear them.
4. #NAME? Your Excel version doesn't have WRAPROWS. Use the INDEX method below.
5. Gaps show #N/A. Add a pad_with value such as "".

How to wrap a list in older Excel with INDEX#

Without WRAPROWS, you can work out which list position belongs in each grid cell and fetch it with INDEX. In a grid that is cols values wide, row r and column c hold list item number (r - 1) * cols + c. For the 3-wide seating plan, row 2, column 3 is item (2 − 1) × 3 + 3 = 6, which is Farid.

To build the whole grid, put this in the top-left cell and fill it right and down:

=INDEX($A$2:$A$13,(ROW(A1)-1)*3+COLUMN(A1))

ROW(A1) counts 1, 2, 3 as you fill down and COLUMN(A1) counts 1, 2, 3 as you fill right, so each cell picks the right name. The $ signs keep the list fixed. Past the end of the list, INDEX returns #REF!, so wrap the formula in IFERROR(...,"") when the list doesn't divide evenly.

In Microsoft 365, =INDEX(A2:A13,SEQUENCE(4,3)) gives the same grid as WRAPROWS in one formula, because SEQUENCE(4,3) numbers the cells 1 to 12 across each row. For a column-first layout like WRAPCOLS, swap the order inside INDEX to (c - 1) * rows + r.

Practise it#

Try the exercise on this page: it uses the INDEX position rule, so you'll know exactly where each value lands in a wrapped grid. Then practise modern Excel functions like SORTBY and spill references and practise SORT in a live spreadsheet: you type each formula and the sheet checks it instantly.