Exclude Blank Rows from VSTACK Data

In a recent post, we looked at how to pull data from several sheets and combine it into vertically stacked data using the VSTACK function. Here's the tutorial: VSTACK to Combine Multiple Sheets.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Downloads

You might have noticed that when we stacked the data, we only pulled data through row 9 from each tab because that's how long our data set was on the first tab.

But what if some of your tabs have longer data sets? The obvious answer is to pull as many rows as your longest data set. When you do that, however, you'll notice some zeros caused by the blank rows being pulled into the stack.

Zeros from blank rows using VSTACK

How can we remove these blank rows automatically?

By using the FILTER function.

How to Write the Formula

Currently, our existing formula looks like this:

=VSTACK(‘Division 1:Division 4'!A2:D12)

We're going to wrap this formula in FILTER, using the existing VSTACK formula as the array argument, and then also using it as the include argument, with one small change. We'll change D12 to A12.

To complete the FILTER formula, we'll end it by setting it not equal to zero (<>0).

The final formula looks like this:

=FILTER(VSTACK(‘Division 1:Division 4'!A2:D12),VSTACK(‘Division 1:Division 4'!A2:A12)<>0)

That removes all of the zero rows from the stacked data.

Related Post

If you didn't catch my recent tutorial for adding headers to your stack, you can check it out here: Including Headers in VSTACK Data.

I hope these posts about VSTACK have been helpful to you. Next time, we'll take a look at HSTACK. If the V stands for Vertical, I bet you can figure out the H!

Leave a comment if you have a question about this post, or about VSTACK in general.

10 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon
    I used VStack for the first time this week to consolidate data from other workbooks into one single book. It worked perfectly, however, when I open the consolidated data workbook, I don’t see my data, it show a REF error. I have to go and open the other books it’s linked to in order to see my data. I obviously don’t want to have them all open – just the consolidation. Why is this happening? Thanks, Louise

  • Another suggestion I would throw in is for users to manage their data using Excel tables. By nature tables create a dynamic named range.

    In the example if the data on the sheets were in tables, you would simply stack the tables. =VSTACK(Table1, Table2, Table3, Table4).

    Many roads to get there, thanks for the tip!

    Readers could also explore using other criteria to get a filtered data stack from multiple source data sets (like sales over $100,000) using the AND (to keep the 0) and VSTACK functions nested inside a filter function.

  • Hi, great tip.
    Wouldn’t it also be good to use the LET function for running VSTACK once, and clarity. So, instead of (just a dummy range)

    =FILTER(VSTACK(D1:D24), VSTACK(D1:D24)0)

    it could be

    =LET(data, VSTACK(D1:D24), FILTER(data, data0))

    if data range is big, does it help, improves performance?

  • A better approach is to put each worksheet’s data into a Table. As each Table changes size, its contribution to the VSTACK will also change size. The VSTACK formula remains simpler, and you don’t risk filtering improperly, for example, removing zero values that should be there.

    • Great point, Jon! Tables are another good solution here.

      I should have mentioned that in this scenario, they wanted to be able to drop in additional sheets and use a 3D reference to automatically include the new sheets.

      I don’t know if there is a way to do that with Tables. I believe all of the Table names would have to be referenced in the VSTACK, which would get long and challenging to manage if there were 20+ sheets with Tables.

  • Re:VSTACK
    I use marcos for some. In my macro, I select area and clear contents to make sure that vstack area has no problem cells.

  • Good, excluding blank rows works like a charm!

    However, the link to the including of headers is dead from your email and when I got to the right video, I failed to get the filter function addition to work. I checked and checked and in the end I wonder if there is something I am missing that is not so obvious!

    Best wishes

    Duncan

  • sorry i think my comment might not have been so clear

    what is not working is combining the exclusion of blank rows … that works
    the inclusion of headers … that works

    but the combination of the two does not work

    I tried this in my own file and in the file you shared, too

    Duncan

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools