How to Combine Multiple Sheets in Excel with VSTACK

Get the VSTACK Practice Workbook

Every example from the tutorial in one ready-to-use file. Combine Tables, stack ranges across sheets, clear the zeros, and set up the bookend method that picks up new sheets on its own.

What is the VSTACK function in Excel?

The Excel VSTACK function allows you to vertically stack multiple ranges or sheets into one dynamic list.

It is the modern, formula-based alternative to manual copy-pasting or complex Power Query appends for internal data consolidation.

How do I use the VSTACK function? (Basic syntax)

The syntax for VSTACK is straightforward:

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

• array1: The first range of data you want to stack.
• [array2], …: Additional ranges to include in the stack.

Each array represents a set of data. VSTACK combines these arrays into one continuous column.

VSTACK with Excel Tables (auto-expanding)

If your sources are Excel Tables, VSTACK grows on its own as you add rows. No fixed range, no bookends needed.

A plain range has a fixed size, so you either guess how many rows to include or stretch it and deal with zeros. Excel Tables solve that. Reference two Tables in VSTACK:

=VSTACK(TableSouth, TableWest)

Add a row to either Table, and it flows straight into the combined list. The Table expands, so VSTACK expands with it.

Keep the two ideas separate in your head:

• Excel Tables handle new rows automatically.
• The bookend method (further down) handles new sheets automatically.

Most people mix these up. They are different problems with different fixes.

How do I combine multiple sheets with VSTACK? (3D references)

Why ‘Bookend’ Sheets are the Secret to Automation

Do you have several sheets with similar tables in Excel?

Here’s how you can combine all the data into one table using the VSTACK function.

Step-by-Step Instructions

1. Create a Consolidated Sheet:

Open a new sheet and name it “ConsolidatedData.”

2. Add Headers:

In cell A1, type the column headers from one of your tables.

3. Enter the VSTACK Formula:

In cell A2, enter this formula:

=VSTACK('R10-1:R40-3'!A2:G50)

💡 Tip: The sheet names “R10-1” and “R40-3” act as bookends. Any sheets placed between these two will be included in the formula. This is called a 3D Range Reference.

4. Understand the Output:

• The formula stacks all data from the range A2in every sheet between “R10-1” and “R40-3.”
• You’ll get a single table combining all the rows.

How do I remove zeros and blank rows from VSTACK?

When you use the VSTACK function, you might see rows filled with zeros where there’s no data.

These rows aren’t actual data—they’re placeholders for empty cells.

Method 1: The trim-reference dot

The fastest way to remove zeros from VSTACK is the trim-reference dot. Add a period right after the colon in your range, and the empty rows disappear.

When you stretch a range to leave room for future data, VSTACK fills the empty cells with zeros. Annoying, but there is a one-character fix.

Take each range and add a dot straight after the colon:

=VSTACK(Jan!A2:.D20, Feb!A2:.D20)

That dot is the trim operator. It tells Excel to ignore the trailing empty rows in each range. The zeros are gone, and the result still updates when you add new data.

💡 Tip: This is relatively new in Microsoft 365. If the dot does nothing for you, your Excel version is likely older.

One catch: the dot does not work on a 3D reference across sheets.

If you built your stack by shift-selecting from the first sheet to the last, the dot has no effect and the zeros stay. For that setup you need FILTER, which is next.

Method 2: The FILTER function

Let’s fix that by using the Excel FILTER function.

=FILTER(VSTACK('R10-1:R40-3'!A2:G50), VSTACK('R10-1:R40-3'!A2:A50) <> "")

What This Formula Does:

• FILTER Function: Filters out rows where column A is empty.
• VSTACK in the Include Argument: Checks if column A has any data (<> "") to decide which rows to keep.

After applying the FILTER formula, your table will exclude all blank rows.

The final output will include only rows with actual data.

Discover how to use the Excel FILTER function with step-by-step examples in our detailed article.

How do I sort VSTACK results with the SORT function?

Once you’ve combined and cleaned your data using VSTACK and FILTER, you might need to sort the results.

Whether it’s by product name or sales figures, Excel’s SORT function makes it easy.

Step 1: Sorting by a Single Column

Here’s how to sort your data by a single column, like “Product Name”:

Enter this formula in your sheet:

=SORT(FILTER(VSTACK('R10-1:R40-3'!A2:G50), VSTACK('R10-1:R40-3'!A2:A50) <> ""), 4)

• FILTER Function: Removes blank rows from the stacked data.
• SORT Function: Sorts the data by the 4th column (e.g., “Product Name”).

• The number 4 in the formula is the sort_index. It tells Excel to sort by the 4th column in the range.
• By default, the SORT function arranges data in ascending order.

Step 2: Multi-Level Sorting

