When you have multiple tables or datasets, often in different files or sheets, and you need bring them together to get insights or do some reporting, combining them can be a tedious, unrewarding, and even an error-prone task. That’s why I present to you the power of VSTACK function in Excel that can make this process painless and when combined with other functions opens up new possibilites to do complex filtering and queries.
VSTACK is useful for conducting combined analysis across different tables/categories/time-series. It doesn’t do any calculation inherently like SUMIF or add up any values; values from each are retained as-is, just stacked as a new row from each table/range. However, as we’ll see you can use nested functions on the returned results after stacking.
VSTACK function is for vertical stacking of multiple tables/ VSTACK is a sibling on HSTACK which would do horizontal stacking (side-by-side) instead of on top of another table. The idea and usage are the same, so I’ll just discuss VSTACK here as it has more pragmatic uses in the real-world.
Suppose we have two separate tables of order and sales data from different years in different tables or documents. We want to get some insights to the products we’re selling year over year and trends. In order to do that, we cannot just look at each table separately, but comprehensively. The first step would be to bring them under a single dataset, be it a data table or a data range. Surely, you can do this by copying and pasting each table on top of one another manually. Not the most interesting task and it can be a nuisance when dealing with large datasets. The better method is to use a formula: VSTACK. The best part is, there is no hard limit to how many tables can be stacked together using VSTACK (or HSTACK).
Let’s see how it’s done with some quick examples. Let’s say we have these two tables…they may be in separate documents, separate sheets, or in the same workbook and sheet but just not together to make any analysis possible. The tables may look as below. One dataset is from 2022, and another is from 2023.
NOTE: The tables do not need to be of the same size (in rows or in columns).
Suppose the tables are in different sheets: one in a sheet called “Sheet3” and another in “Sheet4”. To combine from different sheets, we can use this: =VSTACK(Sheet3!B14:F26,Sheet4!H15:L26)
This way, where ever the formula is entered, the new combined datasets will start its first value and continue for as many columns as specified, and for as many rows in the array specified in the formula. Notice that I did not include the top-level year headers for either, and I also left out the column header for the second range (2023 dataset). This is just because I didn’t want the headers to appear twice in the new combined range. But it is up to you what you want to include or exclude.
The combined tables should look like this:
Now that we have the combined data in a neat range, we can do all sorts of analysis. What if we wanted to combine multiple tables and show it sorted by Total Sales in ascending order? We can do this by writing a single formula: =SORT(VSTACK($B$15:$F$26,$H$15:$L$26),5,1) assuming the first range is B15:F26 and the other is in H15:L26 (without the header). Just wrap the VSTACK with SORT function and supply 5 as the column number (which has Total Sales that we want to sort on), and the sort order (1 for ascending, -1 for descending). The returned data should look like this:
The last column, Total Sales is in ascending order and all associated product information is shown correctly per row.
Now, extract 5 top-selling products from those 2-year period (by Total Sales), and show just the month, product names, and total sales amount, sorted in descending order by the total sales amount. Here’s the result with just one more formula applied on the results above:
How is it done? By a combination of SORT, CHOOSECOLS, and nesting them in TAKE function. This is the formula you’d enter: =TAKE(CHOOSECOLS(SORT($B$37:$F$60,5,-1),1,2,5), 5)
The results from SORT(VSTACK()) previously is in B37:F60 range. So, we sort the range by column number 5, which is the Total Sales, in descending order (-1 argument). Then we wrap it in CHOOSECOLS to specify which columns we want. We wanted month, product name, and total sales and they are column numbers 1, 2, and 5 respectively (the last 3 arguments in SORT function). Finally, we nest the whole thing in TAKE which helps us to show a subset of the results. If we wanted the entire dataset sorted that way, then we wouldn’t have to use TAKE but since we want top 5, that’s where TAKE function comes in handy and we specify the resulting array as its first argument, and 5 as the second, and BOOM!
Let’s try one more complex query: Show 4 items from the top of the dataset that had Total Sales is greater than or equal to $500 but the result should be shown in descending order by Total Sales, AND only show the Product Name and Total Sales columns.
Here’s the result:
How? This is a more complex formula but quite elegant. The solution is: =INDEX(SORT(FILTER($B$37:$F$60,$F$37:$F$60>=500),5,-1),SEQUENCE(4),{2,5})
Again, the results from SORT(VSTACK()) previously is in B37:F60 range. To satisfy the condition of Total Sales (greater than or equal to), we use FILTER to extract the data from that range. Then we wrap it in SORT to by column number 5 (Total Sales), in descending order (-1). Then we use SEQUENCE to get the top 4 items (much like we did with TAKE) and pass it as the 2nd argument for SORT, but now we can also specify the columns to show as an array as the last argument in SORT: 2, 5 which are the product names, and total sales respectively. Finally, we wrap the whole thing in INDEX function which returns a range that represents a portion of the sorted array that we want. And Voila!
As you can see VSTACK (and HSTACK) is a very handy function, that can then be extended with additional functions to retrieve complex query results without having to do SQL, or pivot tables, or even power query, DAX, or Power BI. The choice is yours! I hope this was educational.
▛Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino! 
▟