Analistable

How to merge Google Sheets into one

By the Analistable team · Updated · 5 min read

To merge tabs in the same file, create a new tab and enter =QUERY(VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D), "select * where Col1 is not null", 0). To merge other files, replace each range with IMPORTRANGE("spreadsheet URL", "Tab!A2:D") and click Allow access once per file. The result updates automatically when the sources change.

Part of our guide: How to combine data in Google Sheets

Merge tabs in one spreadsheet

  1. Add a tab called Combined and type the header row in row 1.
  2. In A2, enter =VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D).
  3. Open-ended ranges (A2:D) include empty rows at the bottom of each tab, which appear as gaps. Wrap the formula in QUERY to drop them:
=QUERY(VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D), "select * where Col1 is not null", 0)

Inside QUERY, columns from a combined range are called Col1, Col2… not A, B. The final 0 tells QUERY there's no header row in the data.

={Jan!A2:D; Feb!A2:D} is the older array-literal way to do the same. In locales that use a comma as the decimal separator, the column separator inside {} is a backslash and the row separator is a semicolon.

Add a column showing the source tab

=ARRAYFORMULA(LET(
  j, FILTER(Jan!A2:D, Jan!A2:A<>""),
  f, FILTER(Feb!A2:D, Feb!A2:A<>""),
  VSTACK(HSTACK(IF(INDEX(j,,1)<>"", "Jan", ""), j),
         HSTACK(IF(INDEX(f,,1)<>"", "Feb", ""), f))
))
Combined tab with a source column
TabDateRegionProductUnits
Jan2026-01-04NorthDesk12
Jan2026-01-09SouthChair30
Feb2026-02-02NorthLamp14

Merge separate Google Sheets files

Use IMPORTRANGE for each file inside VSTACK:

=QUERY(VSTACK(
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/AAA…", "Sales!A2:D"),
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/BBB…", "Sales!A2:D")
), "select * where Col1 is not null", 0)

The first time, each IMPORTRANGE shows #REF! with an Allow access button. Click it once per source file. Tips for many files, permissions and slow loading: using multiple IMPORTRANGE formulas.

Merge without formulas

Merging matches columns by position. If tabs have different column orders, reorder them with QUERY's select (for example select Col2, Col1, Col3) before stacking.

Common problems

Problems when merging Google Sheets
ProblemFix
Gaps between the blocksWrap in QUERY(..., "where Col1 is not null", 0)
#VALUE! or #N/A paddingTabs have different numbers of columns; select the same width from each
Data in wrong columnsTabs have different column orders; reorder with QUERY select
#REF! on IMPORTRANGEClick Allow access in the cell

Which method should you use?

Choosing a way to merge Google Sheets
SituationMethodUpdates automatically
A few tabs in one fileVSTACK or { ; } wrapped in QUERYYes
A few separate filesIMPORTRANGE inside VSTACKYes
Dozens of files or tens of thousands of rowsApps Script on a scheduleOn the schedule
A one-off merge to send to someoneCopy and paste, or download and combineNo
Sheets plus Excel or CSV filesDownload, then combine with a tool or Power QueryNo

Tabs with different column orders

Suppose Jan has Date, Region, Product, Units but Feb was set up as Date, Product, Region, Units. Stacking them directly puts products under Region for every February row. Reorder Feb inside the stack with CHOOSECOLS:

=QUERY(VSTACK(Jan!A2:D, CHOOSECOLS(Feb!A2:D, 1, 3, 2, 4)), "select * where Col1 is not null", 0)

CHOOSECOLS(Feb!A2:D, 1, 3, 2, 4) returns Feb's columns in the order Date, Region, Product, Units, matching Jan. If a tab is missing a column entirely, add the column (left blank) to that tab so every tab has the same width; otherwise VSTACK pads the short rows with #N/A.

Summarise the merged data

Once the rows are stacked you can total them in the same formula, without a separate Combined tab:

=QUERY(VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D),
  "select Col3, sum(Col4) where Col1 is not null group by Col3 label sum(Col4) 'Units'", 0)

This returns one row per product with its total units across the three months. For more grouping patterns, see QUERY with multiple ranges. To add up a single value across tabs, SUMIF across multiple sheets is simpler.

Check the merge

  1. Count the rows in each source: =COUNTA(Jan!A2:A), and so on.
  2. Count the rows in the result: =COUNTA(Combined!A2:A). It should equal the sum. With 120, 98 and 131 rows in Jan, Feb and Mar, expect 349.
  3. Compare a total: =SUM(Jan!D2:D)+SUM(Feb!D2:D)+SUM(Mar!D2:D) should equal =SUM(Combined!D2:D).

A lower row count usually means a tab has a blank first cell in some rows, which the Col1 is not null filter removed. Filter on a column that is always filled, such as an ID.

Adding next month's tab

The formula doesn't pick up new tabs on its own: when you add Apr, add Apr!A2:D to the VSTACK. To avoid editing the formula every month, keep all months on one tab with a Month column from the start, or let a script build the list of tabs. Rows added to existing tabs are included automatically because the ranges are open-ended.

Merging tabs that have their own header rows

Starting each range at row 2 skips the header on every tab, which is why the Combined tab needs its own header typed in row 1. If some tabs have two header rows, or a title above the header, start that tab's range lower (for example Mar!A3:D) rather than filtering the extra rows out afterwards. Alternatively, include headers and let QUERY drop them: add and Col1 <> 'Date' to the where clause, which removes any row whose first cell is the header text.

Frequently asked questions

How do I merge multiple tabs into one in Google Sheets?
Use =VSTACK(Tab1!A2:D, Tab2!A2:D) on a new tab, wrapped in QUERY(..., "select * where Col1 is not null", 0) to remove blank rows.
How do I merge two Google Sheets files?
Use IMPORTRANGE for each file inside VSTACK, and allow access the first time.
Will the merged sheet update automatically?
Yes. VSTACK, QUERY and IMPORTRANGE recalculate when the source data changes.
Can I merge Google Sheets with an Excel file?
Not with a formula. Upload the Excel file to Drive and open it as a Google Sheet, then use IMPORTRANGE, or download the Sheet as .xlsx and combine both files outside Sheets.

Related guides