Description
Use this function to vertically stack multiple ranges or arrays into a single range.
Syntax
VSTACK(array_1, [array_2], …, [array_29])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array_1 |
Yes | The first range or array to vertically stack (join). | A cell reference, a cell range, or a formula which results in either of these. |
[array_2], …, [array_29] |
No | Additional ranges or arrays to vertically stack. Up to 29 arrays can be added. | A cell reference, a cell range, or a formula which results in either of these. |
Examples
Sample data
Data set 1
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Name | Sales | Region | Commission | |
| 2 | Q1 | ||||
| 3 | John | 45000 | North | 4500 | |
| 4 | Alice | 52000 | South | 5200 | |
| 5 | Bob | 49500 | West | 4500 | |
| 6 | Q2 | ||||
| 7 | John | 48000 | North | 4800 | |
| 8 | Alice | 55000 | South | 5500 | |
| 9 | Bob | 51250 | West | 5100 |
Data set 2
Sheet 1 (Named "Employees_Q1")
| Name | Age | City | Salary | ||
|---|---|---|---|---|---|
| 1 | John | 35 | New York | 75000 | |
| 2 | Alice | 28 | Chicago | 65000 | |
| 3 | Bob | 42 | Los Angeles | 85000 |
Sheet 2 (Named "Employees_Q2")
| Name | Age | City | Salary | ||
|---|---|---|---|---|---|
| 1 | George | 22 | Denver | 55000 | |
| 2 | Helen | 30 | Scottsdale | 71000 |
Sample formulas
| Use case | Formula | Explanation and Result | |||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Join two horizontal arrays into a single table. | =VSTACK(A2:D4, A6:D8) |
This formula reads the two ranges on the tab and combines the Q1 and Q2 data into a single range. Using data set 1, this produces the following:
|
|||||||||||||||||||||||||||||||||||
| Combine data from two separate sheets into one table. | =VSTACK(Employees_Q1!A2:D4, Employees_Q2!A2:D3) |
This formula extracts the Q1 employee data from Sheet 1 (Employees_Q1!A2:D4) and the Q2 employee data from Sheet 2 (Employees_Q2!A2:D3) and uses VSTACK to vertically join both extracted arrays into a single list. The vertical order is determined by the order of the expressions in the formula. Using data set 2, this produces the following:
|
|||||||||||||||||||||||||||||||||||
| Combine data from one table with a filtered set of data from a second table. A use case for combining data from one table with a filtered set of data from a second table is when you want to enrich data in your primary table with specific information from a related table, but only for certain entries. |
=VSTACK(A2:D4, FILTER(A6:D8, B6:B8>50000)) |
Combines Q1 data with filtered Q2 data (where Sales are greater than 50000). Using data set 1, this produces the following:
|
|||||||||||||||||||||||||||||||||||
| Combine data from two separate ranges, extract the unique names, and total each person's values across both periods. | =HSTACK(UNIQUE(VSTACK(A2:A4, A6:A8)), SUMIF(VSTACK(A2:A4, A6:A8), UNIQUE(VSTACK(A2:A4, A6:A8)), VSTACK(B2:B4, B6:B8))) |
This formula uses VSTACK to combine the Q1 and Q2 Name columns into a single vertical list, then UNIQUE returns each distinct name from that combined list. VSTACK is used again to combine the Q1 and Q2 Sales columns, and SUMIF totals the Sales values for each name across both periods. HSTACK then places the unique names and their totals side by side. Using data set 1, this produces the following:
Note: UNIQUE only removes duplicate rows — it does not perform any calculations. Totaling the Sales values for each name requires SUMIF (or a similar function) used alongside UNIQUE, as shown above. |
Notes
- As with all array functions, VSTACK dynamically populates results across adjacent cells, starting from the cell containing the VSTACK function.
- VSTACK:
- Combines multiple ranges from one or more sheets vertically into a single vertical range. The order of the arrays determines the order of stacking. The first array in the function will always be on the top.
- Can stack ranges that have different widths.
- Is useful for combining data from multiple time periods or sources.
- Is useful for appending new records without manual reformatting.
- Keeps data dynamic (if new rows are added, the output updates).
Tips
- If you have a header row and don't want the headers to be included, start your array values one row down (for example in the above data set, start with A2 rather than A1).
- VSTACK can be combined with other functions, including:
Array formula functions
Here are the other array formula functions supported in Workiva spreadsheets.