Updated: May 11, 2023
in 2022, Microsoft revealed no less than 14 new Excel functions. These functions are probably one of the biggest game-changer of the decade for Excel functions. These are all array functions, meaning the result is not just a value but a full range of results that will adjust dynamically.
We dedicated a specific post to the new text splitting functions: TEXTSPLIT, TEXTBEFORE and TEXTAFTER.
So here we are going to focus on 2 additional functions called HSTACK and VSTACK, which both aim at stacking and combining arrays into a new single array. Let’s check this out with a bunch of examples!
The VSTACK function takes data and stacks it on top of each other. Since it is a function, we can combine it with other brilliant functions to make it even more powerful. It has the following syntax:
=VSTACK(array1, [array2], …)
Let’s take a look at the different arguments:
Array1: the ‘array’ argument specifies the cells ranges you want to append to one another, therefore creating a single, unified list.
Array2: [optional] The second array or range to combine.
The syntax is pretty simple, but there are some clever techniques we can use to enable stacking data.
The result from VSTACK is a single array that spills onto the worksheet into multiple cells. It is worth pointing out that the output from VSTACK is fully dynamic. It works well with Excel tables, since they automatically expand when new data is added.
Let’s jump right into an example that will explain things in the best way possible.
VSTACK stacks ranges or arrays vertically. In the example below, we have some sales figure for different sales representatives and we would like to output all of the sales representatives’ salaries in an Excel table. The 3 lists are proper Excel Tables named ‘Table1’, ‘Table2’ and ‘Table3’.
We don’t necessarily need to have them broken down. Before this function was released, we would generally take each data group, copy and paste them into a new table. Say hello to a new way of doing this more efficiently and dynamically.
We want to stack the table arrays on top of each other to create a single list that could be used for things like sorting, filtering, and perhaps even charting. So, we will combine the arrays of our Table1, Table2 and Table3. Each subsequent range/array will be appended to the bottom of the previous range/array.
Using the VSTACK function, our formula looks like the following:
=VSTACK(Table1,Table2,Table3)
And like magic, the result is all the data from our 3 tables stacked on top of each other.
How cool is that? Pretty cool right?
Imagine if those three tables were on separate sheets or even in separate files. You would have to use more complex tools like Power Query to get the same result.
Since the source is formatted as an Excel Table, the output will update automatically when any of the source ranges is updated with additional data or any other change.
The formula-based result will refresh automatically, whereas you would need to manually refresh the results when using Power Query. Sounds like a no-brainer, right?!
Combining the data is great, but what about headers? How can we have dynamic headers without having to include the headers of each source table?
That's a little more tricky but it's actually quite easy. Column headers can be added:
We can have as first argument a simple row with just the headers
We can reference the first table with headers included, while including only the data for the other tables)
Or we can write custom headers directly in the formula. For this, you need to wrap the list of headers in curly braces, with each header name in double quotes and separated by a comma, as shown below:
In the above example, we may find some duplicates if a value exists in more than 1 of our source tables:
Ignoring duplicates from the final array is super easy with the VSTACK function. For this, just wrap the VSTACK formula in a UNIQUE function which will return only the unique values.
We can then even sort sort these values in ascending order by adding a SORT function on top of it:
The HSTACK array function is the horizontal equivalent of the VSTACK formula. It appends the tables side-to-side instead of atop one another.
HSTACK works in a way similar to VSTACK in every aspect. For this reason, let's jsut look at one scenario and all of the above will apply just like for VSTACK.
In this example, the setup is slightly different. We have 3 vertically arranged arrays and we want to combine them into a horizontal array. We will include the headers of Table1 as shown above.
The formula will be:
As a result, we get the 3 horizontally stacked arrays we wanted:
If the array is being stacked from tables of different sizes, HSTACK (as well as VSTACK) will return # N/A errors instead of the missing data.
With the help of the IFNA() function, we can get rid of those errors when tables don’t match in height as shown above, and specify a different result instead of these errors. For instance, let's use double quotes to show cells empty instead.
We could of course return something different like "Missing data" or "n.d." by simply adding this custom text inside the double quotes in the above formula.
HSTACK and VSTACK actually even allow us to add some double quotes in order to insert blank columns between our stacked ranges, as show below: