How to Use VSTACK Function in Excel

The Excel VSTACK function is a powerful way to combine multiple tables or ranges vertically into a single dataset. It is especially useful when data is maintained separately by departments, branches, months, or business units and needs to be consolidated for reporting and analysis.

Instead of manually copying and pasting records from one table below another, you can use VSTACK to create a dynamic combined list. When the source data changes, the combined result can update automatically.

What Is the VSTACK Function in Excel?

The VSTACK function combines two or more ranges or arrays vertically. It places the second array underneath the first array, the third array underneath the second, and so on.

For example, if a company has separate sales records for Kathmandu, Pokhara, and Biratnagar branches, VSTACK can combine all three datasets into one consolidated table without manually moving the records.

VSTACK Function Syntax

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

The first argument is required, while the additional arrays are optional. You can provide multiple ranges or arrays that you want to combine vertically.

VSTACK Function Arguments

Argument	Description
array1	The first range or array to include in the combined result. This argument is required.
array2	The second range or array to place below the first array.
array3 and onward	Additional ranges or arrays to place underneath the preceding arrays.

How Does VSTACK Work?

Suppose one table contains three rows and another table contains three rows. VSTACK takes the first table and then places the second table directly underneath it.

=VSTACK(A2:D4,F2:I4)

The formula returns a dynamic array containing the records from both ranges. The result spills into the cells below and to the right of the formula automatically.

5 Practical VSTACK Examples

Let us look at five practical examples of the VSTACK function, starting with a simple data consolidation task and progressing to more advanced reporting solutions.

Example 1: Combine Sales Records from Two Departments

Business problem: A company maintains separate sales records for its Retail and Corporate departments. The sales manager needs one combined list to prepare the monthly sales report.

The Retail department has the following records:

The Corporate department maintains its records separately:

Assume the Retail table is in cells A2:D4 and the Corporate table is in F2:I4. To combine them, enter:

=VSTACK(A2:D4,F2:I4)

Excel returns the Corporate records underneath the Retail records, creating one combined six-record dataset.

Why this helps: The sales manager no longer needs to copy and paste departmental records into a master sheet every time a report is prepared. The combined result is generated automatically from the source ranges.

Example 2: Consolidate Monthly HR Records

Business problem: An HR department maintains employee attendance records separately for each month. At the end of the quarter, HR needs one dataset containing all attendance records for analysis.

January records:

February records:

Assume January is in A2:D4 and February is in F2:I4. Use:

=VSTACK(A2:D4,F2:I4)

The result contains the January records followed by the February records.

Why this helps: HR can create a consolidated quarterly attendance dataset and then use functions such as SUMIFS, FILTER, UNIQUE, or PivotTables to analyze employee attendance without manually merging monthly records.

Example 3: Combine Customer Orders from Multiple Branches

Business problem: A retail company has separate order registers for its Kathmandu and Pokhara branches. Head office needs one company-wide order list for monitoring customer demand and sales performance.

Kathmandu branch:

Pokhara branch:

Assume the Kathmandu data is in A2:E4 and the Pokhara data is in G2:K4. Use:

=VSTACK(A2:E4,G2:K4)

VSTACK creates a single dataset containing all six orders.

You can then use the result as the source for other dynamic-array functions. For example, UNIQUE can identify all unique customers, while FILTER can display orders meeting a specific condition.

Why this helps: Head office can maintain a consolidated view while each branch continues to manage its own operational records separately.

Example 4: Combine Regional Sales and Filter High-Value Transactions

Business problem: A company has separate sales tables for three regions. Management wants to combine all regional sales and identify transactions greater than 500,000 without first creating a manually maintained master table.

Assume the East, Central, and West tables are stored in A2:E3, G2:K3, and M2:Q3.

To combine the tables and return only sales above 500,000, use:

=FILTER(VSTACK(A2:E3,G2:K3,M2:Q3),VSTACK(E2:E3,K2:K3,Q2:Q3)>500000)

VSTACK first combines the three regional datasets. FILTER then checks the Sales column and returns only transactions greater than 500,000.

Why this helps: Management can create targeted reports directly from multiple operational datasets without maintaining an additional consolidated worksheet.

Example 5: Create a Dynamic Finance Report from Multiple Departments

Business problem: A finance department receives transaction data from Sales, Services, and Operations. Each department maintains its own transaction table. Finance needs a single report showing only completed transactions, sorted by transaction date.

Assume the three departmental tables are stored in A2:F4, H2:M4, and O2:T4.

To combine the records, keep only completed transactions, and sort them by date, use:

=SORT(FILTER(VSTACK(A2:F4,H2:M4,O2:T4),VSTACK(F2:F4,M2:M4,T2:T4)="Completed"),1,1)

