Table of Contents
The VSTACK function in Excel is a powerful tool that allows users to vertically stack data from different columns into one single column. However, sometimes there may be blank cells within the data, which can cause errors or distort the desired outcome. To overcome this issue, the VSTACK function offers a way to ignore these blank cells while still combining the data. By using the formula “=VSTACK(range,TRUE)”, the function will only stack the non-blank cells from the specified range, ensuring a clean and accurate output. This feature can be particularly useful for data analysis or organizing large sets of data.
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: