Function Problems

Wrap a Two-Column Dynamic Array Horizontally in Excel

Steve KSteve K Nov 12, 2026 868 views

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.

Wrap a Two-Column Dynamic Array Horizontally in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Choose an empty output cell

Click a blank cell outside the B1# spill range with enough empty columns available for the wrapped result.

2
Enter the horizontal wrapping formula

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))))))

3
Adjust the source or group size when needed

Replace B1# with the top-left reference of your spilled source array, or change wrapcount,13 to another number of rows per group.

Use LET and REDUCE to wrap each 13-row group into new columns
How the final group is handled: If the last group has fewer than 13 rows, Excel returns the remaining rows and pads unmatched positions in the horizontal result with #N/A values.
WPS Spreadsheets

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. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the spilled source array.
  2. 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. 3. Select a clear destination cell: Click an empty cell outside the source output area, leaving enough space for each two-column group.
  4. 4. Insert the wrapping formula: Enter the LET formula that uses HSTACK, CHOOSEROWS, SEQUENCE, and REDUCE, then press Enter to spill the horizontal layout.
Organize two-column dynamic-array records into side-by-side row groups.Use spill formulas to update the layout when source data changes.Open and edit Microsoft Excel .xlsx workbooks with familiar spreadsheet tools.Use a lightweight, free office suite with an Excel-like worksheet interface.
microsoft office alternative - wps office

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.