Function Problems

How to Transpose a List into Two Columns in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to rearrange a vertical, single-column dataset into a two-column layout where every two consecutive cells are placed into a single row.

Product: Excel
Device & OS: not provided
Scenario: Formatting and reorganizing vertical lists into structured, multi-column tables for better readability or data processing.
Observed behavior: Data is contained entirely in a single vertical list and requires a formula or function to dynamically split it into two columns.

Before you start

Verify the exact cell range of your vertical list and ensure you have enough empty adjacent columns where the transposed data can populate without overwriting existing information.

Solution 1Recommended

Use the WRAPROWS Function (Microsoft 365 & Office 2024)

This is the fastest and most dynamic method to wrap a vertical list into multiple columns using built-in array functions.

The WRAPROWS function is a dynamic array formula introduced in Microsoft 365 and Office 2024. It takes a one-dimensional list and automatically wraps it into rows based on the column count you specify.

Select the destination cell

Click on an empty cell in your worksheet where you want the top-left corner of your new two-column table to appear.

Enter the WRAPROWS formula

Type =WRAPROWS(B3:B10, 2) into the formula bar. Replace B3:B10 with the actual cell references containing your vertical list. The number 2 dictates that the data should be split into two columns.

Apply the function

Press Enter. The formula will automatically spill the data across two columns, placing every two consecutive cells from the original list into a new row.

Handling Odd Numbers of Items: If your list does not divide evenly by 2, the last cell will display an #N/A error. You can add a third argument to pad it with blanks by modifying the formula to: =WRAPROWS(B3:B10, 2, "").

Efficient Data Formatting in WPS Office

Easily Reorganize and Transpose Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools and comprehensive formula support, making it simple to transpose and manage array data within a highly familiar interface.

1. Open your spreadsheet: Launch WPS Office, open WPS Spreadsheet, and load the document containing the vertical data list.
2. Input the array formula: Click on a blank destination cell and enter the WRAPROWS formula targeting your specific list range and specifying 2 columns.
3. Format the output table: Press Enter to spill the array, then use the Home tab to quickly add borders, adjust column widths, and format the newly transposed two-column data.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsSupports advanced array functions for effortless data reorganizationFamiliar ribbon interface guarantees a seamless transition and zero learning curveLightweight architecture ensures fast performance even on older devices

microsoft office alternative - wps office

Frequently Asked Questions

How can I transpose a list into two columns without Microsoft 365?

In older versions of Excel, you can use the INDEX and ROW functions. In your destination cell, type =INDEX($B$3:$B$10, (ROW(A1)-1)*2+1) for the first column, and =INDEX($B$3:$B$10, (ROW(A1)-1)*2+2) for the second column, then drag the fill handle down to populate the rows.

Can I split a vertical list into three or four columns instead of two?

Yes. When using the WRAPROWS function, the second argument dictates the number of columns. To split the list into three columns, simply change the formula to =WRAPROWS(B3:B10, 3).

How do I reverse this and turn a two-column list back into a single column?

You can use the TOCOL function in newer Excel versions. Selecting a blank cell and typing =TOCOL(A1:B10) will instantly convert a multi-column range back into a single vertical list.

Excel & Data Problems