VSTACK is one of the best reasons to use Microsoft 365: it combines ranges from different sheets into one dynamic list without copy-paste or Power Query. It is also unforgiving when the source ranges do not line up or the target area is blocked.
Start with the error code. Each one points to a different fix.
Quick diagnosis
| Error | Most likely cause | First fix |
|---|---|---|
#NAME? | Excel version does not support VSTACK, or the name is misspelled | Check the version; use Power Query if you are on 2021 or older |
#VALUE! | The stacked ranges have different column counts | Make every range the same width, or pad it with CHOOSECOLS/IFERROR |
#SPILL! | Something is blocking the output range | Clear the cells below and to the right; avoid merged cells and tables |
#CALC! | The formula produces an empty array or an unsupported nested array | Check that each range has at least one row; simplify nested logic |
#NAME? — the version problem
VSTACK is available in Microsoft 365 and Excel 2024. It is not in Excel 2021, 2019 or 2016, even if those versions have other dynamic-array functions. If the same formula works on a classmate's laptop but shows #NAME? on yours, the version is the difference.
Check with File → Account → About Excel. If you are on an older version, use Data → Get Data → From Table/Range and append the tables in Power Query. The result refreshes with one click and works in every supported Excel version.
If you are on 365, check the spelling and the commas. =VSTACK(A2:B10, D2:E10) is correct; =VSTACK(A2:B10 D2:E10) is not.
#VALUE! — the ranges do not match
VSTACK stacks arrays vertically, so every array must have the same number of columns. A2:B10 has two columns; D2:F10 has three. Excel cannot decide how to align them and returns #VALUE!.
Fix it by selecting the same columns from each source:
=VSTACK(A2:B10, D2:E10)
If a source genuinely has an extra column you do not need, use CHOOSECOLS to select the matching columns first:
=VSTACK(A2:B10, CHOOSECOLS(D2:F10,1,2))
If a source is narrower, add a blank column with HSTACK so both sides have the same width:
=VSTACK(A2:B10, HSTACK(D2:D10, ""))
#SPILL! — the output is blocked
A dynamic array needs empty space to expand. #SPILL! means one or more cells in the output area contain something: a value, a formula, a merged cell, a table, or another spilled array.
- Click the error and read the dotted border — that is the range Excel is trying to fill.
- Delete or move anything inside the border.
- Unmerge any merged cells in the output area.
- Do not put a VSTACK formula inside an Excel Table; tables do not allow spilled output. Put it on a blank sheet and reference the table instead.
- Check for a second dynamic formula nearby that is already using the same cells.
#CALC! — an empty or unsupported array
This is rarer with VSTACK. It usually appears when one of the ranges is empty, when a nested function returns an array of arrays, or when the formula is asked to stack results that Excel cannot represent. Test each range separately:
=VSTACK(A2:B10) // does this work on its own? =VSTACK(D2:E10) // and this one?
The one that fails is the source of the problem. If a range can be empty, guard it with IFERROR or point it at a non-empty fallback.
A working VSTACK pattern for multiple sheets
This is the pattern most assignments actually need — three monthly sheets with the same columns:
=VSTACK(Jan!A2:D100, Feb!A2:D100, Mar!A2:D100)
Keep the ranges identical, leave enough blank rows below the formula, and do not place it in a table. If you want to remove blank rows from the result, wrap it in FILTER:
=FILTER(VSTACK(Jan!A2:D100, Feb!A2:D100, Mar!A2:D100), VSTACK(Jan!A2:A100, Feb!A2:A100, Mar!A2:A100)<>"")
That formula stacks the data and uses a stacked key column to drop empty rows. It is longer, but it is easier to explain in a report than a sheet full of pasted values.
Older Excel: use Power Query, not a fragile workaround
If VSTACK is not available, do not build a 40-line INDEX formula that nobody can audit. Use Power Query:
- Select each table → Data → From Table/Range.
- In Power Query, use Home → Append Queries → Append as New.
- Remove blank rows, set column types, then Close & Load.
- Next month, add the new table and click Refresh All.
It works in Excel 2016+, keeps the source data separate, and gives you a refreshable audit trail. For a step-by-step walkthrough, read the Power Query data-cleaning guide.
Which version do you have?
Use the VSTACK availability guide for the full version table, and the VSTACK tutorial for basic examples. If you are building a multi-sheet report, the Data Cleaner can standardise the pasted data before you stack it.
Still seeing an error? The free Excel Error Fixer explains #NAME?, #VALUE!, #SPILL! and #CALC! with exact fixes, and the Formula Checker can inspect the syntax. If the workbook is due tonight, send it to an Excel expert.
