Cellwise Start free

Dynamic arrays function

Excel WRAPROWS function

WRAPROWS takes a single row or column of values and cuts it into rows of a set length, like text wrapping at the edge of a page. It is the fix for data pasted as one long column where every few lines belong to one record. WRAPCOLS does the same but fills columns from top to bottom.

=WRAPROWS(vector, wrap_count, [pad_with])

Arguments

vector
One row or one column of values. A range with several rows and columns returns #VALUE!.
wrap_count
How many values go in each row. Less than 1 returns #NUM!.
pad_with
Optional. What fills the gaps in the last row when the values run out. #N/A if left out.

WRAPROWS examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

A
1Pasted list
2Ana Ruiz
3Sales
4[email protected]
5Ben Cole
6Support
7[email protected]
8Cy Park
9Finance
10[email protected]
11Dee Lund
12Sales
  1. =WRAPROWS(A2:A10,3)

    Result: spills 3 rows × 3 columns

    Ana RuizSales[email protected]
    Ben ColeSupport[email protected]
    Cy ParkFinance[email protected]

    Every three lines become one row: name, team and email for Ana, Ben and Cy.

  2. =WRAPROWS(A2:A12,3)

    Result: spills 4 rows × 3 columns

    Ana RuizSales[email protected]
    Ben ColeSupport[email protected]
    Cy ParkFinance[email protected]
    Dee LundSales#N/A

    Dee’s email is missing, so her row runs out after two values and the gap shows #N/A.

  3. =WRAPROWS(A2:A12,3,"")

    Result: spills 4 rows × 3 columns

    Ana RuizSales[email protected]
    Ben ColeSupport[email protected]
    Cy ParkFinance[email protected]
    Dee LundSales

    The third argument fills the gap instead, here with empty text so the cell looks blank.

  4. =TRANSPOSE(WRAPROWS(A2:A10,3))

    Result: spills 3 rows × 3 columns

    Ana RuizBen ColeCy Park
    SalesSupportFinance
    [email protected][email protected][email protected]

    Each record becomes a column of three instead, for a side-by-side layout. =WRAPCOLS(A2:A10,3) gives the same result in one step, because it fills downwards.

How to use WRAPROWS

  1. Check how many lines make up one record (3 here).
  2. Click a cell with room for the table to spill.
  3. Type =WRAPROWS( then select the list, a comma, and that number.
  4. Optionally add a pad value such as "" for a short last record, then press Enter.

Common WRAPROWS mistakes

A record with a missing line

WRAPROWS counts positions, not meaning. One missing line shifts every record after it into the wrong columns. Check that the list length is a multiple of wrap_count.

Passing a table

WRAPROWS takes one row or one column. Flatten a table first with TOCOL, then wrap it.

Older Excel

WRAPROWS and WRAPCOLS need Excel for Microsoft 365, Excel 2024 or Excel for the web.

WRAPROWS FAQ

How do I convert one column into multiple columns in Excel?

Use =WRAPROWS(A2:A100,3) to make rows of three, or =WRAPCOLS(A2:A100,20) to make columns of twenty. In older versions use INDEX with ROW and COLUMN arithmetic.

What does #N/A mean in a WRAPROWS result?

The values ran out before the last row was full. Add a third argument, such as "" or 0, to fill those cells instead.

What is the difference between WRAPROWS and WRAPCOLS?

WRAPROWS fills each row left to right before starting the next row. WRAPCOLS fills each column top to bottom before starting the next column.

Related functions

Updated