Function Problems

Combine Two Spilled Excel Lists with a Blank Row Between Them

Maira MehtabMaira Mehtab Oct 22, 2026 869 views

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.

How to Combine Two Spilled Excel Lists with a Blank Row Between Them
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell where you want the combined list to begin spilling.

2
Enter the VSTACK formula

Type the formula =VSTACK(Array1, "", Array2) if working with a single column list. For example, =VSTACK(A2#,"", C2#).

3
Adjust for multiple columns

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

Combine Lists Using the VSTACK Function
Column Alignment: Ensure that the inline blank array matches the exact number of columns in your data arrays to prevent #N/A errors from appearing in the blank row space.

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. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dynamic lists.
  2. 2. Select a target cell: Click an empty cell with sufficient space below it to accommodate the combined data.
  3. 3. Input the array formula: Enter the VSTACK formula with a blank delimiter: =VSTACK(Range1, {""}, Range2).
  4. 4. Apply and spill: Press Enter to instantly generate the combined dynamic list with a perfect blank row separator.
Fully compatible with Microsoft Excel formulas, including array functions.Lightweight and incredibly fast to install on any operating system.Seamless handling of dynamic spilled arrays for advanced data reporting.Free built-in tools for comprehensive data visualization and management.
microsoft office alternative - wps office

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.