#SPILL! when stacking sheets with VSTACK: causes and fixes
By the Analistable team · Updated · 5 min read
#SPILL! means VSTACK calculated a result but could not write it to the grid. The usual causes are cells in the spill range that aren't empty (even a space), the formula sitting inside an Excel table, merged cells in the way, or a result too big for the sheet. Click the warning icon and choose Select Obstructing Cells to see which.
Part of our guide: How to combine Excel sheets into one
How spilling works
A dynamic array formula such as =VSTACK(Jan!A2:D41, Feb!A2:D39) returns a block of cells (here 79 rows by 4 columns) from the one cell you type it in. Excel needs every cell of that block to be empty. If anything stands in the way, the formula shows #SPILL! instead of a partial result. Hover over the error and the tooltip names the reason.
| Tooltip / cause | Typical trigger | Fix |
|---|---|---|
| Spill range isn't blank | Old data, a space, or another formula below | Clear the cells |
| Spill range has merged cells | Merged headers or labels in the output area | Unmerge |
| Spill range in table | Formula typed inside an Excel table | Move it outside the table |
| Spill range is too big | Result runs past row 1,048,576 or column XFD | Trim the input ranges |
| Spill range is unknown | Size changes as it calculates (volatile functions) | Remove volatility |
Blocked range: the most common cause
- Click the cell showing #SPILL! and then the warning icon next to it.
- Choose Select Obstructing Cells. Excel selects whatever is in the way.
- Press Delete to clear them, or move the formula to an empty area.
Obstructions are often invisible: a space, an apostrophe, a formula returning "", or white text. When the stacked sources grow, a formula that used to fit can suddenly collide with notes typed underneath it, so give the output its own sheet with nothing below or to the right.
Tables and merged cells
Excel tables do not support spilled formulas, so a VSTACK typed inside a table always returns #SPILL!. Put the formula on a normal range. You can still stack tables as inputs: =VSTACK(tblJan, tblFeb, tblMar) is fine, and it grows as the tables grow.
Merged cells in the output area block the spill too. Unmerge them (Home > Merge & Center to toggle off), and if you used merging for headings, use Center Across Selection instead; see merged cells breaking sorting and filtering.
Results that are too big
Stacking whole columns is the usual cause: =VSTACK(Jan:Mar!A:D) asks for three times 1,048,576 rows, more than any sheet holds. Use bounded ranges, tables, or a trimmed range, and filter out the empty rows:
=LET(
data, VSTACK(Jan:Mar!A2:D2000),
FILTER(data, CHOOSECOLS(data, 1) <> "")
)Recent Microsoft 365 builds also accept trim references like Jan!A2:.D2000, which drop trailing empty rows before stacking; check your version supports them. If your real data exceeds the grid, see merging files larger than Excel's row limit.
A worked example
| Sheet | Rows | Spill rows used | Last spilled row |
|---|---|---|---|
| North | 120 | 2–121 | 121 |
| South | 95 | 122–216 | 216 |
| West | 143 | 217–359 | 359 |
With 358 rows of data spilling from A2, a total typed in A300 blocks the range and the whole stack shows #SPILL!. Put totals above the stack, beside it, or on another sheet, and reference the spill with A2#: =SUM(CHOOSECOLS(A2#, 4)) totals the fourth column however long the stack grows.
Other formula patterns for combining are in dynamic arrays for combining data and the combine Excel sheets guide. If you'd rather not maintain formulas, Analistable can query the sheets as tables in your browser without writing the stack back to your workbook.
HSTACK and other stacking functions
Everything above applies to HSTACK, which spills sideways: obstructions to the right of the formula cause #SPILL!, and a result wider than 16,384 columns is too big. The same goes for FILTER, SORT, UNIQUE and CHOOSECOLS wrapped around a stack, since the outer function's result is what spills.
Two related errors are easy to confuse with #SPILL!:
- #CALC! from FILTER means no rows matched; add the third argument,
FILTER(data, test, "none"). - #N/A inside the stack means the inputs had different widths; VSTACK pads the shorter ones. Wrap the stack in
IFNA(…, "")or make the widths match. - #REF! often means a sheet named in a 3D range (
Jan:Mar!) was deleted or moved outside the range.
If you share the workbook with someone on an older Excel version, they see the formula result as a static array or an error rather than a live spill, depending on the version. For a workbook that must work everywhere, a Power Query append is the safer way to stack sheets, since the result is an ordinary table.
Finally, a formula that spilled fine and starts showing #SPILL! after a data refresh usually means the stack grew into something. Check for content below the old last row first. A good habit is to keep stacked results on a dedicated output sheet with a note at the top saying nothing should be typed below the formula, and to put any totals or charts on a separate sheet that reads the result through the spill reference.
Frequently asked questions
- Why does VSTACK give #SPILL! when the cells look empty?
- Something invisible is there: a space, an empty-text formula, white text, or a merged cell. Use Select Obstructing Cells to find it.
- Can I use VSTACK inside an Excel table?
- No. Spilled formulas are not supported in tables. Put the formula outside the table; tables can still be its inputs.
- How do I refer to the whole VSTACK result?
- Use the spill reference: the first cell followed by #, such as A2#.
- Is VSTACK available in every Excel version?
- No. It is in Microsoft 365 and Excel for the web; older perpetual versions like Excel 2019 don't have it.
- Why did my VSTACK work yesterday and show #SPILL! today?
- The source data grew, so the spill range now reaches cells that contain something. Clear the area below the formula or move it to its own sheet.
- Does #SPILL! mean the formula is wrong?
- No. The formula calculated a result; Excel just can't place it. Fixing the obstruction makes the result appear without changing the formula.