Split a Column into Fixed-Size Blocks in Excel
Question details
Arrange a column of values into groups with a chosen number of rows per column.

- 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.
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.
Use WRAPCOLS to Create Fixed-Size Blocks
Use this dynamic array formula in Microsoft 365 to rearrange a column into blocks automatically.
Click an empty cell outside the source range, such as C1.
Type =WRAPCOLS(A1:A100,25,"") and press Enter. Replace A1:A100 with your source range and 25 with your preferred block size.
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 INDEX to Split a Column Without WRAPCOLS
Use this formula when WRAPCOLS is unavailable and you can fill the output range manually.
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. Open your spreadsheet: Open WPS Spreadsheet and use File > Open to select your workbook.
- 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. Fill the blocks: Copy C1, select C1:F25, and paste to create four blocks of 25 rows.
- 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.

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.




