The Problem with Combining Data Sets
In Microsoft Excel, receiving data in chunks is a common annoyance. You might get a sales report for Q1 in one worksheet, and a sales report for Q2 in another. If the columns are identical, you usually have to manually copy the data from the second sheet and paste it at the very bottom of the first sheet to create a master table.
If that data changes or grows, your static copied-and-pasted master table instantly becomes obsolete. You could try using complex Power Query merges, but for simple tasks, that is often overkill.
Excel’s VSTACK (Vertical Stack) dynamic array function solves this instantly. It allows you to select multiple different arrays (or ranges) and seamlessly stack them on top of each other, creating a single, dynamically updating master list.
Understanding the Syntax
The syntax for VSTACK is incredibly simple:
=VSTACK(array1, [array2], [array3], ...)
- array1: The first range of data (this will appear at the top of the stack).
- array2 (Optional): The second range of data (this will be appended directly beneath array1).
You can stack up to 253 different arrays.
Example 1: Stacking Data from Multiple Worksheets
Assume you have three worksheets named “Jan”, “Feb”, and “Mar”. Each sheet contains an identical table structure (Date, Rep, Sales) running from A2 to C50.
To create a master table on a new summary sheet, simply go to cell A2 and write:
=VSTACK(Jan!A2:C50, Feb!A2:C50, Mar!A2:C50)
Excel will instantly spill a brand new, three-column array. It pulls all 49 rows from January, immediately followed by the 49 rows from February, and finishes with March. If a sales rep updates a number in the “Feb” sheet, the VSTACK array on the summary sheet updates in real-time.
Handling Blank Cells and Zeroes
One caveat with VSTACK is how it handles empty cells within the ranges you select. If you select A2:C100, but only 50 rows actually contain data, VSTACK will pull in the 50 blank rows and display them as “0”s before stacking the next array underneath.
To avoid this, you should ideally use Excel Tables instead of static ranges. If your data on the “Jan” sheet is formatted as a Table named TableJan, you can simply write:
=VSTACK(TableJan, TableFeb, TableMar)
Because Excel Tables automatically expand and contract to fit their exact data, VSTACK will never pull in blank rows, ensuring your stacked master list is always perfectly consolidated.
Example 2: Adding Headers on the Fly
If you are pulling data from Tables, the headers are usually excluded by default. You can manually type the headers into your summary sheet above the VSTACK formula, but you can also use VSTACK to generate the headers dynamically.
You can stack a hard-coded text array on top of a dynamic range:
=VSTACK({"Date", "Rep", "Sales"}, TableJan, TableFeb)
The curly braces {} denote a horizontal array of text. VSTACK places this text array at the very top (Row 1), and then stacks the data tables underneath it.