Wrap a Two-Column Dynamic Array Horizontally in Excel
Question details
Create one formula that rearranges a two-column dynamic array into horizontal groups of 13 rows while preserving both columns in each group.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A 27-row, two-column spilled array starts at B1, and the user wants each 13-row block displayed in the next two columns.
- Observed behavior
- The desired output places the first 13 rows in the first two columns, the next 13 rows in the next two columns, and the remaining row in a final pair of columns.
Make sure the source range is a spilled dynamic array beginning at B1 and that the target spill area is empty. This formula requires a current Excel version that supports LET, REDUCE, HSTACK, CHOOSEROWS, SEQUENCE, and DROP.
Use LET and REDUCE to wrap each 13-row group into new columns
Use this method when the source is a two-column spilled array and you want one dynamic formula that automatically expands as the source array changes.
The formula calculates the number of 13-row groups, selects each group from the original spill range, and appends the groups horizontally. Each group retains its original two-column structure.
Click a blank cell outside the B1# spill range with enough empty columns available for the wrapped result.
Paste this formula and press Enter: =LET(array,B1#,wrapcount,13,totalRows,ROWS(array),groups,SEQUENCE(ROUNDUP(totalRows/wrapcount,0),,0),first,CHOOSEROWS(array,SEQUENCE(MIN(wrapcount,totalRows))),REDUCE(first,DROP(groups,1),LAMBDA(a,b,HSTACK(a,CHOOSEROWS(array,SEQUENCE(MIN(wrapcount,totalRows-b*wrapcount),,b*wrapcount+1))))))
Replace B1# with the top-left reference of your spilled source array, or change wrapcount,13 to another number of rows per group.

Arrange Dynamic Array Results into Horizontal Groups with WPS Spreadsheets
WPS Spreadsheets can help you build and manage spill-based worksheet layouts. Use a current version that supports the required dynamic-array functions, enter the wrapping formula in an empty output area, and let the result update when the source array changes.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the spilled source array.
- 2. Verify the source spill range: Click the source formula cell and confirm that its results spill from B1 or update the formula reference for your worksheet.
- 3. Select a clear destination cell: Click an empty cell outside the source output area, leaving enough space for each two-column group.
- 4. Insert the wrapping formula: Enter the LET formula that uses HSTACK, CHOOSEROWS, SEQUENCE, and REDUCE, then press Enter to spill the horizontal layout.

Frequently Asked Questions
Why does the formula use B1# instead of a fixed range?
B1# refers to the entire dynamic array spilled from B1. It automatically includes added or removed rows, unlike a fixed reference such as B1:C27.
How do I change the formula to use 10 rows per horizontal group?
In the formula, replace wrapcount,13 with wrapcount,10. Each output block will then contain up to 10 rows and two columns.
Why are #N/A values shown below the final group?
The last group has fewer rows than the earlier groups. HSTACK aligns arrays of different heights by filling missing positions with #N/A; this is expected behavior.
Can I replace the #N/A values in the final group with blanks?
Yes. Wrap the full formula in IFERROR, such as =IFERROR(your_formula,""). This changes padding errors in the incomplete final group to blank cells.
What Excel versions support this horizontal wrapping formula?
You need a current Microsoft 365 Excel version with dynamic-array support for LET, REDUCE, HSTACK, CHOOSEROWS, SEQUENCE, and DROP. Older perpetual Excel versions may not include all of these functions.




