How to Combine Two Sheets Into One Without Copy-Pasting

"Combine two sheets" means two completely different jobs, and the tools for them share nothing. Work out which one you have before you start.

You have	You want	This is
Same columns, different rows January sales, February sales	One longer list	Stacking
Different columns, a shared key Orders, and customer details	One wider table	Joining

Stacking is the more common question and the easier one. Start there.

Two sheets, one list, and four cells at the bottom proving nothing was lost on the way.

Stacking: the rule nobody tells you

Before any technique: add a Source column.

The moment two sheets become one, you lose the only thing that told you where each row came from. A month later you'll find a row that looks wrong and have no way to know which file it arrived in — and that is precisely when you need to know.

One extra column, filled with the sheet name, costs nothing and answers the question permanently. It also lets you total by source afterwards, which is how you check the merge worked at all.

Then check it added up

Three formulas, every time:

January total   =SUM(January!$D$2:$D$8)
February total  =SUM(February!$D$2:$D$7)
Combined total  =SUM($D$2:$D$14)

Then the one that matters:

=IF(ROUND(Combined-(Jan+Feb), 2)=0, "Yes", "No — check for missed rows")

Count the rows too — COUNTA on both sources against COUNTA on the result. Thirteen in, thirteen out.

⚠️ Note the ROUND(..., 2). Comparing two sums directly with = can report a difference of about 0.0000000001 on figures that are actually identical, because of how computers store decimals. Rounding to the precision you care about is the fix, and it applies to any comparison of two calculated numbers.

This check takes a minute and catches the failure that matters: rows silently left behind because a range stopped one short.

Three ways to stack

1. VSTACK — if you have Microsoft 365

=VSTACK(January!A2:D8, February!A2:D7)

One formula, spills into place, updates when the sources change. It's the right answer where it exists.

The catch is real: VSTACK matches columns by position, not by name. If February has Customer and Order the other way round, VSTACK stacks them anyway and you get a column with order numbers and customer names mixed together, with no error. Check the headers line up before trusting it.

Not available in Excel 2021 or earlier, and a file using it breaks for anyone on an older version.

2. Direct references — small and fixed

For a one-off with a known number of rows, plain references work and are readable:

A2:  =January!A2      (down to row 8)
A9:  =February!A2      (down to row 14)

Fine for two sheets that won't grow. Bad the moment a row is added, because you'll have to shift everything below by hand — which is copy-pasting with extra steps.

3. Power Query — the one that survives next month

If the sheets refill every month, this is the answer. Set it up once and combining becomes a single click.

1. Click inside the first sheet's data, press Ctrl+T to make it a Table. Repeat for the second.
2. Data → From Table/Range. The Power Query editor opens.
3. Home → Close & Load To → Only Create Connection. Repeat for the second table.
4. Data → Get Data → Combine Queries → Append. Pick both.
5. Close & Load to a new sheet.

Next month: paste the new data into the source tables and press Data → Refresh All. That's the whole job.

Append matches columns by header name, not position — the opposite of VSTACK, and the better behaviour. A column present in one table and not the other appears with blanks rather than corrupting the data.

It takes about fifteen minutes to set up the first time. For anything recurring, it repays that on the second run.

Joining: the question to answer first

Joining is pulling customer details onto an order list using a shared key. For a couple of columns, INDEX MATCH does it and needs nothing new.

What matters more than the technique is a decision most spreadsheet users have never been asked to make explicitly: what happens to rows that don't match?

Choice	Result	Right when
Keep all rows	Unmatched rows stay, with blanks in the new columns	Every order matters, even if the customer record is missing
Keep only matched rows	Unmatched rows disappear	You genuinely only want complete records

Power Query asks this outright — Merge Queries offers Left Outer, Inner and the rest by name, and it tells you how many rows matched before you commit.

INDEX MATCH doesn't ask. It keeps every row and puts #N/A in the gaps, and the danger is what people do next: wrap it in IFERROR, see blanks, and never notice that two hundred orders have no customer.

So count them. Before you hide any errors:

=COUNTIF(Customers!$A$2:$A$500, $B2)=0

Total that column. If it isn't zero, you have unmatched rows and a decision to make rather than an IFERROR to write. Matching two lists covers the three reasons a key that looks fine still won't match — and after a join is the wrong time to discover them.

Does this work in Google Sheets?

Sheets is better at stacking than Excel is, with no add-ins at all:

={January!A2:D; February!A2:D}

Curly braces build an array; a semicolon stacks vertically, a comma joins side by side. It updates live and needs nothing installed.

To add the source label as you go:

={January!A2:D, ARRAYFORMULA(IF(January!A2:A="", "", "January"));
  February!A2:D, ARRAYFORMULA(IF(February!A2:A="", "", "February"))}

For joining, Sheets has QUERY, and IMPORTRANGE for pulling from another file entirely — which Excel has no clean equivalent for.

Sheets has no Power Query. For recurring work at scale, that's the one place Excel is clearly ahead.

When it goes wrong

The totals don't reconcile

A range stopped short. Almost always the last row of one source — A2:D7 where the data runs to row 8. This is exactly what the check exists to catch.

Columns are mixed up after a VSTACK

The two sheets have their columns in different orders. VSTACK goes by position. Reorder one sheet, or use Power Query, which goes by name.

Dates in the combined sheet show as numbers

The destination cells aren't formatted as dates. The value is right; format the column.

Refresh doesn't pick up new rows

The source data isn't in a Table, so the query range never grew. Ctrl+T on the source, then refresh.

Duplicate rows after combining

The same records were in both source sheets — an overlap at the month boundary is common. Highlight them and look before deleting; the Source column tells you which file each copy came from.

#REF! after renaming a sheet

A reference was typed as text somewhere. Rebuild it by clicking rather than typing.

Common questions

What if the columns don't match at all?

Make them match first. Add the missing columns to the shorter sheet, leave them blank, and name every header identically — trailing spaces in a header count. Combining mismatched structures produces something no tool can fix afterwards.

Can I combine more than two sheets?

Yes, all the same ways. Past about five, or if new sheets keep appearing, Power Query can point at a whole folder and append every file in it automatically. That's the point at which it stops being worth doing any other way.

Should I keep the original sheets?

Yes. With Power Query you must — it reads them on every refresh. With any method, they're the only place the unmerged data still exists.

Is Power Query available on a Mac?

Yes, and with fewer data connectors than the Windows version. Everything in this article works on both.

Can I combine sheets from different workbooks?

Power Query, yes, and cleanly. With formulas, technically yes and badly — the links break when files move and go stale when they're closed. Copy the sheets into one workbook first.

The short version

• Decide first: stacking (same columns, more rows) or joining (more columns, shared key).
• Add a Source column before you merge. You cannot recover it afterwards.
• Reconcile the totals and the row counts, and round before comparing.
• VSTACK matches by position; Power Query Append matches by header name.
• Power Query is the answer for anything that repeats — set-up cost, one click thereafter.
• Joining: decide what happens to unmatched rows, and count them before hiding the errors.
• In Google Sheets, ={A; B} stacks with no tooling at all.

Next steps: if the keys won't match after a join, the three usual causes are here, and it's worth cleaning each sheet before combining rather than after — cleaning a merged file means fixing the same problem twice. Once combined, a dashboard over the result is the usual reason for doing any of this.