top of page

How to use the new VSTACK and HSTACK Excel functions

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


Simple VSTACK example with 3 arrays


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’.



Excel VSTACK function example with 3 arrays - Initial set up


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)



Excel VSTACK array function formula example for 3 arrays
Excel VSTACK function example with 3 arrays - selected arrays

And like magic, the result is all the data from our 3 tables stacked on top of each other.

Excel VSTACK function example with 3 arrays - result array

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.



Excel VSTACK function example with 3 arrays - result spilled array updating automatically

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




How to manage headers with VSTACK


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)

Excel VSTACK function example with headers included in first array

  • 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:

Excel VSTACK function example with custom array headers
Excel VSTACK function example with custom array headers


Returning unique rows only in a VSTACK function


In the above example, we may find some duplicates if a value exists in more than 1 of our source tables:


Excel VSTACK function duplicate rows

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.

Excel VSTACK function example with unique rows only and removing duplicates dynamically
Excel VSTACK function remove duplicates with UNIQUE function

We can then even sort sort these values in ascending order by adding a SORT function on top of it:


Excel VSTACK function sort results ascending or descending with SORT array function
Excel VSTACK function sort results ascending or descending with SORT array function


The HSTACK function


The basics


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.


Excel HSTACK function example

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:


Excel HSTACK function example with 2 arrays and including headers
Excel HSTACK function example with 2 arrays and including headers

As a result, we get the 3 horizontally stacked arrays we wanted:

Excel HSTACK function example with 2 arrays and including headers, formula result

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.


Excel HSTACK function removing errors when data is missing
Excel HSTACK function removing errors when data is missing

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.



Adding some space


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:


Excel HSTACK formula inserting blank columns
Excel HSTACK formula inserting blank columns

5,304 views0 comments

Recent Posts

See All

Comments


bottom of page