WRAPROWS Function in Excel

The WRAPROWS function in Excel takes a single column or row of values and wraps it into a grid, with as many values in each row as you ask for.

It’s handy when data lands in one long list, like a product export where each item’s name, price, and rating are stacked one under the other.

In this article, I’ll show you how WRAPROWS works, how to clean up the #N/A it can leave behind, and how to pair it with FILTER, TOCOL, and TEXTSPLIT.

WRAPROWS Function Syntax

Here is the syntax of the WRAPROWS function:

=WRAPROWS(vector, wrap_count, [pad_with])

• vector – the single row or single column of values you want to wrap. It can be a range, an array, or the result of another formula.
• wrap_count – the number of values you want in each row of the result.
• [pad_with] – (optional) the value used to fill the empty spots in the last row. If you leave it out, Excel fills them with #N/A.

The wrap_count sets how wide each row is, not how many rows you get. Excel works out the number of rows on its own, so 10 values with a wrap_count of 3 give you four rows (3 + 3 + 3 + 1).

Values fill across each row before moving down to the next one. If wrap_count is equal to or larger than the number of values, you simply get the whole list back as one row, with no padding.

If you give WRAPROWS a range that is more than one row tall and more than one column wide, it returns a #VALUE! error.

The WRAPROWS function is available in Excel for Microsoft 365 and Excel 2024, on Windows and Mac, and in Excel for the web. It isn’t available in Excel 2021, 2019, or older versions, where the formula returns a #NAME? error.

When to Use the WRAPROWS Function

Use this function when you need to:

• Arrange a long list into rows of a fixed size, such as seating plans, teams, or groups
• Rebuild a table from data that was pasted as one long column, one field per cell
• Group a single row of values (like monthly numbers) into quarters or other blocks
• Change how many columns an existing grid has, when combined with TOCOL
• Turn a comma-separated text string into a proper table, when combined with TEXTSPLIT

Let me show you a few practical examples of how to use the WRAPROWS function.

Example 1: Wrap a Single Column into Rows of Four

Let’s start with a simple example.

Below is a list of 12 party guests, and I want to seat them at tables of four, with each table as one row starting in column C.

Here is the formula:

=WRAPROWS(A2:A13,4)

The formula takes the 12 names in A2:A13 and places them four at a time across each row. Jessica, Michael, Ashley, and Chris go into row 2 (the first table), the next four go into row 3, and the last four go into row 4.

Since 12 divides evenly by 4, every row is full. And the whole result spills into C2:F4 from the one formula in C2, so there’s nothing to copy across or down.

If any cell in that C2:F4 area already has something in it, the formula can’t spill and you get a #SPILL! error instead. Clear the cells in the way, and the result shows up.

If you are using Excel 2021 or earlier, enter the below formula in cell C2, copy it across to F2, and then copy those four cells down to row 4:

=INDEX($A$2:$A$13,(ROWS($C$2:C2)-1)*4+COLUMNS($C$2:C2))

This formula uses the INDEX function to pick a name by its position in the list. The ROWS part tells each cell which row it’s in, and the COLUMNS part tells it which column it’s in.

The formula turns those into a position in the list with (row – 1) x 4 + column, so the second cell of the second row picks name number 6. To use a different row width, change the 4 to that number.

Example 2: Replace the #N/A in the Last Row With pad_with

Now let’s see what happens when the list doesn’t divide evenly.

Below is a list of 10 kids going on a field trip, and I want to split them into carpools of three, with the carpools in column C.

Here is the formula:

=WRAPROWS(A2:A11,3)

Ten kids in rows of three gives three full rows and a last row with just Mia. There’s nothing left to fill the other two spots in that row, so Excel puts #N/A in D5 and E5.

That #N/A doesn’t mean anything is wrong with your data. It’s just the default padding, but it looks messy in a list you want to share or print.

To fix this, use the third argument. Here is the formula in cell C8, below the first result, that fills the empty spots with blanks instead:

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

The “” tells WRAPROWS to use an empty text string for the padding, so D11 and E11 now look blank. You can use any other value here too, such as “-” or “TBD”.

Note that those padded cells aren’t truly empty, since they hold empty text. So if you count the result with the COUNTA function, it counts them too (12 instead of 10).

Example 3: Wrap a Horizontal Row into Quarters

WRAPROWS works the same way when the values sit in a row instead of a column.

Below I have a year of monthly electric bills in a single row, and I want to show them by quarter in the table below it, with three months in each row.

Here is the formula:

=WRAPROWS(B2:M2,3)

