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

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

1. Go to a new sheet called “AllData”.
2. 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.

---

📣 Promote Your Excel Skills

Want to learn more Excel automation and dynamic functions like VSTACK, LET, FILTER, etc.?

👉 Mastering MS Excel – A Comprehensive Course
Build job-ready Excel skills with real-world business scenarios and Indian datasets.

---