Hi - Dave here.
Happy Friday!
Excel makes it easy to split data up: one sheet per month, one table per region, one list per team, etc. But putting data back together has always been a pain.
The VSTACK function solves this problem. As the name suggests, VSTACK stacks ranges vertically into a single list that updates automatically when the data changes. For example, in the worksheet below, the formula in J5 combines two Excel Tables:
| =VSTACK(Table1,Table2) |
Download the workbook and read the full explanation
Once the data is combined into one array, you can sort it, filter it, remove duplicates, or summarize it, just like a single range. For example, to list the unique names from three separate lists in alphabetical order, you can use a formula like this:
| =SORT(UNIQUE(VSTACK(B5:B9,D5:D10,F5:F11))) |
I just updated our VSTACK page with 11 worked examples: combining lists and tables, adding headers, ranges with different column counts, data from multiple sheets, filtering and summarizing combined data, a report with a total row, and a couple of advanced tricks with DROP and REDUCE.
Along the way, I ran into some gotchas worth knowing about. First, empty cells come through as zeros when ranges are combined. The fix is a trim reference like B5:.B100, which drops the empty rows at the end of the range. Second, COUNTIF and SUMIFS won't accept the result from VSTACK directly inside a formula, because they want a range, not an array. The page includes some workarounds. Finally, trim references don't work with 3D references like Jan:Mar!B5:D16, so when you combine sheets this way, you may still need FILTER to remove the empty rows.
Note: VSTACK requires Excel 2024 or later, or Excel 365. Trim references require Excel 365.
Keeping Exceljet going
The internet as we know it is getting crushed by AI. Many websites have lost over half their traffic. Exceljet is still at it, publishing new content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever.
Excel formulas
We maintain a list of over 1000 working formulas here.
If you need more structure, we also offer video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.
Exceljet
P.O. Box 4804
Salt Lake City, UT 84110
Copyright © 2026 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.