Select Page

Excelâ€™s VSTACK function is used to quickly combine multiple lists into a single list. The source lists can all be on the same worksheet or they can come from separate sheets. They can be Tables or they can be simple ranges.

The screenshot above is taken from an Excel file containing 6 sheets. The sheets are named Jan, Feb, Mar, Apr, May and Jun. Each sheet contains a Table and each table contains a different number of rows with each row representing details of a sale.

To combine the data into a single list, I can use the following formula:

=VSTACK(tblJan,tblFeb,tblMar,tblApr,tblMay,tblJun)

Three of the Tables (tblApr, tblMay and tblJun) have no data:

However, a Table must have a minimum of 2 rows – 1 row for the headings and 1 row for the data (even if there is no data). Try and change the above Table to be A5:G5 and Excel will automatically change it to be A5:G6)

The result of using the above formula is that the consolidated range includes 3 blank rows at the bottom (the blank “data row” from each of the 3 Tables tblApr, tblMay and tblJun)

In this video I demonstrate 3 ways to solve the problem

The formulas I used in the video:

=FILTER(VSTACK(tblJan,tblFeb,tblMar,tblApr,tblMay,tblJun),VSTACK(tblJan[OrderID],tblFeb[OrderID],tblMar[OrderID],tblApr[OrderID],tblMay[OrderID],tblJun[OrderID])<> “”)

This formula combines the data in the 6 Tables into a single list and then removes the blank lines. It does this by using the FILTER function to retain only those rows from the consolidated range where the OrderID column from the consolidated range does not equal blank

This formula does the same thing but is shorter…

=FILTER(VSTACK(Jan:Jun!A6:G50),VSTACK(Jan:Jun!A6:A50)<> “”)

It uses 3D references (a range of consecutive worksheets – in this case Jan to Jun.

However 3D references do not play nicely with Tables so I have specified A6:G50 as the range in each sheet that contains the data to combine. Basically I chose a range that is larger than the number of rows I’ll need. The FILTER function removes the additional blank rows from the consolidated/combined list