The Problem with Combining Data
A very common scenario in Microsoft Excel is receiving data in separate, identical tables. For example, your regional managers might send you three separate spreadsheets: “North Region Sales”, “South Region Sales”, and “West Region Sales”.
Historically, to combine this data into one master list for a PivotTable, you had to manually copy the data from the South spreadsheet, paste it at the bottom of the North spreadsheet, and then do it again for the West spreadsheet. If the regional managers updated their numbers the next day, you had to delete everything and start the copy-pasting process all over again.
The VSTACK (Vertical Stack) function solves this permanently. It is a dynamic array function that automatically grabs data from multiple different arrays or tables and visually stacks them on top of each other, creating one continuous, live-updating master column.
Understanding the Syntax
The syntax for VSTACK is incredibly simple. You just list the arrays you want to stack, separated by commas.
=VSTACK(array1, [array2], [array3], ...)
- array1: The first range of data (this will appear at the top of the stack).
- array2: The second range of data (this will appear directly below array1).
You can stack up to 253 different arrays in a single formula.
Example 1: Basic Vertical Stacking
Assume you have two small lists on the same worksheet. A list of Fruits is in cells A1:A5. A list of Vegetables is in cells C1:C4.
You want to combine these into one long list in Column E. Click on cell E1 and type:
=VSTACK(A1:A5, C1:C4)
Excel will instantly output all the fruits, and directly underneath them, it will output all the vegetables. Because this is a dynamic array, if you change the word “Apple” in cell A1 to “Banana”, the VSTACK array in Column E will automatically update.
Example 2: Stacking Multi-Column Tables
VSTACK works beautifully on large grids of data, not just single columns. The only strict requirement is that the tables you are stacking must have the exact same number of columns, and the columns must be in the same order.
If you have a 3-column table for “Q1 Sales” on Sheet1 (A2:C100), and a 3-column table for “Q2 Sales” on Sheet2 (A2:C150), you can stack them on a master summary sheet like this:
=VSTACK(Sheet1!A2:C100, Sheet2!A2:C150)
This will generate a massive 3-column array containing all 248 rows of data seamlessly merged together.
Example 3: Adding a Custom Header Row
When stacking raw data from other sheets, you usually do not want to highlight their header rows, because VSTACK would place the Q2 header row right in the middle of your master dataset.
Instead, you can use VSTACK to dynamically generate your own header row at the very top of the stack. You do this by typing the header names in curly braces {" "} as the very first array argument.
=VSTACK({"Date", "Salesperson", "Revenue"}, Sheet1!A2:C100, Sheet2!A2:C150)
Excel will place your custom header row at the top, followed by the Q1 data, followed by the Q2 data.
Handling Missing Data Errors
If you attempt to use VSTACK on tables that have different widths (for example, stacking a 3-column table on top of a 2-column table), VSTACK will execute, but it will fill the empty spaces of the narrower table with ugly #N/A errors to maintain the grid structure.
If you encounter this, you must either ensure your source tables have identical column widths, or you must wrap the shorter array in the EXPAND function to dynamically pad it with blank spaces before stacking it.