Need to sort by more than one column? For example, “Total Sales” within each “Product Name”?

Use this modified formula:

=SORT(FILTER(VSTACK('R10-1:R40-3'!A2:G50), VSTACK('R10-1:R40-3'!A2:A50) <> ""), {4,7}, {1,-1} )

Here’s what it does:

1. Primary Sort (Column 4):
   • {4,7} tells Excel to sort first by the 4th column (“Product Name”).
   • 1 specifies ascending order for this column.
2. Secondary Sort (Column 7):
   • Excel then sorts by the 7th column (“Total Sales”).
   • -1 specifies descending order for this column.

Understanding the Curly-Braces

If curly braces {} are new to you, here’s a simple explanation:

• Curly braces allow Excel to handle multiple inputs at once.
• In this formula:
  • {4,7} lists the columns to sort by (4th and 7th columns).
  • {1,-1} specifies the sort direction for each column (ascending for column 4, descending for column 7).

How do I summarize VSTACK data across sheets? (GROUPBY)

To summarize your combined data without a PivotTable, wrap it in GROUPBY. Point the row and value arguments at your spill range with CHOOSECOLS, and Excel builds the report for you.

Once your data is stacked into one list, the next step is usually to analyze it. You could drop a PivotTable on the spilled range. But if you want a report that is pure formula and updates on its own, use GROUPBY.

Say your combined list spills from A1 and you want total revenue by product. Product sits in column 4, revenue in column 6.

