Excel How To Combine Tables with a Single Formula
If you have ever tried to combine data from different tables manually, that is a lot of copy/paste! In this post, I’ll show you how to use Excel’s VSTACK function so you can do it instantly with a single formula. No copy/paste needed!!
Step by Step
Let’s say you have a table in Excel with January’s transactions. Perhaps it is named Jan and looks something like this:
You also have a different table for February named Feb:
And you have a March table named Mar:
Now, you’d like to combine them all and create something like this:
Rather than copy/paste Jan, copy/paste Feb, and copy/paste Mar, we can use VSTACK.
The VSTACK function is designed to allow you to combine data from multiple tables/ranges. The underlying assumption is that the tables have the same columns and the same column order. If so, you can easily combine them with the VSTACK function.
For example, to combine the transactions from our Jan, Feb, and Mar tables:
=VSTACK(Jan, Feb, Mar)
You basically enter the table names as function arguments and hit enter. That’s it! You now have all of the transactions combined.
To include a header row, like in the screenshot above, just update the formula like this:
=VSTACK(Jan[#Headers], Jan, Feb, Mar)
Now the Jan table’s header row will be stacked on top.
Plus, the best part is that this is dynamic. So, if you add another transaction to any table, it will automatically show up in the combined results table without any extra steps 🙂
Note: the VSTACK function is not available in every version of Excel. If you are using Excel Online or a fairly modern version, you should have it available. If you have a legacy version of Excel that doesn’t include VSTACK, you can check out Power Query’s merge/append feature instead.
If you have any other tips or suggestions, please share by posting a comment below … thanks!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.