How to use the VSTACK function
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array. The VSTACK function is available to Excel 365 users and is in the "Array manipulation" category.
What is VSTACK an abbreviation of?
VSTACK stands for vertical stacking meaning arrays are arranged on top of each other.
What is an array?
An array is an array of values meaning multiple values in its simplest form. Excel functions may accept an array as the input value or argument.
Some Excel functions return an array of values instead of a single value. Excel 365 handles these arrays automatically whereas previous versions need to be entered as an array formula. For example, the FREQUENCY function returns multiple values.
You can also create a formula that returns multiple values, in Excel 365 they are named dynamic array formulas and in earlier versions named array formulas. The most simple dynamic array formula I know is this: = A1:A2, it returns two values populated in cells A1 and A2.
Table of Contents
1. Syntax
The VSTACK function has one required arument and the remaining aruments are optional.
VSTACK(array1,[array2],...)
2. Arguments
| array1 | Required. The first cell range or array. |
| [array2] | Optional. The second cell range or array to merge. |
3. Example
The image above demonstrates how the VSTACK function merges the ranges B2:D4 (blue) and F2:H4 (red). It appends the second cell range (red) to the bottom of the first cell range (blue).
Formula in cell E4:
The VSTACK function is great for consolidating data from multiple worksheets, it is easy to use. Keep in mind to not reference the table headers or you will get table headers from each cell reference in the returning array. Check out formulas that use VSTACK function.
3.1 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - VSTACK function
VSTACK(array1,[array2],...)
Step 2 - Populate arguments
array1 - B2:D4
[array2] - F2:H:4
Step 3 - Evaluate function
VSTACK(B2:D4, F2:H:4)
returns
{89, 68, ... , 70}
4. VSTACK Function errors
The image above demonstrates what happens when you try to append two cell ranges containing a different number of columns. The first cell range (blue) has three columns and the second cell range (red) has 2 columns.
The result is an array containing #N/A errors in locations where no value exists.
Formula in cell E4:
The IFNA function lets you remove #N/A errors.
Formula in cell B8:
The array in cell B8 is now empty of #N/A! errors, see the image above.
4.1 Explaining formula
Step 1 - Stack cell ranges vertically
VSTACK(B2:D4, F2:G:4)
returns {89, 68, ... , #N/A}
Step 2 - Remove #N/A errors
IFNA(VSTACK(B2:D4, F2:G:4), "")
returns {89, 68, ... , 31, ""}
5. VSTACK Function alternative
There is unfortunately no way to merge cell ranges in earlier Excel versions unless you are willing to manually merge the ranges or use a User Defined Function.
The image above shows how to manually merge two cell ranges, this technique works in all Excel versions as far as I know, however, you are required to enter the array as an array formula in order to show all values.
Here are the steps:
- Select cell range B8:D:13. The resulting array is three columns wide and contains six rows, make sure you select a cell range that fits your data.
- Type = (equal sign).
- Select with mouse the first cell range B2:D4.
- Type a + (plus sign).
- Select with mouse the second cell range F2:H4.
- Select F2:H4 in the formula.
- Press F9 to convert the cell range to constants.
- Repeat steps 6 and 7 using the first cell range B2:D4, the result looks like this:
- Select the last curly bracket of the first array, the plus sign and the first curly bracket of the second array:
- Press Delete to remove those characters.
- Type a semicolon ;
- Steps 12 to 14 show how to enter the array as an array formula.
Press and hold CTRL + SHIFT simultaneously. - Press Enter once.
- Release all keys.
The formula has now a leading and ending curly bracket, they appear automatically. Don't enter these characters yourself.
This article describes how to merge cell ranges using a User Defined Function: Combine cell ranges ignore blank cells (User Defined Function)
6. Extract unique distinct rows from multiple cell ranges
This example demonstrates how to extract unique distinct rows/records from multiple non-adjacent cell ranges. The image above shows one data set in B3:D5 and another in F3:H5.
The VSTACK function stacks these data sets on top of each other. The UNIQUE function the merges duplicate rows/records to one distinct row/record for each instance.
Formula in cell B9:
This formula is an Excel 365 dynamic array formula. It is entered as regular formula and it spills values to cells below and to the right of B9 as far as needed. A #SPILL! error is shown if the destination cells are not empty.
Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - Join cell ranges
VSTACK(array1,[array2],...)
VSTACK(B3:D5, F3:H5)
returns {89, "Charles",... , 62}.
Step 2 - Extract unique distinct rows
The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(VSTACK(B3:D5, F3:H5), FALSE, FALSE)
returns
{89, "Charles", ... , 62}
The bolded rows are duplicates, only one instance of those rows in the result.
7. VSTACK Function works with 3D ranges
The image above demonstrates how to use 3D ranges in the VSTACK function. You must have data in the same location on each worksheet for this to work.
What is a 3d range?
A 3d range is a cell reference that references multiple worksheets in only one cell reference. However, you need to know how to create a 3D reference which is easy and the data must be on the same cell range on each worksheet. The image above shows that data on worksheet 1, 2 and 3 are on the same location.
Dynamic array formula in cell B3:
Some of the data sets are smaller than others, this makes the formula get blank values as well from cell ranges that contain blanks. The FILTER function filters out blanks based on if each cell in column A in each sheet is empty.
7.1 How to enter the 3D range cell reference
This is entered as a regular formula, however, the 3D range needs to be explained in greater detail.
To create this reference: '1:3'!A2:C10 follow these steps:
- Double press with left mouse button on cell B3, the prompt appears.
- Type: =FILTER(VSTACK(
- Go to worksheet: 1
- Select cell range A2:C10
- Press and hold SHIFT key.
- Select the remaining worksheets, they are 2 and 3 in this example.
- Type the remaining part of the formula and repeat the above steps for the second 3D range.
- Press Enter.
7.2 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - Merge data from three diffrent worksheets vertically
VSTACK('1:3'!A2:C10)
becomes
VSTACK('1'!A2:C10,'2'!A2:C10,'3'!A2:C10)
returns
{89,"Charles",..., 0}
Step 2 - Remove empty rows
The FILTER function filters values based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(VSTACK('1:3'!A2:C10),VSTACK('1:3'!A2:A10)<>"")
returns
{89,"Charles",...,34}
Useful resources
VSTACK function - Microsoft support
How to combine ranges / arrays in Excel with VSTACK & HSTACK functions
'VSTACK' function examples
This post explains how to lookup a value and return multiple values. No array formula required.
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]
Functions in 'Array manipulation' category
The VSTACK function function is one of 11 functions in the 'Array manipulation' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form