Here are another two new functions in Excel: VSTACK and HSTACK. The V is for vertical, and the H is for horizontal. The STACK part implies we are making a stack or pile and that is exactly what we are doing. The input is two, or more, arrays. These can be groups of cells, as in the examples below, or even better Excel tables. Learn more about the advantage of tables here.
VSTACK.
=VSTACK(array1,[array2],…) Note the square brackets which means the input is optional, but it would be a bit weird to not have two inputs, as there wouldn’t be any point to the function.
This is a function that spills into surrounding cells. See below we have stacked the two arrays of names one on top of the other. Now that isn’t very exciting when the names are on the same sheet, but this formula works if the list where on different sheets, or even different workbooks! No need to copy and paste from places to combine the data into one list.
You can achieve the same thing (and a LOT more) with Power Query, but a lot of people find that intimidating, so this is a great way to do it, when your lists already have matching columns, and just need stacking. Does work best when your two lists have the same number of columns.
HSTACK.
HSTACK has the same inputs, two or more arrays. And it is a function that will spill into surrounding cells. Rather than put the values on top of each other, it puts them side by side.
Great to join data together where the rows match up. Does work best when the data has the same number of rows.
These formulas will update automatically when the source data changes. If you have linked to tables, then as the tables grow the formula will automatically include the new data.