Stack the Deck in Excel with VSTACK and HSTACK

Overview

Combining data from different ranges, sheets, or even files is a very common task in Excel.  While there are other ways to combine data (such as copying and pasting, macros, and Power Query), using dynamic array functions (also known as DAFs), is a great approach that combines simplicity, flexibility, and scalability.

In this article, we’ll walk through two specific DAFs that are especially useful for combining data: VSTACK and HSTACK.  We’ll cover why they are worth using, how they work, and examples of each.  Make sure to grab a copy of the example workbook that accompanies this article using the box below!  And if you want examples of all of the DAFs along with tips and gotchas, check out this comprehensive template.

Why use VSTACK and HSTACK

There are a variety of reasons why both of these are helpful to use in your formula-heavy Excel files:

1. Utility – combine data sets vertically or horizontally (or both!), without the need to endlessly copy and paste
2. Scalability – as your data changes, the results reflect those updates – both the data and the number of rows/columns!
3. Flexibility – they work great by themselves or along with other functions like FILTER, SORT, and UNIQUE
4. Simplicity – they are extremely easy to learn and use

How to use VSTACK and HSTACK

Both VSTACK and HSTACK are very easy to use in a basic way.  Simply reference the ranges you want to combine (aka stack), as you’ll see in the examples below.

VSTACK

Notice how in the screenshot below, we’re simply stacking three simple data ranges on top of each other.  Because VSTACK is naturally a DAF, it automatically spills based on the references with just one cell formula to manage instead of the need to fill formulas down and across.

VSTACK only requires one argument – a range to stack – but using just one wouldn’t really accomplish much.  You can reference up to 254 separate ranges that you want to stack vertically.  In the screenshot above, you’re seeing three separate ranges stacked.

You can also stack values instead of references.  A common example of this is adding headers to a data set.  Notice in the screenshot below that the headers have been included within curly brackets (which defines an array), and separated by commas.

If for some reason you don’t need all the rows or columns from the data you’re stacking, consider using the DROP function to remove some of them.

HSTACK

HSTACK is extremely similar, but instead of stacking vertically across rows it stacks horizontally across columns.  Notice how in the screenshot below, we’re simply stacking three related data ranges side by side.  Just like VSTACK, it automatically spills across and down based on the references with just one cell formula to manage instead of the need to fill formulas down and across.

Just like VSTACK, the only arguments you need to supply are the ranges (or arrays of values) you want to stack horizontally.

If for some reason the ranges you reference do not have the same number of columns (for VSTACK) or rows (for HSTACK), any missing values will be displayed with the #N/A error (as seen in the screenshot below).  You can display blanks in place of those errors by wrapping the formula in an error-handling function such as =IFERROR(VSTACK(….),””).

Bonus Example

There are several other examples of using VSTACK and HSTACK in the free workbook you can download at the top and bottom of this article, and discussed in the video below.  They include stacking across sheets, summarizing data, and unpivoting data.

Here’s one other use case worth considering – embedding your formulas within your data headers.  You might be wondering what that means and why it’s helpful, so let me explain (or just look at the workbook or the video below!).

I use Excel for tracking things, making lists, and building schedules.  I imagine you do too.  Within those lists is often a mix of input values and formulas.  If you were to clear out the entire list, you’d lose those valuable formulas.  For a long time, I would copy the formulas into the header cell comments just in case someone deleted them.  But now I put them directly in the column header, along with dynamically-expanding formulas!

Take a look at the screenshot below in the selected cell’s formula (cell E3).  It may look a little complex, but let’s break it down.  This is from a time tracking template I’ve used and optimized over the years.  If that sounds useful, you can grab a copy of that template here.

• The VSTACK portion is just stacking the header “Hours” with the rest of the formula.
• The LET portion is allowing us to set reusable variables.
• The st_tm variable references column C, and the en_tm variable references column D, both with plenty of room for growth.
• The st_tm_b variable uses OFFSET, MAX, and COUNTA to determine the last used cell in column C.  There are other ways to do this (i.e. with TRIMRANGE and the dot shortcuts), but they are only compatible with very recent versions of Excel.
• The en_tm_b variable does the same for column D.
• The last part says if there’s nothing in column D, just show a blank. Otherwise, show the difference in columns C and D, and multiply by 24 to display in hours.

While it may sound a little complex, there are two key benefits to using a formula like this:

1. Normally, if you were to clear all the cells below the headers (range A4:F28), you’d overwrite the formula and have to think through it and type it again (not fun, trust me!). But with this approach, the formula stays in the header and just returns a blank value until some data is filled in!
2. The formula results automatically expand based on the data in columns C and D so as you enter new start and end times, column E (hours) will automatically spill over into the next row!

Video

Summary

VSTACK and HSTACK are just two of the many DAFs that are dramatically changing the formula approach for Excel users.  They cut out the need to copy and paste to combine your data in a simple way.  And for your more complex tasks, they are awesome at working together with other functions like FILTER, SORT, and UNIQUE to calculate and structure just the data you’re looking for.  And with that last example of embedding formulas within your header cells, you can save a lot of your sanity and add some controls to your workbooks!

Have you done some interesting things with the VSTACK or HSTACK functions yet?  Let us know in the comments below!