How to Combine Multiple Worksheets in Excel (3 Methods + VSTACK)

Q: Can I combine worksheets from different workbooks?

Yes! Use Consolidate with Browse to add external files, or VBA to loop through workbooks.

Q: Can I update merged data automatically?

Yes! Check Create links to source data in Consolidate, or use VSTACK for dynamic updates.

CHOOSE YOUR METHOD

VSTACK vs Power Query vs VBA — which fits you?

Method	Best for	Version needed
VSTACK()	Quick stack of identical tables	Microsoft 365 / 2024
Power Query	Clean + combine messy data	2016+
VBA	Repeatable automation	All versions

QUICK DIAGNOSIS

What's your merge goal?

Your Need	Recommended Method	Key Consideration
📊 Aggregate by category (SUM/AVERAGE)	Consolidate Function	✅ Needs consistent structure, supports label matching
📋 Simple stacking (365 users)	VSTACK Formula	✅ Dynamic updates, older versions need alternatives
⚙️ Batch merge sheets/workbooks	VBA Macro	⚠️ Needs backup, for advanced users

⚠️ Critical Warning: Before merging, ALWAYS check your data source structure! Same layout, no blank rows/columns, and identical labels are the keys to successful merging.

MERGE METHODS

Choose Your Method

Consolidate Function (Aggregate by Category)

Best for aggregating data by category (SUM/AVERAGE) when sheets share the same structure.

1

Click Data > Consolidate

Go to the Data tab → Click Consolidate.

2

Choose the Function

Select the aggregation function: SUM, AVERAGE, COUNT, etc.

3

Add Source Ranges

Click Add for each sheet's data range. Check Top row and Left column if your sheets have labels.

4

Click OK to Generate

Click OK. The combined table appears at your selected location.

⚠️ Safety Alert: Mismatched labels cause merge failures! Make sure all source tables have IDENTICAL headers — including no extra spaces or typos.

✅ Pro Tip: Check "Create links to source data" to keep the merged table auto-updating when source data changes!

VSTACK Formula (Simple Stacking)

Best for simply stacking data rows. Available in Microsoft 365 and Excel 2024.

1

Select the Target Cell

Click the cell where you want the stacked data to begin.

2

Enter the VSTACK Formula

Type: =VSTACK(Sheet1!A1:D10, Sheet2!A1:D10)

3

Press Enter

Press Enter. The data from both sheets automatically stacks vertically.

✅ Advantage: VSTACK supports dynamic updates — when source data changes, the merged table updates automatically!

⚠️ Limitation: VSTACK only works on Microsoft 365, Excel 2024, and Excel Web. Older versions need INDEX+ROW or Power Query alternatives.

💡 Scenario Tip: For older Excel, use this alternative:
=INDEX(A:A, ROW()) combined with row-offset logic — or simply use Power Query's Append function.

VBA Macro (Batch Merge for Advanced Users)

Automate merging multiple sheets or workbooks into one.

⚠️ VBA Warning

VBA operations cannot be undone! Always backup your workbook before running macros. Only suitable for users familiar with VBA.

1

Open VBA Editor

Press Alt + F11 (Windows) or Fn + Option + F11 (Mac).

2

Insert a New Module

Click Insert → Module to create a new code module.

3

Paste the Merge Code

Copy and paste this code to merge all sheets into a "Combined" sheet:

Sub CombineSheets()

  Dim ws As Worksheet

  Dim target As Worksheet

  Dim lastRow As Long, copyRow As Long

  Set target = Worksheets.Add

  target.Name = "Combined"

  copyRow = 1

  For Each ws In Worksheets

    If ws.Name <> "Combined" Then

      ws.UsedRange.Copy target.Rows(copyRow)

      copyRow = copyRow + ws.UsedRange.Rows.Count

    End If

  Next ws

End Sub

4

Run the Macro

Press F5 to run. All sheets get merged into a new "Combined" sheet.

✅ Pro Tip: This VBA code copies ALL sheets. Modify the If ws.Name <> "Combined" line to exclude specific sheets you don't want merged.

ABOUT THIS GUIDE

About This Guide

Three ways to merge sheets, each for a different job: Consolidate (aggregate by label), VSTACK (stack rows dynamically, Excel 365/2024 only), and VBA (batch merge across workbooks). The right choice depends on whether your sheets share identical headers.

FREQUENTLY ASKED

Frequently Asked Questions

How to combine multiple worksheets with different structures?

Use VBA or Power Query for flexible merging. Consolidate and VSTACK require consistent structures.

Can I combine worksheets from different workbooks?

Yes! Use Consolidate with "Browse" to add external files, or VBA to loop through workbooks.

Why does my Consolidate result have extra rows?

Labels don't match! Ensure all source tables have identical headers and no extra spaces.

How to use VSTACK on older Excel versions?

Use INDEX+ROW combination or Power Query as alternatives to VSTACK.

Can I update merged data automatically?

Yes! Check "Create links to source data" in Consolidate, or use VSTACK for dynamic updates.

How to combine worksheets on Mac?

Consolidate and VSTACK work on Mac. VBA requires Mac-compatible code adjustments.

CONTINUE LEARNING

Merged Your Data? Keep It Organized!

🛡️

Protect Merged Data

Prevent accidental edits to your combined table. Learn the correct protection method.

Learn More →

🖨️

Print Combined Sheets

Print your merged data correctly. Page setup, preview, and format fixes.

Learn More →

📑

Group Worksheets

Edit multiple merged sheets at once. Master batch operations.

Learn More →

QUICK LINKS

Continue Exploring

SOURCES

Sources & References

This guide was written and fact-checked against Microsoft's official Excel documentation.

Consolidate data in multiple worksheets — Microsoft Support