Combine Data from Multiple Excel Sheets (Pick the Right Method First)

Copying data between sheets by hand is one of those tasks that feels unavoidable right up until you realize it isn't. A colleague of mine spent three hours every Friday doing exactly that: pulling numbers from six department sheets into a summary tab, row by row. I built her a formula solution over a lunch break. The task now takes thirty seconds. If you're manually copying to combine data from Excel sheets right now, you're not saving time by being careful. You're just losing it more slowly.

Before touching anything in your workbook, make one decision: which method fits your situation? Get this wrong and you'll spend twenty minutes setting up Power Query for a one-time merge that copy-paste would've finished in four.

The sheet tabs at the bottom are where consolidation problems usually start — and where the right method ends them.

What You Can Combine and Which Method to Pick Before You Touch Your Workbook

Here's how I frame it for anyone who asks:

Pick your row. Then skip to that section.

Step 1: Pick Your Method to Combine Data from Multiple Excel Sheets

Once you know which method fits, the actual mechanics are simpler than most tutorials make them look. The confusion usually comes from trying all four methods before committing to one.

When Copy and Paste Is Actually the Right Call

For a one-time merge of two or three sheets with identical columns, copy and paste is the right answer. Not a fallback. The right answer. Open each source sheet, select your data range, copy it, go to your destination sheet, click the first empty row below your existing data, and paste. Repeat per sheet. Done in under two minutes if your data is clean.

The one prerequisite: your column headers have to match exactly across every sheet. "Revenue" and "revenue" will not merge cleanly downstream, especially if you're feeding this into a pivot table for dashboards.

When to Use the Consolidate Feature Instead

The Consolidate feature lives under Data → Consolidate and is built for one specific job: summing (or averaging, or counting) numeric data across sheets that share the same structure. Think monthly budget sheets where every row and column means the same thing across January, February, and March.

To use it, open a blank destination sheet, go to Data → Consolidate, select your function (Sum is the default), then add each source range one at a time using the reference picker. Check "Top row" and "Left column" if you want Excel to match by label rather than position. Click OK.

Two things worth knowing before you start: this feature is completely greyed out in Excel for the web as of 2026 — if that's your environment, skip to Power Query. It also consolidates data numerically, so it won't merge text columns. It's a summarization tool, not a row-stacking tool.

When to Use Power Query

If you need to merge data from multiple worksheets where the goal is analysis rather than summarization, Power Query is what you actually want. For getting your data cleaned up before any of this, the guide on preparing data for analysis in Excel is worth reading first.

To append tables in Power Query: go to Data → Get & Transform → Get Data → From File → From Workbook, select your file, choose your sheets in the Power Query Editor, then use Home → Append Queries to stack them. If your sheets have different column headers, Power Query will still append them — it just fills nulls where columns don't match. That's actually useful when you're combining sheets with different columns and don't want to restructure your source data.

Step 2: Use VSTACK or a VBA Macro to Combine Excel Sheets When Formulas Are Faster Than Clicks

With your method selected and your source data at least roughly organized, you've got two more options that skip the click-through interfaces entirely.

VSTACK (Microsoft 365 only) stacks ranges vertically in a single formula. To combine data from three sheets:

=VSTACK(Sheet1!A2:D100, Sheet2!A2:D100, Sheet3!A2:D100)

The result spills into your destination sheet automatically. No refresh needed. The catch: VSTACK doesn't filter out blanks, so if your source ranges have empty rows, they'll show up in the output. Pair it with FILTER to strip them:

=VSTACK(FILTER(Sheet1!A2:D100, Sheet1!A2:A100<>""), FILTER(Sheet2!A2:D100, Sheet2!A2:A100<>""))

That's the combination almost no tutorial explains clearly, and it's the reason pure VSTACK falls apart at scale.

For older Excel versions, a simple VBA macro covers the same ground. Press Alt + F11 to open the VBA editor, insert a new module, and paste this:

Sub CombineSheets()
    Dim ws As Worksheet
    Dim destWs As Worksheet
    Dim lastRow As Long
    Dim destRow As Long

    Set destWs = Sheets("Combined")
    destRow = 2

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Combined" Then
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            ws.Range("A2:D" & lastRow).Copy destWs.Cells(destRow, 1)
            destRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
        End If
    Next ws
End Sub

Create a sheet named "Combined" first, then run the macro. It'll loop through every other sheet and stack the data. Adjust the column range from A2:D to match your actual structure.

Common Mistakes When You Combine Data from Multiple Sheets in Excel

This is the part most tutorials skip. It's also where most of the actual time gets lost.

If you're taking your combined dataset into something like a retail inventory analysis in Excel, clean your data before you consolidate. Fixing structure after the merge is always harder than fixing it before.

If you take one thing from this article: decide which method you need before you open a single dialog box. The method mismatch is what turns a ten-minute task into an afternoon.