Author: Oscar Cronquist Article last updated on May 06, 2022

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.

Formula in cell E3:

=VSTACK(B2:D4, F2:H:4)

The VSTACK function is available to Excel 365 users and is in the "Array manipulation" category.

1. VSTACK Function Syntax

VSTACK(array1,[array2],...)

Back to top

2. VSTACK Function Arguments

array1 Required. The first cell range or array.
[array2] Optional. The second cell range or array to merge.

Back to top

3. VSTACK Function example

VSTACK function 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:

=VSTACK(B2:D4, F2:H:4)
Note, if any of the values in the source ranges change the same values in the result from the VSTACK function change as well.

3.1 Explaining formula

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)

becomes

VSTACK({89, 68, 19;27, 84, 92;26, 98, 62}, {37, 89, 99;63, 8, 1;100, 31, 70})

and returns

{89, 68, 19;
27, 84, 92;
26, 98, 62;
37, 89, 99;
63, 8, 1;
100, 31, 70}

Back to top

4. VSTACK Function errors

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:

=VSTACK(B2:D4, F2:G:4)

VSTACK function IFNA function

The IFNA function lets you remove #N/A errors.

Formula in cell E4:

=IFNA(VSTACK(B2:D4, F2:G:4), "")

4.1 Explaining formula

Step 1 - Stack cell ranges vertically

VSTACK(B2:D4, F2:G:4)

becomes

VSTACK(
{89, 68, 19;
27, 84, 92;
26, 98, 62},
{37, 89;
63,8;
100, 31})

and returns

{89, 68, 19;
27, 84, 92;
26, 98, 62;
37, 89, #N/A;
63, 8, #N/A;
100, 31, #N/A}

Step 2 - Remove #N/A errors

IFNA(VSTACK(B2:D4, F2:G:4), "")

becomes

IFNA({89, 68, 19;
27, 84, 92;
26, 98, 62;
37, 89, #N/A;
63, 8, #N/A;
100, 31, #N/A}, "")

and returns

{89, 68, 19;
27, 84, 92;
26, 98, 62;
37, 89, "";
63, 8, "";
100, 31, ""}

Back to top

5. VSTACK Function alternative

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:

  1. 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.
  2. Type = (equal sign).
  3. Select with mouse the first cell range B2:D4.
  4. Type a + (plus sign).
  5. Select with mouse the second cell range F2:H4.
  6. Select F2:H4 in the formula.
    VSTACK function alternative1
  7. Press F9 to convert the cell range to constants.
    VSTACK function alternative2
  8. Repeat steps 6 and 7 using the first cell range B2:D4, the result looks like this:
    VSTACK function alternative3
  9. Select the last curly bracket of the first array, the plus sign and the first curly bracket of the second array:
    VSTACK function alternative4
  10. Press Delete to remove those characters.
  11. Type a semicolon ;
  12. Steps 12 to 14 show how to enter the array as an array formula.
    Press and hold CTRL + SHIFT simultaneously.
  13. Press Enter once.
  14. Release all keys.

VSTACK function array formulapng

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)

Back to top

6. Extract unique distinct rows from multiple cell ranges

VSTACK function unique distinct rows

Formula in cell B9:

=UNIQUE(VSTACK(B3:D5, F3:H5), FALSE, FALSE)

Explaining formula

Step 1 - Join cell ranges

VSTACK(array1,[array2],...)

VSTACK(B3:D5, F3:H5)

becomes

VSTACK({89, "Charles", 19;
27, "Tina", 45;
26, "Linda", 62},
{89, "Charles", 24;
27, "Tina", 45;
26, "Laura", 62})

and returns

{89, "Charles", 19;
27, "Tina", 45;
26, "Linda", 62;
89, "Charles", 24;
27, "Tina", 45;
26, "Laura", 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)

becomes

UNIQUE({89, "Charles", 19;
27, "Tina", 45;
26, "Linda", 62;
89, "Charles", 24;
27, "Tina", 45;
26, "Laura", 62}, FALSE, FALSE)

and returns

{89, "Charles", 19;
27, "Tina", 45;
26, "Linda", 62;
89, "Charles", 24;
26, "Laura", 62}

The bolded rows are duplicates, only one instance of those rows in the result.

Back to top

7. VSTACK Function - 3D range

VSTACK function 3D range

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.

Dynamic array formula in cell B3:

=FILTER(VSTACK('1:3'!A2:C10),VSTACK('1:3'!A2:A10)<>"")

7.1 How to enter the 3D range

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:

  1. Double press with left mouse button on cell B3, the prompt appears.
  2. Type: =FILTER(VSTACK(
  3. Go to worksheet: 1
  4. Select cell range A2:C10
    VSTACK function 3D range1
  5. Press and hold SHIFT key.
  6. Select the remaining worksheets, they are 2 and 3 in this example.
    VSTACK function 3D range2
  7. Type the remaining part of the formula and repeat the above steps for the second 3D range.
  8. Press Enter.

7.2 Explaining formula

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)

becomes

VSTACK({89,"Charles",19;
27,"Tina",45;
26,"Linda",62;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0},
{89,"Charles",24;
27,"Tina",45;
26,"Laura",62;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0},
{101,"Ted",21;
203,"Jennifer",24;
105,"Marge",45;
201,"Doris",65;
106,"Meghan",34;
0,0,0;
0,0,0;
0,0,0;
0,0,0}

and returns

{89,"Charles",19;
27,"Tina",45;
26,"Linda",62;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
89,"Charles",24;
27,"Tina",45;
26,"Laura",62;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
0,0,0;
101,"Ted",21;
203,"Jennifer",24;
105,"Marge",45;
201,"Doris",65;
106,"Meghan",34;
0,0,0;
0,0,0;
0,0,0;
0,0,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",19;
27,"Tina",45;
26,"Linda",62;
89,"Charles",24;
27,"Tina",45;
26,"Laura",62;
101,"Ted",21;
203,"Jennifer",24;
105,"Marge",45;
201,"Doris",65;
106,"Meghan",34}

Back to top