How to convert one column into multiple columns in Excel

Records that repeat every 3 rows: WRAPROWS

This is the classic mess: you copy a contact list out of a PDF, an email or a web page, and it lands in a single column. Column A has four clients, each as three cells in a row: name, phone, email. You want a table with one client per row.

WRAPROWS(vector, wrap_count, [pad_with]) takes a one-column (or one-row) list and starts a new row every wrap_count values. Each record here is 3 cells, so the wrap count is 3. To add headers in the same formula, stack a row of labels on top with VSTACK and an array constant.

WRAPROWS, WRAPCOLS and VSTACK are in Excel for Microsoft 365, Excel for the web and Excel 2024. Excel 2021 and older show #NAME?; use the INDEX formula at the end of this page there.

WRAPROWS or WRAPCOLS: fill across or fill down

The two functions differ in which way they fill. WRAPROWS fills across: values 1, 2, 3 go in the first row. WRAPCOLS fills down: values 1, 2, 3 go in the first column. In both, wrap_count is how many values go in each row (WRAPROWS) or each column (WRAPCOLS).

That second point catches people. Column C is a class list of 11 children to print in three columns, reading down. =WRAPCOLS(C2:C12,3) does not give three columns; it gives three rows, with four columns. For WRAPCOLS, the number you pass is the column height. Work it out from the count: ROUNDUP(COUNTA(C2:C12)/3,0) is 4 rows, which gives exactly three columns. The formula keeps working as names are added, if you extend the range.

Records are the other way round: for the contact list, WRAPCOLS(A2:A13,3) puts each client in a column, with names in row 1, phones in row 2 and emails in row 3. Use WRAPROWS for records and WRAPCOLS for newspaper-style columns.

Hide the #N/A in the last row with pad_with

11 names do not split evenly into rows of 3. The last row has only Jack and Kira, and WRAPROWS fills the gap with #N/A by default. That looks like a broken formula on a printout, and any SUM or MAX over the result returns #N/A too.

The third argument, pad_with, sets what fills the gap. "" leaves it looking empty; 0 or "-" work too. Note that it only fills the gaps at the end. If a cell in the source list is empty, WRAPROWS shows it as 0, not as blank.

Blank separator rows between records

Pasted lists often have an empty row after each record, like column E. Wrap it by 3 and everything after the first blank drifts: the second row starts with a 0 (the blank) and Jon Park's email ends up in the third row. Every record after the first separator is off by one more cell.

If every record is followed by exactly one blank, you can wrap by 4 and drop the last column: =DROP(WRAPROWS(E2:E12,4,""),,-1). Wrap by 4 alone and that fourth column is full of zeros. The safer fix is to remove the blanks first, so the separators do not matter at all.

Check that no record is missing a field

The real danger is silent. In column G, Beth Moss has no phone number, so her record is two cells instead of three. WRAPROWS does not know that. Her email lands in the Phone column, Carl Ito lands in the Email column, and his details slide into the next row. No error appears.

Two quick checks catch it. =MOD(COUNTA(G2:G9),3) must be 0 when every record has 3 fields; here it returns 2, because the list has 8 cells. And a test on a column that has a pattern, like the @ in an email, shows where the drift starts. The first FALSE is the first broken record: fix the source list (type the missing phone, or insert an empty cell) and the table rights itself.

Excel 2019, 2016 and older: an INDEX formula

Without WRAPROWS, INDEX can pick the right cell for each spot in the table. Type this in the top-left cell of the table, fill it right 3 columns and then down 4 rows. ROWS(A$1:A1) counts the table row and COLUMNS($A1:A1) the table column, so the position in the list is (row - 1) x 3 + column. Change the 3 to the number of fields per record.

Filled down past the last record (row 5 here) it returns #REF!, because position 13 is past the end of a 12-cell list. Empty source cells come back as 0. Once the table is right, copy it and use Paste Special > Values if you want to delete the original column.

Which method to pick, and what this is not

Use WRAPROWS for records (name, phone, email) and WRAPCOLS for a long list printed in columns. Add TOCOL(range,1) inside when the list has blank separator rows, and always run the MOD check before trusting a table of pasted records. Use INDEX in Excel 2021 and older.

The result is a live formula: edit the source and the table changes, and you cannot type over single cells of a spill. Copy it and Paste Special > Values when you need a normal table to sort or edit. If each cell holds several values, like "Dana Whitfield, (555) 201-3344", you are splitting text, not wrapping a list: use TEXTSPLIT or Data > Text to Columns. And if you want one column turned into one row, TRANSPOSE is the function.