This formula combines three functions:

• VSTACK combines the departmental transaction tables vertically.
• FILTER keeps only records where the Status column contains Completed.
• SORT sorts the resulting records by the first column, which contains the transaction date.

The result is a dynamic finance report that contains only completed transactions and automatically arranges them chronologically.

Why this helps: Finance teams frequently consolidate transaction files from different departments. This formula reduces manual consolidation work and provides a repeatable reporting solution that can be expanded as the source datasets grow.

VSTACK with Different-Sized Ranges

When using VSTACK, the arrays do not have to contain the same number of rows. However, they should ideally have the same number of columns when the datasets represent the same type of information.

For example:

=VSTACK(A2:D5,F2:D10)

This is valid because the ranges can contain different numbers of rows while still having the same number of columns.

However, if the arrays have different numbers of columns, VSTACK fills the missing positions with #N/A.

=VSTACK(A2:D5,F2:H10)

If you want to replace those errors with blank cells, you can use:

=IFERROR(VSTACK(A2:D5,F2:H10),"")

VSTACK with Other Excel Functions

VSTACK becomes even more useful when combined with other dynamic-array functions.

Combination	Purpose
VSTACK + FILTER	Combine datasets and return only records meeting specific conditions.
VSTACK + SORT	Combine datasets and sort the consolidated result.
VSTACK + UNIQUE	Combine datasets and remove duplicate records or values.
VSTACK + IFERROR	Replace errors caused by mismatched column structures.
VSTACK + TAKE	Combine datasets and return only a specified number of rows.
VSTACK + DROP	Combine datasets while removing unwanted rows from the result.

VSTACK vs Copy and Paste

Feature	Copy and Paste	VSTACK
Combines multiple datasets	Yes	Yes
Updates automatically	No	Yes
Requires repetitive work	Yes	No
Creates a dynamic array	No	Yes
Can be combined with FILTER	Not directly	Yes
Suitable for recurring reports	Limited	Excellent

Common Uses of the VSTACK Function

• Combining sales data from multiple branches
• Consolidating monthly reports
• Combining employee records from multiple departments
• Creating a master customer list
• Consolidating inventory records
• Combining regional sales data
• Combining data from multiple worksheets
• Creating dynamic management reports
• Preparing datasets for further analysis
• Building automated reporting systems

Common Mistakes When Using VSTACK

Using Ranges with Different Column Structures

If two datasets represent the same type of information but have different numbers of columns, the resulting array may contain #N/A values. Check the structure of the source tables before combining them.

Including Headers from Every Table

If every source table contains its own header row, VSTACK will also stack those headers. For a clean consolidated dataset, it is often better to include the header only once and stack the data rows underneath it.

Forgetting That the Result Spills

VSTACK produces a dynamic array that spills into neighboring cells. Make sure the destination area is empty. If existing data blocks the result, Excel may return a #SPILL! error.

Frequently Asked Questions About VSTACK

What is the VSTACK function used for?

The VSTACK function is used to combine multiple ranges or arrays vertically into one dynamic array. It is particularly useful for consolidating similar datasets from different departments, branches, worksheets, or reporting periods.

Can VSTACK combine more than two tables?

Yes. You can combine multiple ranges in one formula. For example:

=VSTACK(A2:D10,F2:I10,K2:N10)

Does VSTACK remove duplicates?

No. VSTACK combines the arrays but does not remove duplicates. If duplicate values need to be removed, combine VSTACK with the UNIQUE function.

=UNIQUE(VSTACK(A2:D10,F2:I10))

Can VSTACK combine data from different worksheets?

Yes. For example, if January and February sales are stored on separate worksheets, you can use:

=VSTACK(January!A2:D100,February!A2:D100)

What happens when VSTACK ranges have different numbers of columns?

VSTACK uses the widest array as the width of the result. Missing positions from narrower arrays are returned as #N/A. You can use IFERROR to replace these errors with blanks or another value.

What is the difference between VSTACK and HSTACK?

VSTACK combines arrays vertically, placing one array underneath another. HSTACK combines arrays horizontally, placing one array beside another.

Conclusion

The VSTACK function provides an efficient way to combine multiple Excel ranges or arrays into a single dynamic dataset. It can eliminate repetitive copy-and-paste tasks when an organization receives similar data from multiple departments, branches, months, or business units.

For simple consolidation, VSTACK can combine two or more tables. For more advanced reporting, it can be combined with functions such as FILTER, SORT, UNIQUE, TAKE, and DROP to create automated reports from multiple sources.

If you regularly combine similar Excel datasets manually, learning the VSTACK function can make your reporting process faster, more reliable, and easier to maintain.