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!!

Video

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.

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.

Dynamic

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.

Sample file

If you have any other tips or suggestions, please share by posting a comment below … thanks!