Excel: How to Use VSTACK Function and Ignore Blanks

by Zach Bobbitt Published on October 2, 2023

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:

Excel VSTACK ignore blanks

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

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel

Zach Bobbitt

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike. My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

4 Replies to “Excel: How to Use VSTACK Function and Ignore Blanks”

Allison says:

November 25, 2024 at 12:34 am

Awesome, thank you very much.

Reply
1. James Carmichael says:
  
  November 25, 2024 at 12:54 pm
  
  You are very welcome Allison! Keep us posted on your progress!
  
  Reply
Barbara Frary says:

January 18, 2025 at 4:07 pm

Does this only work for numbers? I am stacking text and doesn’t
seem to work giving me #NUM

Reply
1. James Carmichael says:
  
  January 18, 2025 at 5:47 pm
  
  The **VSTACK** function in Excel works for both numbers and text. However, if you’re getting a `#NUM!` error, it might be due to an issue with how the function is used or with the data it is referencing. Let’s troubleshoot and clarify the behavior.
  
  ### Common Reasons for `#NUM!` Error in `VSTACK`
  1. **Blank Cells**:
  – The `VSTACK` function includes blank cells when stacking, but if the blank cells are causing issues, it may be due to downstream operations or formulas dependent on the `VSTACK` output.
  
  2. **Data Formatting**:
  – If the cells contain mixed data types (e.g., numbers and text), and the output is being processed in a way that expects only one data type (e.g., numeric operations), it might lead to a `#NUM!` error.
  
  3. **Dynamic Arrays or Formula Errors**:
  – If the range you’re referencing in `VSTACK` has dependent formulas that produce errors, `VSTACK` will propagate those errors into the result.
  
  ### Solution: Using `VSTACK` and Ignoring Blanks
  To stack text and numbers while ignoring blanks, you can pair the `VSTACK` function with the `FILTER` function:
  
  #### Example Formula:
  “`excel
  =VSTACK(FILTER(A1:A10, A1:A10<>“”))
  “`
  
  – **`FILTER(A1:A10, A1:A10<>“”)`**:
  This filters out blank cells (`””`) from the range `A1:A10`.
  – **`VSTACK`**:
  Then stacks the non-blank values together.
  
  ### Additional Tips:
  – **Ensure Data Consistency**: Check that all cells in the referenced range contain valid text or numbers.
  – **Error Handling**:
  If you expect errors in your data (e.g., `#N/A`), you can wrap the formula with `IFERROR`:
  “`excel
  =VSTACK(FILTER(A1:A10, A1:A10<>“”, “”))
  “`
  – **Clearing Hidden Characters**:
  Blank cells may appear empty but can sometimes contain hidden characters (e.g., spaces). Use `TRIM` or `CLEAN` to clean the data:
  “`excel
  =VSTACK(FILTER(TRIM(A1:A10), TRIM(A1:A10)<>“”))
  “`
  
  ### If It Still Doesn’t Work
  If you’re still getting `#NUM!`, double-check:
  1. The referenced range doesn’t include cells with errors.
  2. There aren’t formatting issues or formulas causing problems.
  3. Excel is updated to the latest version, as `VSTACK` is a relatively new function.
  
  Let me know if you need further clarification or another example! 😊
  
  Reply