How to Combine Data From Two Excel Sheets

Two files land in your inbox on a Monday. One export is last month’s orders, the other is this month’s, and someone upstairs wants a single report by lunch. So you open both, start copying and pasting, and halfway through you realize the columns don’t even line up the same way. Sound familiar?

Here’s the part almost nobody mentions upfront. There are two completely different jobs hiding inside the phrase “combine data from two sheets in excel,” and picking the wrong one wastes an afternoon. One job stacks rows on top of each other. The other matches columns side by side using a shared ID. Get the diagnosis right and the actual work takes minutes.

Point is, this guide splits by intent first, then hands over the right tool for each. No guessing.

How to Combine Data From Two Sheets in Excel: The Mistake Everyone Makes First

Most guides pick a method and run with it. They’ll walk through VLOOKUP for six paragraphs, or show Power Query and never mention lookups at all. Half the readers land on the wrong page and leave more confused than they arrived.

The real problem isn’t the tool. It’s that people copy-paste manually because they never stopped to ask what shape the data is in. And manual pasting breaks in ways that are easy to miss – a stray blank row here, a column shifted one over there, and suddenly your total is off by a few thousand and nobody knows why.

So before anything else, run this quick check.

• Two sheets, same columns, different records (January orders + February orders)? You need to append rows. That’s a Power Query job.
• Two sheets, shared ID column, different details (order IDs in one, customer emails in another)? You need to match columns. That’s an XLOOKUP or INDEX/MATCH job.

That single question decides everything that follows. Get it wrong and no amount of formula skill saves you.

Job One: Append Rows With Power Query

When you want to merge two excel sheets by stacking one under the other, Power Query is the cleanest route. It’s built into Excel (Data tab, since Excel 2016) and it refreshes, so next month you drop in new data and click one button instead of rebuilding the whole thing.

Microsoft’s own documentation covers the basics of how to combine data from multiple sheets, but the append flow specifically goes like this.

Turn each sheet into a proper Table first. Click anywhere in your data, press Ctrl+T, confirm the headers checkbox. Do it for both sheets. Name them something you’ll recognize, like tblJan and tblFeb, under Table Design.

Then load each into Power Query. With your cursor in the first table, go to Data > From Table/Range. The editor opens. You don’t need to change anything here – just close and load it as a connection only (File > Close & Load To > Only Create Connection). Repeat for the second table.

Now the actual append. Data > Get Data > Combine Queries > Append. Pick your two queries, click OK. Power Query stacks the second table’s rows right under the first, matching columns by header name.

Load the result to a new sheet. Done. And the beauty of the power query append excel approach is the refresh. Next time you get updated files, paste the new rows into your source tables and hit Data > Refresh All. The combined table updates itself.

“Appending isn’t about formulas at all. It’s about telling Excel these two piles are the same kind of thing, then letting it stack them for you every month on demand.”

There’s a solid walkthrough of the multi-sheet version over at Trump Excel’s guide to combining worksheets if you’ve got more than two files to fold in. Same logic, just more queries in the append list.

Job Two: Match Columns With XLOOKUP or INDEX/MATCH

This job is different in kind. Your two sheets describe the same records but hold different columns about them. Sheet one has order IDs and amounts. Sheet two has those same order IDs plus the customer’s email and region. You want one table with all of it side by side.

This is a vlookup between sheets situation, except VLOOKUP is the old, brittle way. XLOOKUP replaced it for anyone on Microsoft 365 or Excel 2021, and it’s worth using instead.

Say your main table sits on Sheet1 with order IDs in column A. The email you want lives on Sheet2, IDs in column A and emails in column B. In Sheet1, cell D2:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")

That reads as: take the ID in A2, find it in Sheet2’s ID column, return the matching email. That last argument, "Not found", is what shows up when there’s no match – which beats VLOOKUP’s ugly #N/A staring back at you.

Drag it down the column and every row pulls its match. Clean.

Stuck on an older Excel version without XLOOKUP? INDEX/MATCH does the same work:

=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

That 0 in MATCH forces an exact match, which is what you almost always want with IDs. Skip it and Excel does approximate matching, which quietly returns wrong values on unsorted data. Classic silent error.

Worth knowing: Power Query can do this matching job too, via Merge instead of Append. For a one-off report a formula is faster to write. For something you’ll rebuild monthly with thousands of rows, the Power Query merge scales better and won’t drag your file to a crawl the way ten thousand XLOOKUP formulas will.

When You Actually Want to Consolidate

There’s a third, quieter option people forget. If your goal is to consolidate data excel style – meaning you want sums or averages rolled up across sheets rather than every raw row preserved – Excel has a dedicated tool for that. Data > Consolidate.

Point it at both ranges, pick your function (Sum, Average, Count), and it aggregates by matching row and column labels. Spreadsheeto’s consolidate walkthrough covers the dialog in detail. It’s the right pick when you don’t need the underlying detail, just the totals – combining regional sales figures into one summary, that kind of thing.

Don’t reach for Consolidate when you actually need every row intact, though. It rolls things up and you lose the granularity. That’s an append job wearing the wrong hat.

Troubleshooting the Combine

Things go sideways in predictable ways. Here’s what usually breaks and the fix.

Duplicate headers after an append almost always mean mismatched column names between your two source tables. Go back, rename so they match exactly – spaces and capitalization included – then refresh.

Lookups returning #N/A on rows you know exist? Nine times out of ten it’s a data type mismatch. One sheet stores the ID as text, the other as a number. They look identical on screen but Excel treats “1024” and 1024 as different things. Convert one side so both match.

Extra blank rows sneaking into your combined table usually trace back to empty rows inside the original data range that got swept in. Filter them out at the source before combining.

And if your file starts crawling after a big lookup job, that’s your signal to move to Power Query. Thousands of volatile formulas recalculating on every edit will bring even a decent machine to its knees. Ablebits has a good overview of the heavier merge scenarios if you’re wrangling multiple large workbooks.

FAQ

Should I use Power Query or a formula to combine two sheets?
Depends on whether the job is a one-off or recurring. For a single report you’ll never touch again, a formula is quicker to write. For anything you rebuild monthly, Power Query wins because it refreshes with one click instead of you rebuilding formulas each time.

What’s the difference between appending and merging?
Appending stacks rows underneath each other when both sheets hold the same kind of records. Merging (or looking up) matches columns side by side using a shared ID when the sheets hold different details about the same records. Different jobs entirely.

Is XLOOKUP better than VLOOKUP for combining sheets?
Yes, if you have it. XLOOKUP handles a built-in “not found” message, looks left or right, and won’t break when someone inserts a column. VLOOKUP is fine for legacy files but there’s no reason to start new work with it on Excel 2021 or 365.

Why does my lookup return errors when the data clearly matches?
Almost always a data type mismatch. One sheet stores the key as text, the other as a number. They look the same but Excel doesn’t treat them as equal. Standardize the format on both sides and the matches resolve.

Can I combine sheets from two different workbooks?
Yes. Power Query connects to external files through Data > Get Data > From File > From Workbook. The append and merge steps work exactly the same once both queries are loaded.

Wrapping Up

Combining two sheets isn’t hard once you stop treating it as one problem. Ask the one question first: same records stacked, or same IDs matched? Append with Power Query for the former, XLOOKUP or INDEX/MATCH for the latter, Consolidate when you only need the totals. When you combine data from two sheets in excel, diagnose, then build. That order saves the afternoon.