Have you ever received a long list of data in one Excel column and wished you could arrange it into several columns automatically?
For example, you have a list of nine names in Column A, but you want to display them in three columns instead of one long list. You could copy and paste the names manually, but Excel has a function that can do this for you in seconds: WRAPROWS.
In this guide, you will learn how WRAPROWS works, how to use it with simple examples, and how to avoid common mistakes.
What Is the WRAPROWS Function?
WRAPROWS is an Excel function that rearranges a single row or column of values into multiple rows, with a specified number of values in each row.
Imagine you have nine names in Column A. If you want three names per row, WRAPROWS can arrange them into three columns and three rows automatically.
1. Understand the WRAPROWS Formula
The basic formula is:
- vector: The row or column containing the original data.
- wrap_count: The number of values you want in each output row.
- pad_with: Optional. The value to display in any remaining cells if the final row is incomplete. If omitted, Excel returns #N/A in those cells.
2. Example One: Convert One Column into Three Columns
Let's start with a simple example. Imagine the names below are stored in Column A, from cell A2 to A10.
| Row | Column A | Column B | Column C | Column D | Column E |
|---|---|---|---|---|---|
| 1 | Name | ||||
| 2 | Adam | ||||
| 3 | Ben | ||||
| 4 | Chris | ||||
| 5 | Daniel | ||||
| 6 | Emma | ||||
| 7 | Farah | ||||
| 8 | Grace | ||||
| 9 | Henry | ||||
| 10 | Isabel |
Step 1: Click cell C2. This is where you want the rearranged list to begin.
Step 2: Enter this formula in cell C2:
Step 3: Press Enter. Excel will automatically fill the results across Columns C, D and E.
The result should look like this:
| Row | Column C | Column D | Column E |
|---|---|---|---|
| 1 | Result 1 | Result 2 | Result 3 |
| 2 | Adam | Ben | Chris |
| 3 | Daniel | Emma | Farah |
| 4 | Grace | Henry | Isabel |
3. Example Two: Choose How Many Columns You Want
The second argument controls how many values appear in each output row. You can change it depending on your needs.
| Formula | What it does | Output shape for 9 values |
|---|---|---|
=WRAPROWS(A2:A10,2) | 2 values per row | 5 rows × 2 columns |
=WRAPROWS(A2:A10,3) | 3 values per row | 3 rows × 3 columns |
=WRAPROWS(A2:A10,4) | 4 values per row | 3 rows × 4 columns |
For example, if you enter =WRAPROWS(A2:A10,4) in cell C2, Excel will place four names in each row. Since there are nine names, the last row will have only one name and the remaining three cells will show #N/A by default.
4. Example Three: Avoid #N/A in the Last Row
What if your list does not divide evenly into the number of columns you choose? You can use the optional third argument, pad_with, to decide what appears in the remaining cells.
For example, enter this formula in C2:
The double quotation marks tell Excel to use blank text in the remaining cells instead of displaying #N/A.
| Row | Column C | Column D | Column E | Column F |
|---|---|---|---|---|
| 2 | Adam | Ben | Chris | Daniel |
| 3 | Emma | Farah | Grace | Henry |
| 4 | Isabel |
This is useful when preparing reports, contact lists or data layouts where you do not want error values to appear in the final row.
5. Example Four: Wrap a Horizontal Row of Data
WRAPROWS can also rearrange data that is already stored horizontally in a single row.
Suppose cells A2:I2 contain these values:
| Row | Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H | Column I |
|---|---|---|---|---|---|---|---|---|---|
| 2 | 101 | 102 | 103 | 104 | 105 | 106 | 107 | 108 | 109 |
To display three values per row, enter this formula in cell A4:
Excel will rearrange the nine values into three columns and three rows, beginning at A4.
| Row | Column A | Column B | Column C |
|---|---|---|---|
| 4 | 101 | 102 | 103 |
| 5 | 104 | 105 | 106 |
| 6 | 107 | 108 | 109 |
6. Practical Uses for WRAPROWS
- Product lists: Arrange a long list of products into several columns for a compact display.
- Names and contacts: Turn a long list of names into a grid for easier viewing.
- Report preparation: Rearrange a single list into a consistent row-and-column layout.
- Dashboard layouts: Present a sequence of values in a more compact format.
- Imported data: Reshape a one-dimensional list before using it in another part of your worksheet.
7. Common WRAPROWS Mistakes
Using a two-dimensional range
The vector argument should be a single row or a single column. A rectangular range containing multiple rows and columns may return a #VALUE! error.
Choosing a wrap count below 1
The wrap_count must be at least 1. A value of zero or a negative number will return an error.
Not leaving enough room for the results
WRAPROWS returns a dynamic array. Make sure the cells where the result needs to appear are empty. If existing data blocks the output, Excel may display a #SPILL! error.
Using an Excel version without WRAPROWS
WRAPROWS is available in Microsoft 365 and Excel 2024. If your Excel version does not support it, you may need another formula or a different method to rearrange the data.
=WRAPROWS(A2:A10;3).WRAPROWS vs WRAPCOLS
Both functions reshape a one-dimensional list, but they arrange the values in different directions.
- WRAPROWS: Fills values across each row, then continues on the next row.
- WRAPCOLS: Fills values down each column, then continues in the next column.
Choose WRAPROWS when you want to control how many values appear in each row.
Final Thoughts
WRAPROWS is a handy Excel function for turning a long list into a compact table without manually copying and pasting each value. Just choose the source range, decide how many values you want in each row, and let Excel arrange the results.
Start with the formula =WRAPROWS(A2:A10,3) and try changing the number 3 to 2 or 4 to see how the layout changes.
Have you used WRAPROWS in your Excel work? Share your experience or questions in the comments below!
0 Comments