Data Import & Export

Split One Excel Column into Multiple Columns of 50 Rows

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

The user wants to reshape a long list in one column into multiple columns containing up to 50 entries each.

Split One Excel Column into Multiple Columns of 50 Rows
Product
Microsoft Excel
Device & OS
not provided
Scenario
A list such as A2:A70001 needs to be arranged sequentially across columns, with 50 rows per column.
Observed behavior
The desired output places the first 50 entries in the first column, the next 50 in the second, and so on.
Before you start

Identify the exact source range, excluding any header, and choose an empty output area that does not overlap it. A list of 70,000 entries requires 50 rows and 1,400 output columns.

Solution 1Recommended

Use WRAPCOLS to split the list automatically

Use this method in an Excel version that supports WRAPCOLS to create the entire output with one formula.

1
Select an empty cell

Click B2 or another empty cell outside the source range. Make sure there is room for the formula to spill across the required rows and columns.

2
Enter the formula

Type =WRAPCOLS(A2:A70001,50,"") and press Enter. Replace A2:A70001 with your actual list range.

3
Check the output

Verify that the first output column contains the first 50 entries and the next column starts with the 51st entry. The empty-string argument pads any unused cells in the final column.

Use WRAPCOLS to split the list automatically
Function availability: WRAPCOLS requires a compatible Excel version. If Excel does not recognize the function, use the INDEX method instead.
WPS Spreadsheet

Arrange a Long List into 50-Row Columns in WPS Spreadsheet

Use an INDEX formula in WPS Spreadsheet and fill it down and across to reshape a list into columns of 50 entries.

  1. 1. Open the spreadsheet: Open your Excel file in WPS Spreadsheet and identify the source list, such as A2:A70001.
  2. 2. Enter the formula: Click B2 and enter =IF(ROWS($B$2:B2)+(COLUMNS($B$2:B2)-1)*50>ROWS($A$2:$A$70001),"",INDEX($A$2:$A$70001,ROWS($B$2:B2)+(COLUMNS($B$2:B2)-1)*50)). Adjust the references if your source or output starts elsewhere.
  3. 3. Fill the output: Drag the fill handle from B2 down to B51, then select B2:B51 and drag right across enough columns for the list.
  4. 4. Save the workbook: Choose File > Save As and select an Excel workbook format such as XLSX.
Arrange list entries sequentially in 50-row columnsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and fill handlesWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How many columns do I need for 70,000 entries?

You need 1,400 columns because 70,000 divided by 50 is 1,400. Choose an empty output area with enough space before entering or filling a formula.

What happens if the list does not divide evenly by 50?

The final column contains the remaining entries. Use =WRAPCOLS(A2:A70001,50,"") to pad unused cells with empty strings, or use the INDEX formula shown above.

Why does WRAPCOLS show a spill error?

The output area may contain existing data. Clear or move entries from the cells where the result needs to spill, or enter the formula in a different empty area.

Can I change the number of entries per column?

Yes. In WRAPCOLS, replace 50 with the desired count. In the INDEX formula, replace the multiplication by 50 with that count, then fill the corresponding number of rows.