How to Use the VSTACK Function to Combine Multiple Sheets in Excel

Jun 29, 2025

—

by

The VSTACK function in Excel (available in Microsoft 365 and Excel 2021+) allows you to vertically stack arrays or ranges. It’s especially powerful when you want to combine data from multiple sheets into a single list for reporting, analysis, or dashboards.

🧠 Function Syntax:

VSTACK(array1, [array2], …)

array1, array2, … are the ranges or arrays you want to stack vertically.
All ranges must have the same number of columns.

📘 Scenario-Based Example: Combine Sales Data of Multiple Cities

Let’s say you’re maintaining monthly sales data for your retail business across 3 different cities – Delhi, Mumbai, and Kolkata. Each city has its own worksheet with the same structure.

📄 Sheet: Delhi

Name	Product	Sales
Rajesh	Laptop	55000
Anjali	Phone	30000

📄 Sheet: Mumbai

Name	Product	Sales
Vikram	Tablet	20000
Neha	Phone	25000

📄 Sheet: Kolkata

Name	Product	Sales
Arjun	Laptop	60000
Priya	Phone	28000

🛠️ Step-by-Step: Combine All Sheets Using `VSTACK`

Go to a new sheet called “AllData”.
In cell A1, enter this formula:

=VSTACK(Delhi!A2:C3, Mumbai!A2:C3, Kolkata!A2:C3)

✅ This will vertically combine the data from the three sheets into one continuous table.

📌 With Header Row Included

If you also want the headers, you can do:

=VSTACK({"Name","Product","Sales"}, Delhi!A2:C3, Mumbai!A2:C3, Kolkata!A2:C3)

This adds a custom header at the top.

🎯 Tips for Real-World Use

Dynamic Ranges: Use Excel Tables or LET function with named ranges for flexibility.
Error Handling: Use IFERROR inside nested formulas if some ranges might be empty.
Tracking Source Sheet: Add a column with the sheet name:

=VSTACK(
  CHOOSE({1,2,3,4},
    "Delhi", Delhi!A2:A3, Delhi!B2:B3, Delhi!C2:C3),
  CHOOSE({1,2,3,4},
    "Mumbai", Mumbai!A2:A3, Mumbai!B2:B3, Mumbai!C2:C3),
  CHOOSE({1,2,3,4},
    "Kolkata", Kolkata!A2:A3, Kolkata!B2:B3, Kolkata!C2:C3)
)

This adds the city name as a column, useful for filtering and pivoting.