Function Problems

How to Split One Excel Column into Multiple Columns with a Formula

Khadija Khan Oct 9, 2026 869 views

Question details

The user needs to rearrange a long, single column of data into a multi-column grid, specifically breaking a 1,000-row column into 10 columns of 100 rows each.

How to Split One Excel Column into Multiple Columns with a Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reorganizing a massive single-column dataset into a more compact and readable multi-column layout.
Observed behavior: The data currently sits in a single 1,000-row column and must automatically flow sequentially into adjacent columns once it hits a 100-row threshold.

Before you start

Ensure that the destination cells where your formula will expand are completely empty, as existing data will block dynamic array formulas and cause a #SPILL! error.

Solution 1Recommended

Use the WRAPCOLS Function (Microsoft 365)

The quickest and most straightforward method to spill a single column into a designated number of rows per column using modern dynamic arrays.

The WRAPCOLS function is specifically designed to convert a one-dimensional array or single column into a two-dimensional grid. By specifying the maximum number of items per column, Excel automatically determines how many columns are needed.

Select a target cell

Click on an empty cell where you want the new multi-column grid to start (e.g., cell C1).

Enter the WRAPCOLS formula

Type =WRAPCOLS(A1:A1000, 100) into the formula bar. Replace A1:A1000 with your actual data range, and 100 with the exact number of rows you want in each column.

Apply the formula

Press Enter. The formula will automatically spill the data horizontally across 10 columns, placing 100 items into each column.

Use the WRAPCOLS Function (Microsoft 365)

Handling Empty Spaces: If your dataset does not perfectly divide into the specified row count, the remaining cells will display #N/A. You can add a third argument to handle this, like =WRAPCOLS(A1:A1000, 100, "") to leave them blank.

Solution 2

Use the MAKEARRAY and INDEX Functions

An alternative dynamic-array approach if you want to strictly define both the exact row and column dimensions for your output grid.

Efficient Data Management with WPS

How to Split Data into Multiple Columns in WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools and high compatibility with complex Excel formulas, allowing you to easily reshape single columns into multi-column grids even if you prefer standard INDEX formulas.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your single column of data.
2. Enter the INDEX formula: Select an empty cell and enter: =INDEX($A$1:$A$1000, ROWS($1:1) + (COLUMNS($A:A)-1)*100)
3. Fill rows downwards: Click the small square at the bottom-right of the cell (fill handle) and drag it down to fill exactly 100 rows.
4. Fill columns rightwards: While those 100 rows are still selected, click the fill handle again and drag it to the right across 10 columns to populate the entire grid.

100% compatible with Microsoft Excel formulas and .xlsx filesLightweight and fast, running smoothly on Windows, Mac, and LinuxIncludes advanced data formatting tools and a massive built-in function libraryFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when using the WRAPCOLS formula?

A #SPILL! error occurs when there is existing data blocking the area where the formula needs to expand. Ensure that the 10 columns and 100 rows next to your formula cell are completely empty.

Can I split the column horizontally instead of vertically?

Yes, you can use the WRAPROWS function instead of WRAPCOLS. WRAPROWS will fill the data across a specified number of columns row by row, rather than filling columns sequentially.

Will this formula automatically update if I add more data to the original column?

If your original data is formatted as an Excel Table, or if you use dynamic named ranges in your formula (like =WRAPCOLS(Table1[Data], 100)), it will update automatically. If you used hardcoded references like A1:A1000, you will need to manually adjust the range when adding new data.

Excel & Data Problems