Excel: Use VSTACK Function and Ignore Blanks

---

By default, the VSTACK function in Excel stacks multiple columns into one column and fills in zeros when blank values are encountered.

However, you can use the following formula to use the VSTACK function and simply ignore blanks:

=LET(x,VSTACK(A2:A9,B2:B9),FILTER(x,x<>""))

This particular formula stacks the values in the range A2:A9 and B2:B9 into one column and ignores any blank values in each range.

The following example shows how to use this formula in practice.

Example: How to Use VSTACK Function and Ignore Blanks in Excel

Suppose we have the following two columns in Excel that show the sales made at two different retail stores:

Suppose we would like to stack the sales in each column into one single column.

Suppose we type the following formula into cell D2 to do so:

=VSTACK(A2:A9, B2:B9)

The following screenshot shows how to use this formula in practice:

We can see that the VSTACK function stacks the values from each column into one single column while filling in each blank value with a zero.

To ignore these blank values entirely, we could instead type the following formula into cell D2:

=LET(x,VSTACK(A2:A9,B2:B9),FILTER(x,x<>""))

The following screenshot shows how to use this formula in practice:

We can see that the VSTACK function stacks the values from each column into one single column and simply ignores the blank values from each column.

Note #1: In this example we used the VSTACK function to stack values from two columns, but in practice you can use this function to stack as many columns as you’d like into one single column.

Note #2: You can find the complete documentation for the VSTACK function in Excel .