Function Problems

Split a Column into Fixed-Size Blocks in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Arrange a column of values into groups with a chosen number of rows per column.

How to Split a Column into Fixed-Size Blocks in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to reorganize a column array into blocks, such as groups of 25 cells.
Observed behavior
The desired output places up to 25 values in each column before continuing in the next column.
Before you start

Identify the source range and the number of rows you want in each block. Choose an empty output area with enough columns for the results.

Solution 1Recommended

Use WRAPCOLS to Create Fixed-Size Blocks

Use this dynamic array formula in Microsoft 365 to rearrange a column into blocks automatically.

1
Select an empty output cell

Click an empty cell outside the source range, such as C1.

2
Enter the formula

Type =WRAPCOLS(A1:A100,25,"") and press Enter. Replace A1:A100 with your source range and 25 with your preferred block size.

3
Check the result

Review the spilled output: each column contains up to 25 source values. Clear any cells blocking the output if Excel displays a #SPILL! error.

Use WRAPCOLS to Create Fixed-Size Blocks
Use a spilled source array: If the source is a spilled array beginning in A1, use A1# instead of A1:A100. The empty-string argument leaves unused cells in the final block blank.
Split data in WPS Spreadsheet

Arrange Column Values into Blocks in WPS Spreadsheet

Use an INDEX formula in WPS Spreadsheet to place values from one column into fixed-size blocks across multiple columns.

  1. 1. Open your spreadsheet: Open WPS Spreadsheet and use File > Open to select your workbook.
  2. 2. Enter the formula: With source values in A1:A100, select C1 and enter =IFERROR(INDEX($A$1:$A$100,(COLUMN()-COLUMN($C$1))*25+ROW()-ROW($C$1)+1),"").
  3. 3. Fill the blocks: Copy C1, select C1:F25, and paste to create four blocks of 25 rows.
  4. 4. Adjust and save: Change the source range and block size as needed, fill the required output area, and use File > Save As to save the workbook.
Arrange values into blocks of your chosen sizeEdit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill operations
QA img-9

Frequently Asked Questions

How do I change the number of rows in each block?

In =WRAPCOLS(A1:A100,25,""), replace 25 with the number of rows you want in each output column.

Can I split a spilled array instead of a fixed range?

Yes. If the spilled array begins in A1, enter =WRAPCOLS(A1#,25,"") in an empty output cell.

Why does WRAPCOLS return a #SPILL! error?

Cells in the intended output area may contain data. Clear the blocking cells or enter the formula in a larger empty area.

What can I use if my Excel version does not have WRAPCOLS?

Enter the INDEX and IFERROR formula shown above in C1, then copy it across an output area with the desired number of rows and enough columns for your data.