Combine Two Spilled Excel Lists with a Blank Row Between Them
Question details
The user needs to merge two dynamic Excel lists that vary in length (1 to 25 rows) into a single spill range, separated by exactly one blank row without leaving excessive empty spaces.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Generating a cohesive report from two separate dynamic arrays that spill to different lengths, requiring a unified output.
- Observed behavior
- Without the correct function, combining the lists either leaves varying empty gaps between the arrays or fails to update dynamically as the source lists grow or shrink.
Check your Excel version to see if modern dynamic array functions like VSTACK are available, as this will determine whether you can use a built-in function or need to create a custom LAMBDA formula.
Combine Lists Using the VSTACK Function
The most efficient way to stack dynamic arrays vertically, available in modern versions of Excel.
If you are using a newer version of Excel (Microsoft 365 or Excel 2024), the VSTACK function handles array stacking natively. You can insert a blank array directly into the function arguments to force a blank row.
Click on the cell where you want the combined list to begin spilling.
Type the formula =VSTACK(Array1, "", Array2) if working with a single column list. For example, =VSTACK(A2#,"", C2#).
If your arrays have multiple columns (e.g., 2 columns), you need to provide an inline array for the blank row that matches the column count: =VSTACK(A2:B10, {"",""}, D2:E15).

Create a Custom LAMBDA Function (For Older Versions)
Use a combination of LET, SEQUENCE, INDEX, and IFERROR to stack arrays vertically when VSTACK is not supported.
Combine Dynamic Arrays Seamlessly in WPS Spreadsheet
WPS Office Spreadsheet provides excellent support for advanced array formulas, allowing you to manage, analyze, and combine dynamic data ranges effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dynamic lists.
- 2. Select a target cell: Click an empty cell with sufficient space below it to accommodate the combined data.
- 3. Input the array formula: Enter the VSTACK formula with a blank delimiter: =VSTACK(Range1, {""}, Range2).
- 4. Apply and spill: Press Enter to instantly generate the combined dynamic list with a perfect blank row separator.

Frequently Asked Questions
Why do I get a #SPILL! error when combining my lists?
A #SPILL! error occurs when the dynamic formula needs room to expand (spill), but there is existing data blocking it. Clear any text, spaces, or formatting in the cells below your formula to allow the array to populate fully.
Can I combine arrays with different numbers of columns using VSTACK?
Yes, but VSTACK will automatically pad the shorter array with #N/A errors to match the column count of the wider array. You can wrap your VSTACK formula in an IFERROR function, like =IFERROR(VSTACK(...), ""), to display blank cells instead.
Are LAMBDA and VSTACK available in all versions of Excel?
No, LAMBDA and newer dynamic array functions like VSTACK and TOCOL are generally only available in Microsoft 365 subscriptions and Excel 2021 or later. If you are using Excel 2019 or earlier, you will need to rely on complex INDEX, MATCH, and ROW workarounds.




