Excel VSTACK and HSTACK: Append Ranges
By Szabó Gergő · Updated
VSTACK stacks ranges vertically. HSTACK places them side by side. Missing columns become #N/A unless you wrap IFNA.
Syntax and arguments
=VSTACK(array1, [array2], ...)- array1
- The first block to append.
- array2
- The next block. Column counts should match for VSTACK.
VSTACK / HSTACK examples
01
Stack two lists
East in A2:B20, West in D2:E15.
=VSTACK(A2:B20,D2:E15)One spill with East rows then West rows.
02
Side by side
Names in A, IDs in C.
=HSTACK(A2:A20,C2:C20)Two columns in one array.
03
Drop blanks
After a stack.
=FILTER(VSTACK(A2:A20,D2:D15),VSTACK(A2:A20,D2:D15)<>"")FILTER removes empty rows from the stacked list.
Common mistakes
Different column counts in VSTACK
Pad the shorter block or CHOOSECOLS both to the same width.
Headers stacked twice
Stack data rows only, or take headers from the first block.
Using it in Excel 2016
VSTACK and HSTACK need Microsoft 365.
VSTACK / HSTACK FAQ
VSTACK vs append in Power Query?
Power Query is better for refreshable imports. VSTACK is live on the sheet.
Can I stack whole sheets?
Yes if the ranges are the same width: =VSTACK(Jan!A2:C100,Feb!A2:C100).
What about TOCOL?
TOCOL flattens one range into a single column. VSTACK appends several ranges.