The formula in B5 takes the 12 bills in B2:M2 and puts three in each row. So January to March land in row 5 (Q1), April to June in row 6 (Q2), and so on down to Q4.

Only the numbers come from the formula. The Quarter labels in A5:A8 and the Month 1 to Month 3 headers are typed in, so the grid reads like a small report.

Example 4: Turn a Stacked List into a Table

Here’s a situation you can run into when you copy data from a web page or a PDF.

Below is a product listing that got pasted into a single column, with each product’s name, price, and rating stacked one under the other, and I want to turn it into a proper table in columns C to E.

Here is the formula:

=WRAPROWS(A2:A19,3)

Each product takes up exactly three cells in the list (name, price, rating). So a wrap_count of 3 puts each product’s three values into one row, and the 18 cells in A2:A19 become a six-row table.

WRAPROWS doesn’t change the values, so the prices and ratings stay numbers because they were numbers in column A. That means you can format the Price column as currency or use the values in other formulas.

Important: This only works when every record has the same number of cells. If one product is missing its rating, every value after it moves one column over, so product names show up in the Rating column and prices in the Product column.

A quick way to check is to count the filled cells in the list and divide by the number of fields. Here, 18 cells divided by 3 gives 6, a whole number, so every product has all three values.

Example 5: Use WRAPROWS With FILTER to Split a List into Teams

Now let’s look at something a bit more interesting, where WRAPROWS works on the result of the FILTER function.

Below is a list of volunteers and whether each one is available, and I want to put only the available volunteers into teams, using the team size in cell E2, with the teams in column G.

Here is the formula:

=WRAPROWS(FILTER(A2:A13,B2:B13="Yes"),E2,"")

How this formula works:

• FILTER(A2:A13,B2:B13=”Yes”) returns only the volunteers whose Available value is Yes. That’s 8 of the 12 names.
• WRAPROWS then wraps those 8 names into rows of 3 (the team size in E2), so you get two full teams and a third team with Kyle and Eric.
• “” fills the last empty spot with a blank instead of #N/A.

Since the team size comes from E2, you can change it without touching the formula. Type 4 in E2, and the result redraws as two teams of four, spilling into column J, so keep that column empty.

The value in E2 needs to be 1 or more, since a blank cell or a 0 returns a #NUM! error. A decimal works, but it’s cut down to a whole number, so 3.5 gives teams of 3.

Example 6: Change the Width of a Grid With TOCOL

Here’s another practical scenario where the data is already in a grid.

Below is a classroom seating chart with five desks in each row (one desk is empty), and I want to rearrange the students into rows of four desks, starting in column H.

Here is the formula:

=WRAPROWS(TOCOL(B2:F4,1),4,"")

How this formula works:

• WRAPROWS can’t wrap the B2:F4 grid directly, because it needs a single row or column. So the grid has to be flattened first.
• TOCOL(B2:F4,1) reads the grid row by row and stacks all the names into one column. The 1 tells it to skip blank cells, so the empty desk in D3 is dropped.
• WRAPROWS then rewraps those 14 names into rows of four, and “” keeps the two leftover desks in the last row blank.

If you leave out the 1 in TOCOL, the empty desk comes through as a 0. You would then see a 0 sitting in the new seating chart as if it were a student.

Example 7: Convert Comma-Separated Text into a Table With TEXTSPLIT

Let’s step it up with one more combination, this time with the TEXTSPLIT function.

Below is a weekly workout plan that was copied as one line of text, with each day followed by its workout, and I want to turn it into a Day and Workout table in columns C and D.

Here is the formula:

=WRAPROWS(TEXTSPLIT(A2,","),2)

How this formula works:

• TEXTSPLIT(A2,”,”) splits the text in A2 at every comma, which gives 12 separate values in a single row (Mon, Yoga, Tue, Spin, and so on).
• WRAPROWS(…,2) then puts those values two at a time into each row, so every day sits next to its workout.

You can’t get this table from TEXTSPLIT alone, because the same comma separates a day from its workout and one day from the next. WRAPROWS is what turns the single row of pieces back into pairs.

So that’s how you can use the WRAPROWS function in Excel to turn a single list into a neat grid, whether it’s a plain list, a stacked export, a filtered list, or a line of text.

If you’d rather fill the values down each column first, use the WRAPCOLS function instead.

I hope you found this article helpful.

List of All Excel Functions

Other Excel Articles You May Also Like:

• Split Text into Multiple Rows in Excel
• Transpose Multiple Rows into One Column
• SORT Function in Excel
• How to use ARRAYTOTEXT in Excel
• RANDARRAY in Excel
• TOROW Function in Excel
• CHOOSEROWS function
• EXPAND Function in Excel