=GROUPBY(CHOOSECOLS(A1#,4), CHOOSECOLS(A1#,6), SUM)

Here is what each part does:

• CHOOSECOLS(A1#,4) grabs the product column. The A1# refers to the whole spilled VSTACK result.
• CHOOSECOLS(A1#,6) grabs the revenue column you want to summarize.
• SUM is how the numbers get rolled up. GROUPBY gives you plenty of other options here.

Press enter and you get revenue by product, with the total added at the bottom automatically.

💡 Tip: In the practice file, the same report is built with VSTACK nested directly inside GROUPBY, so it is one formula instead of two. I kept them separate here so each step is easy to follow.

And it stays live. Add a new product to any source sheet, and both the combined list and the GROUPBY summary pick it up. No refresh, no rebuild.

How do I format VSTACK output to match my source data?

By default, Excel does not carry over cell formatting from the original data to the VSTACK output.

Here’s how to fix this:

1. Manually Apply Formatting:
   • After creating your VSTACK formula, select the output range.
   • Go to the Home tab and use the Format Cells option to apply the same styles as your source data.
2. Use Conditional Formatting (Optional):
   • If your source data had conditional formatting, you can replicate it in the output by applying the same rules in the Conditional Formatting menu.

💡 Learn more about conditional formatting in our detailed article.

Why does VSTACK show a circular reference error?

If you’re combining data within the same workbook, be careful where you place the output sheet. Here’s why:

The Problem:
If your output sheet is included in the range of sheets used in your VSTACK formula, Excel will create a circular reference error.

This happens because the formula tries to reference its own results, creating an endless loop.

The Solution:

Always place the output sheet outside the range of sheets being stacked.

For example, if your formula references sheets “Sheet1” to “Sheet3,” place the output on a new sheet named “ConsolidatedData” outside this range.

How do I make VSTACK auto-include new sheets? (Bookend method)

If you’re combining data from multiple sheets with Excel’s VSTACK function, adding a new sheet (like “Apr” for a new month) can break your formula.

Let’s explore how to fix this issue without constantly updating the formula.

The Problem: Adding New Sheets

Imagine you’re combining data from sheets named “Jan,” “Feb,” and “Mar” using this formula:

=VSTACK(Jan:Mar!A2:B50)

If you add a new sheet called “Apr,” Excel won’t include it in the VSTACK results because it falls outside the defined range (Jan:Mar).

This means you’ll have to update the formula every time you add a new sheet.

The Solution: Use Bookend Sheets

A better way to handle this is by creating permanent bookend sheets called “Start” and “End.”

These sheets act as placeholders, and any new sheet added between them is automatically included in the VSTACK formula. Here’s how to set it up:

1. Create Bookend Sheets:
Add two blank sheets named “Start” and “End.”

Place them before and after all the sheets you want to include in your formula.

2. Update the Formula:
Rewrite your VSTACK formula to reference the bookend sheets:

=VSTACK(Start:End!A2:B50)

3. Add New Sheets:
When adding a new sheet (e.g., “Apr”), place it between the “Start” and “End” sheets.

It will automatically be included in the results.

4. Removing Blank Rows from Bookend Sheets

Since the “Start” and “End” sheets don’t have data, they’ll introduce blank rows into your VSTACK output.

Use the FILTER function to clean up the results:

=FILTER(VSTACK(Start:End!A2:B50), VSTACK(Start:End!A2:A50) <> "")

How do I automatically sort sheets inside the Bookends? (VBA)

When using bookend sheets like “Start” and “End” for your VSTACK formula, it’s essential to ensure that all new sheets fall between them.

Instead of manually rearranging sheets, you can use VBA (Visual Basic for Applications) to sort them automatically.

Before You Start

To use VBA, you need to save your Excel workbook as a macro-enabled file (.XLSM). Here’s how:

1. Go to File > Save As.
2. In the “Save as type” dropdown, select Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

Step 1: Open the Visual Basic Editor

1. Use the keyboard shortcut Alt + F11 to open the Visual Basic Editor.
2. In the editor, go to Insert > Module.
3. A blank module sheet will appear.

Step 2: Add the VBA Code

Copy and paste the following code into the module sheet:

Sub Sort_Sheets()
  Dim CurrentSheetIndex As Integer
  Dim PrevSheetIndex As Integer
  For CurrentSheetIndex = 1 To Sheets.Count
    For PrevSheetIndex = 1 To CurrentSheetIndex - 1
      If UCase(Sheets(PrevSheetIndex).Name) > _
      UCase(Sheets(CurrentSheetIndex).Name) Then
        Sheets(CurrentSheetIndex).Move _
        Before:=Sheets(PrevSheetIndex)
      End If
    Next PrevSheetIndex
  Next CurrentSheetIndex
End Sub

Step 3: Run the Code

1. Close the Visual Basic Editor by pressing Alt + Q.
2. Back in Excel, press Alt + F8 to open the Macro dialog box.
3. Select Sort_Sheets from the list and click Run.

The macro will sort your sheets alphabetically, ensuring that any new sheets fall between “Start” and “End.”

Advanced Sheet Sorting in Excel

The problem with this code is that it sorts alphabetically in ascending order. 

Since our sheet tabs have month name abbreviations as well as the bookend names, we will get an undesirable sort result.

You can take sorting to the next level by renaming your sheets. This method ensures they are sorted logically, even when new sheets are added.

Step 1: Understand the Naming Convention

To sort sheets in a meaningful order, we use specific characters that Excel recognizes for sorting:

• Periods (.): Sheets starting with a period are sorted before those starting with letters or numbers.
  • Two periods (..): Sorts before one period.
  • Example: ..2022 Sales will appear before .Start.
• Tilde (~): Sheets starting with a tilde are sorted last.
  • Example: ~End will always appear after all other sheets.
• M-YY Format: Using month numbers (e.g., “3-22” for March 2022) forces chronological sorting.

Step 2: Rename Sheets for Proper Sorting

Here’s how to rename your sheets to ensure logical order:

1. Rename month sheets using month number and year:
   • Example: Change Jan to 1-22, Feb to 2-22, and so on.
2. Add special characters to bookend sheets:
   • Rename Start to .Start.
   • Rename End to ~End.
3. Use double periods for key sheets:
   • Rename 2022 Sales to ..2022 Sales.

Step 3: Update the VSTACK Formula

After renaming your sheets, update your VSTACK formula to reflect the new names.

Use single quotes to handle special characters in sheet names:

=VSTACK('.Start:~End'!A2:G50)

Step 4: Run the Sorting Macro

Once the sheets are renamed, you can sort them alphabetically to maintain the desired order:

1. Open the Visual Basic Editor (press Alt + F11).
2. Use the sorting macro provided above.
3. Save your workbook as a macro-enabled file (.XLSM).
4. Run the macro (press Alt + F8 and select Sort_Sheets).

Bonus Tip: Hide Bookend Sheets

To keep your workbook tidy, you can hide the .Start and ~End sheets:

1. Right-click the .Start or ~End tab.
2. Select Hide.

These sheets will remain functional but won’t be visible to users.

VSTACK vs. Power Query: Which One Should You Use?

VSTACK FAQ

How do I remove zeros from VSTACK without FILTER?

Add the trim-reference dot to your range. Place a period right after the colon, like South!A2:.G40, and VSTACK ignores the trailing empty rows. This works when you stack normal ranges. It does not work on a 3D reference across sheets, where you still need FILTER.

Why doesn’t the dot trick work with my 3D reference?

The trim-reference dot only applies to a normal range. A 3D reference, the kind you build by shift-selecting from the first sheet to the last, does not support it, so the zeros stay. Wrap your VSTACK in FILTER instead to drop the blank rows.

Can VSTACK summarize data, not just combine it?

No. VSTACK only stacks data into one list. To summarize it, pair it with GROUPBY for a formula-based report, or drop a PivotTable on the spilled result. GROUPBY updates automatically as new data comes in.

How do I combine multiple sheets in Excel with one formula?

Use =VSTACK(‘FirstSheet:LastSheet’!A2:G100). This stacks data from every sheet between FirstSheet and LastSheet into one dynamic array. For automatic inclusion of new sheets, create two blank bookend sheets named ‘Start’ and ‘End’ and reference ‘Start:End’ in the formula.

How do I remove zeros and blank rows from VSTACK results?

Excel returns a zero for empty cells inside a VSTACK range. To remove them, wrap the formula in FILTER:

=FILTER(VSTACK('Start:End'!A2:G100), VSTACK('Start:End'!A2:A100)<>"")

Does VSTACK update automatically when I add new worksheets?

Yes, if you use the bookend method. Create two blank sheets named ‘Start’ and ‘End’. Any new sheet dragged between them is automatically included in the VSTACK results, and deleted sheets are removed on the next recalculation.

When should I use VSTACK instead of Power Query?

Use VSTACK when you need instant, live updates on data within the same workbook and your sheets share the same column structure. Use Power Query for larger datasets, external files, messy data that requires unpivoting, or when the source workbook may be closed.

What is the difference between VSTACK and HSTACK?

VSTACK stacks arrays vertically, appending rows below each other. HSTACK stacks arrays horizontally, placing columns side by side. Both take the same arguments. They differ only in direction.

Why is VSTACK not showing up in my Excel?

VSTACK is a dynamic array function exclusive to Microsoft 365 and Excel for the web. It is not available in perpetual versions like Excel 2021, 2019, or earlier. Excel 2024 also supports it.

What is the difference between VSTACK and HSTACK?

VSTACK stacks arrays vertically, appending rows below each other. HSTACK stacks arrays horizontally, placing columns side by side. Both functions take the same arguments and work the same way, they differ only in direction.

When to use VSTACK

Use VSTACK when your source tables share the same columns but hold different rows. Classic examples:

• Monthly sales sheets (Jan, Feb, Mar) stacked into a yearly list
• Regional data (North, South, East, West) combined into one master table
• Multiple forms with the same fields rolled up for reporting

The formula looks like this:

=VSTACK(Jan!A2:D50, Feb!A2:D50, Mar!A2:D50)

When to use HSTACK

Use HSTACK when your source tables share the same rows but hold different columns. Classic examples:

• Adding a new metric column (like “2026 Forecast”) next to existing columns
• Combining two datasets that share an ID column but have different attribute columns
• Building a side-by-side comparison view

The formula looks like this:

=HSTACK(A2:A50, Forecast!B2:B50, Forecast!C2:C50)

Quick reference

💡 Pro tip: You can nest them. =HSTACK(VSTACK(Jan:Mar!A2:A50), VSTACK(Jan:Mar!B2:B50)) stacks rows from three months and then places the results in side-by-side columns.

Useful when you need to reshape data before feeding it into a chart or PivotTable.

Can I use Copilot to combine sheets instead of VSTACK?

Yes. If you have Microsoft 365 with Copilot access, you can combine sheets using plain-language prompts through Edit with Copilot in Excel. This is the default mode when you open the Copilot pane in the ribbon.

Example prompt

Open the Copilot pane in Excel and type:

“Combine all sheets between ‘Start’ and ‘End’ into one table on a new sheet called Combined. Keep the headers from the first sheet and skip blank rows.”

Copilot writes the VSTACK formula, places it on a new sheet, and applies the cleanup steps for you.

Requirements

• Subscription: Microsoft 365 Personal, Family, or Premium with AI credits, a commercial Microsoft 365 Copilot subscription, or a Copilot Chat-eligible business plan
• File location: Your workbook must be an .xlsx file saved to OneDrive or SharePoint
• Excel version: Microsoft 365 desktop (Windows or Mac) or Excel for the web

When Copilot beats VSTACK

Copilot is faster when you need to combine sheets with different column orders, slightly different headers, or partial overlap. Writing a VSTACK formula to handle those edge cases gets messy. Copilot handles the reshaping in one prompt.

When VSTACK still wins

• No subscription needed beyond Microsoft 365. Copilot requires AI credits or a separate license
• Works offline. VSTACK recalculates without any cloud dependency
• Transparent and auditable. A formula shows exactly what it does. A Copilot-generated table can be harder to debug when numbers look off
• Instant updates. VSTACK recalculates as you type. Copilot output is static until you re-prompt

For most repeatable reporting workflows, VSTACK is still the right call. Reach for Copilot when the sheets don’t match cleanly or when a one-time consolidation is all you need.

Where is VSTACK available? (Excel versions)

The Excel VSTACK Function is available in Excel for Microsoft 365 and in Excel for the web.

Download the VSTACK Practice Workbook

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses