How to use the VSTACK function

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.

VSTACK Function Syntax
VSTACK Function Arguments
VSTACK Function example
VSTACK Function errors
VSTACK Function alternative
Extract unique distinct rows from multiple cell ranges
VSTACK function - 3D range

1. VSTACK Function Syntax

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

2. VSTACK Function Arguments

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

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}

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, ""}

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:

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.

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)

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.

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:

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

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}

'VSTACK' function examples

The following 5 articles contain the VSTACK function.

Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Merge three columns into one list
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

Merge two columns
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

Merge two columns with possible blank cells
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

Workaround for the TEXTSPLIT function – LAMBDA function
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns […]

Functions in this article

VSTACK IFNA UNIQUE FALSE FILTER

Functions in 'Array manipulation' category

The VSTACK function function is one of many functions in the 'Array manipulation' category.

CHOOSECOLS function

Returns given columns from a cell range or array.

CHOOSEROWS function

Returns given rows from a cell range or array.

DROP function

Removes a given number of rows or columns from a 2D cell range or array.

EXPAND function

Increases a cell range or array by a specified number of columns and rows.

HSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

TAKE function

Returns a given number of rows or columns from a 2D cell range or array.

TOCOL function

Rearranges values in 2D cell ranges to a single column.

TOROW function

Rearranges values from a 2D cell range to a single row.

VSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

WRAPCOLS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPROWS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the VSTACK function

Table of Contents

1. VSTACK Function Syntax

2. VSTACK Function Arguments

3. VSTACK Function example

3.1 Explaining formula

Step 1 - VSTACK function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. VSTACK Function errors

4.1 Explaining formula

Step 1 - Stack cell ranges vertically

Step 2 - Remove #N/A errors

5. VSTACK Function alternative

6. Extract unique distinct rows from multiple cell ranges

Explaining formula

Step 1 - Join cell ranges

Step 2 - Extract unique distinct rows

7. VSTACK Function - 3D range

7.1 How to enter the 3D range

7.2 Explaining formula

Step 1 - Merge data from three diffrent worksheets vertically

Step 2 - Remove empty rows

Excel '365' functions

'VSTACK' function examples

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment