Excel Tutorials · Lesson 73
Two lists that should really be one is a common mess, whether it's this month's signups and last month's, or two exports from different systems. VSTACK joins them into a single array with one formula, and paired with UNIQUE, it hands back a clean, deduplicated list with no copy-pasting involved.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
VSTACK stacks two or more ranges or arrays vertically into one spilled array. Its syntax is =VSTACK(array1, array2, ...), and it takes as many ranges as you give it, joining them top to bottom in the order listed. On its own it does not deduplicate, sort, or filter anything, it just joins.
The requirement that trips people up is column count. Every range passed to VSTACK needs the same number of columns, since Excel has no way to reconcile a three-column list with a two-column one. Row counts, by contrast, can differ freely between the ranges.
Where VSTACK earns its place is combined with other dynamic array functions. Wrapped in UNIQUE, it merges two overlapping lists into one with no duplicates. Wrapped in SORT as well, that combined list comes out already alphabetized or numerically ordered, all from a single formula that updates itself as the source data changes.
The honest framing: VSTACK requires Excel 365 or Excel 2021 and later, since it is a dynamic array function. On older versions, the same result needs a helper-column workaround or manual copy-paste, both far more fragile than one live formula.
Step-by-step, matching the video above.
Confirm both ranges have the same number of columns. VSTACK can stack any number of rows, but the column counts must match.
In an empty cell, type =VSTACK(List1,List2). Excel spills List2 directly beneath List1 as one continuous array.
A #VALUE! error means the column counts don't match. A #SPILL! error means something is already sitting in the cells the array needs.
Change the formula to =UNIQUE(VSTACK(List1,List2)) so any entry that appeared in both lists only shows up once in the result.
Add one more wrap: =SORT(UNIQUE(VSTACK(List1,List2))) alphabetizes or numerically sorts the combined, deduplicated list automatically.
Convert each source list to a Table (Ctrl+T) so new rows added to either one flow into the combined list without editing the formula.
Reference the spilled range with the # suffix, such as B2#, in a dropdown, chart, or another formula so it always reflects the full combined list.
| List A | List B | =UNIQUE(VSTACK(A,B)) |
|---|---|---|
| Jordan Reyes | Priya Nair | Jordan Reyes |
| Priya Nair | Mateo Cruz | Priya Nair |
| Sam Cole | Sam Cole | Sam Cole |
List A and List B share two names: Priya Nair and Sam Cole. =UNIQUE(VSTACK(A2:A4,B2:B4)) stacks all six entries into one array, then collapses the two overlaps, spilling four unique names total instead of six with duplicates.
Three functions, and the order they're nested in matters.
Takes any number of ranges and stacks them vertically. Column counts must match across every range; row counts can differ freely.
Wrapped around VSTACK's result, it removes any row that appears more than once, regardless of which source list it came from originally.
Wrapped around UNIQUE, it orders the deduplicated result. =SORT(UNIQUE(VSTACK(A2:A10,B2:B10))) is the full pattern, evaluated inside out.
Once a VSTACK formula spills, referencing the anchor cell with a trailing #, like D2#, points at the entire live result, useful for feeding it into a chart or a dropdown list.
=HSTACK(array1,array2) does the identical job sideways, joining ranges into more columns instead of more rows.
Three errors, each with one specific cause.
One range has more or fewer columns than the other. Trim both ranges down to the same column width before stacking them.
Something already occupies the cells below or beside the formula. Clear that area, or move the formula to open space on the sheet.
VSTACK doesn't exist before Excel 2021/365. Confirm the version, or fall back to a helper-column approach if upgrading isn't an option.
| Formula | Result |
|---|---|
| =VSTACK(Team1,Team2) | Every name, duplicates included |
| =UNIQUE(VSTACK(Team1,Team2)) | Every name, once each |
| =SORT(UNIQUE(VSTACK(Team1,Team2))) | Every name, once each, alphabetized |
The final formula's spilled range is exactly what a Data Validation dropdown or a chart's category axis needs, one clean list built from two source ranges without ever combining them by hand.
Where combining ranges genuinely earns its place.
Two customer lists from different platforms, each with the same columns, combine into one clean master list without a manual copy-paste step.
Rather than appending each new month by hand, VSTACK a fixed set of monthly ranges once, and the combined list updates as each month's data changes.
A dropdown that needs to offer values from two separate lists (like an internal team list and an external contractor list) can point straight at the VSTACK output.
Sheets has VSTACK with the same syntax and the same pairing with UNIQUE, so a formula built in Excel copies over without changes.
Excel has no way to reconcile a two-column list with a three-column one. Trim both to match before writing the formula, or a #VALUE! error is guaranteed.
VSTACK alone just joins the lists; it does not deduplicate. Overlapping entries between the two source lists show up twice unless UNIQUE wraps the whole thing.
The array needs empty cells below and to the right of the formula. Data sitting in that space causes a #SPILL! error instead of a result.
A hard-coded range like A2:A50 stops picking up new rows once the source list grows past it. Reference a Table column instead so it expands automatically.
It only works in Excel 365 and 2021+. Opening the same file in Excel 2019 shows a #NAME? error where the formula used to work.
Only the anchor cell holds the formula. Typing into any other cell in the spill range breaks it, since those cells are calculated, not editable.
A single source of truth beats stacking two after the fact.
Free 14-day trial. No credit card required.
It stacks two or more ranges or arrays vertically into a single spilled array, joining lists that live in separate places into one continuous list.
Excel 365 or Excel 2021 and later. VSTACK is a dynamic array function and does not exist in Excel 2019 or earlier.
No. VSTACK only joins the lists together; duplicates from both lists remain unless VSTACK is wrapped inside UNIQUE.
=UNIQUE(VSTACK(List1,List2)) stacks both ranges and then removes any duplicate that resulted, leaving one clean list with no overlap.
Yes. Add more ranges separated by commas, such as =VSTACK(List1,List2,List3), and all of them stack into one array in the order listed.
VSTACK returns a #VALUE! error. Every range passed to VSTACK needs the same number of columns, though row counts can differ freely.
Yes. Wrap the whole thing once more: =SORT(UNIQUE(VSTACK(List1,List2))) stacks, deduplicates, and alphabetizes or numerically sorts the result in one formula.
Something is already occupying the cells the combined array needs to spill into. Clear that range, or move the formula to an empty area of the sheet.
Yes. Since VSTACK is a live formula, adding a row to either source list updates the combined output the moment the source range is referenced as a Table or a full column.
Yes, HSTACK. It works identically but combines ranges side by side into more columns instead of stacking them into more rows.
Yes, Sheets added VSTACK with the same syntax and behavior, including pairing it with UNIQUE the same way.
Manually copy-pasting one list underneath the other, or building a helper column with IFERROR and INDEX formulas to fake a combined list, both far more fragile than a single VSTACK formula.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data already connected to where it belongs.
Start Your Free Trial →