Analistable

How to combine data in Google Sheets

By the Analistable team · Updated · 12 min read

To stack tabs in one Google Sheet, use =VSTACK(Jan!A2:D, Feb!A2:D) or the array literal ={Jan!A2:D; Feb!A2:D}, then wrap it in QUERY(..., "where Col1 is not null") to drop blank rows. To pull data from other files, use IMPORTRANGE. To match two sheets by an ID, use XLOOKUP.

Pick the right function

Google Sheets functions for combining data
GoalFunctionExample
Stack tabs in the same fileVSTACK or { ; }=VSTACK(Jan!A2:D, Feb!A2:D)
Stack and filter in one stepQUERY over an array=QUERY({Jan!A2:D; Feb!A2:D}, "where Col1 is not null")
Bring in data from another fileIMPORTRANGE=IMPORTRANGE("url", "Sales!A1:D")
Match rows by a keyXLOOKUP=XLOOKUP(A2, Customers!A:A, Customers!B:B)
Combine text from cellsTEXTJOIN or &=TEXTJOIN(" ", TRUE, A2:C2)

Stack tabs into one

Open a new tab and enter =VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D). Open-ended ranges such as A2:D include every row, so blank rows at the end of each tab appear between the blocks. Filter them out with QUERY:

=QUERY(VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D), "select * where Col1 is not null", 0)

Inside QUERY, columns from an array are named Col1, Col2… rather than A, B. Full guide: merging Google spreadsheets and QUERY with multiple ranges.

Combine data from other spreadsheets

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/…", "Sales!A1:D") pulls a range from another file. The first time, click Allow access in the cell. To stack several files, put IMPORTRANGE calls inside VSTACK. See using multiple IMPORTRANGE formulas.

Join two sheets by a key

QUERY can't join two tables, so matching rows needs a lookup. =XLOOKUP(A2, Customers!A:A, Customers!C:C, "") brings the matching value across; with ARRAYFORMULA or MAP you can fill a whole column at once. Walkthrough: linking two Google Sheets.

VSTACK, array literals and HSTACK in detail

VSTACK and the array literal { ; } both put ranges on top of each other and match columns by position. Three details decide which one to use:

  • Uneven widths. If one tab has an extra column, VSTACK still returns a result and fills the gaps with #N/A; wrap it in IFERROR(..., "") to blank them. An array literal stops with the error "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows".
  • Headers. Take the header row once and the data rows from every tab: =VSTACK(Jan!A1:D1, Jan!A2:D, Feb!A2:D). If every range starts at row 1, the header repeats inside the data.
  • Locale. In spreadsheets set to a locale that uses a comma as the decimal separator, array literals use a backslash between columns, so ={A1, B1} becomes ={A1\B1}. VSTACK and HSTACK use the normal argument separator, which makes formulas easier to share between teams in different countries.

HSTACK is the side-by-side version: =HSTACK(Orders!A2:C, Notes!B2:B) places the columns next to each other row by row. It doesn't match anything by key, so only use it when both ranges are in exactly the same row order. For anything else, use a lookup.

Label each row with its source tab

Once tabs are stacked you can no longer see which month or region a row came from. Add a label column to each range before stacking:

=ARRAYFORMULA(QUERY(VSTACK(
  {North!A2:C, IF(North!A2:A="", , "North")};
  {South!A2:C, IF(South!A2:A="", , "South")};
  {West!A2:C,  IF(West!A2:A="",  , "West")}
), "select * where Col1 is not null", 0))

The IF returns the tab name for every filled row and nothing for blank ones, so the label column lines up with the data. Col4 now holds the region, and you can total by it: select Col4, sum(Col3) where Col1 is not null group by Col4.

Pulling data from other files with IMPORTRANGE

  1. Copy the URL of the source spreadsheet, or just its key (the long ID between /d/ and /edit).
  2. In the destination file, enter =IMPORTRANGE("URL", "Sales!A2:D") on its own in an empty cell.
  3. Hover over the #REF! error and click Allow access. Access is granted once per pair of files, so later formulas that import from the same source work straight away.
  4. Delete the test cell and build the stack: =VSTACK(IMPORTRANGE("URL1", "Sales!A2:D"), IMPORTRANGE("URL2", "Sales!A2:D")).
  5. Wrap the stack in QUERY to drop blank rows and choose columns, exactly as with tabs.

Keep each source range as narrow as possible — Sales!A2:D rather than Sales!A:Z — because the imported cells slow recalculation and the result counts towards the destination file's cell limit. Anyone who can open the destination file can see the imported data, even without access to the source, so check who the destination is shared with. If a source file is renamed the formula keeps working, because it uses the file ID; if a tab is renamed, the range string breaks and needs editing.

Joining sheets by key: a whole column at once

A separate XLOOKUP in every row works, but thousands of copied formulas are fragile. One formula at the top of the column is easier to maintain:

=ARRAYFORMULA(IF(A2:A="", , IFERROR(VLOOKUP(A2:A, Customers!A:C, 3, FALSE), "Not found")))

This looks up every ID in column A against the Customers tab, returns its third column, writes "Not found" where there is no match and leaves blank rows blank. To bring back several columns at once, pass an array of column numbers: VLOOKUP(A2:A, Customers!A:D, {2,3,4}, FALSE).

Lookups return the first match only. When one key has several matching rows — a customer with many orders — use FILTER to list them all, =FILTER(Orders!A2:D, Orders!B2:B = A2), or TEXTJOIN to put them in one cell: =IFERROR(TEXTJOIN(", ", TRUE, FILTER(Orders!A2:A, Orders!B2:B = A2)), ""). The IFERROR handles keys with no matches, where FILTER returns #N/A. More on this pattern: TEXTJOIN for matched rows.

Worked example: orders with customer regions

An Orders tab has OrderID, CustomerID and Amount. A Customers tab has CustomerID, Name and Region. The goal is sales by region.

Orders tab
OrderIDCustomerIDAmount
1001C-01250
1002C-0390
1003C-02140
1004C-0560
Customers tab
CustomerIDNameRegion
C-01Atlas CaféNorth
C-02Bloom FloristSouth
C-03Cedar BooksNorth

In Orders!D2, =ARRAYFORMULA(IF(A2:A="", , IFERROR(VLOOKUP(B2:B, Customers!A:C, 3, FALSE), "Not found"))) fills a Region column. Then, on a summary tab:

=QUERY(Orders!A2:D, "select D, sum(C) where A is not null group by D label sum(C) 'Sales'", 0)
Sales by region
RegionSales
North340
Not found60
South140

North is 250 + 90 = 340 and South is 140, and the three groups add up to the 540 total on the Orders tab. Order 1004 belongs to C-05, which is missing from the Customers tab; the "Not found" row makes that gap visible instead of silently dropping 60 from the total. Here QUERY uses column letters, because it reads a sheet range directly rather than an array.

When formulas aren't enough

Large IMPORTRANGE stacks recalculate slowly, and a formula can't do a many-to-many join. For a scheduled merge, use Google Apps Script. To ask questions across several Google Sheets and Excel files without building formulas, connect them in Analistable: it joins them on the column you confirm and answers in plain language.

Worked example: three regional tabs

Tabs North, South and West have the same columns: Date, Product, Units. On a Summary tab:

=QUERY(VSTACK(North!A2:C, South!A2:C, West!A2:C),
  "select Col2, sum(Col3) where Col1 is not null group by Col2 label sum(Col3) 'Units'", 0)
Units by product across all regions
ProductUnits
Chair96
Desk41
Lamp27

The formula stacks the tabs, removes the empty rows the open-ended ranges add, and totals by product — and it updates when someone adds a row to any tab.

Limits to plan for

Google Sheets limits that affect combined data
LimitValueWhat it means
Cells per spreadsheet10 millionIncludes the combined tab and every source tab
Formula recalculationSlows with large importsImport only the columns you need
QUERYOne table per queryUse lookups to combine tables by key
Apps Script run time6 minutes per executionSplit very large merges into batches

When a combined sheet becomes slow, move the merge from live formulas to a scheduled script that writes static values, or analyse the files outside Sheets.

Common mistakes

  • Using A, B, C inside QUERY over a stacked array — it must be Col1, Col2, Col3.
  • Stacking tabs whose columns are in a different order: VSTACK matches by position, so reorder first.
  • Forgetting that IMPORTRANGE needs Allow access once per source file.
  • Mixing numbers stored as text with real numbers in one column: QUERY treats the minority type as empty.

Google Sheets vs Excel for combining data

Google Sheets combines tabs and files with live formulas and is easy to share. Excel adds Power Query, which can stack a whole folder of files, merge tables with any join type and refresh in one click. If your sources are a mix of both, see joining spreadsheets or export everything to one format first.

Stacking tabs whose columns are in a different order

VSTACK and array literals line columns up by position, not by header. If the February tab has Product before Date, stacking it as it is puts product names in the date column of the combined table. Reorder the range inside the formula with CHOOSECOLS, which returns the columns you list in the order you list them:

=VSTACK(Jan!A2:D, CHOOSECOLS(Feb!A2:D, 2, 1, 3, 4))

February's second column (Date) now comes first and its first column (Product) second, so both blocks match January's Date, Product, Units, Price layout. CHOOSECOLS also drops columns you don't want: CHOOSECOLS(Mar!A2:F, 1, 2, 3, 5) keeps four of six. To check the result, sort a copy of the combined tab by its first column — product names sorted among dates show at once that a block is out of order.

Removing duplicates after stacking

Exports often overlap: a March download that repeats the last week of February doubles those rows when both tabs are stacked. UNIQUE removes rows that are identical in every column, and the QUERY around the stack removes the blank rows first:

=UNIQUE(QUERY(VSTACK(Feb!A2:D, Mar!A2:D), "select * where Col1 is not null", 0))

UNIQUE compares whole rows, so two rows for the same order with a different status both survive. Decide which version should win — usually the row from the newer file — before removing anything. The step-by-step options are in merging two spreadsheets and removing duplicates.

Finding rows that are in one sheet but not the other

Combining two lists often starts with the difference between them: which IDs on the CRM tab have no row on the Billing tab? FILTER with COUNTIF answers it in one formula:

=FILTER(CRM!A2:C, CRM!A2:A<>"", COUNTIF(Billing!A2:A, CRM!A2:A)=0)

COUNTIF counts how often each CRM ID appears among the billing IDs, and FILTER keeps the rows where that count is 0. Swap the two tabs round to list billing rows with no CRM record, or change =0 to >0 to list the matches instead. COUNTIF ignores case, so "c-01" and "C-01" count as the same ID; trailing spaces do not, so clean them with TRIM first. For a wider comparison of two lists, see comparing spreadsheets.

Totals across tabs without building a combined table

If all you need is a total, you don't have to stack anything. Google Sheets has no 3D references like Excel's Jan:Dec!B2, so each tab is named in the formula, either as separate SUMIFs added together or as one SUM over a filtered stack:

=SUMIF(Jan!B2:B, "Chair", Jan!C2:C) + SUMIF(Feb!B2:B, "Chair", Feb!C2:C)

=SUM(FILTER(VSTACK(Jan!C2:C, Feb!C2:C), VSTACK(Jan!B2:B, Feb!B2:B) = "Chair"))

The second form is easier to extend — add one range to each VSTACK — and both VSTACKs must list the tabs in the same order so the criteria line up with the amounts. More patterns: SUMIF across multiple sheets in Google Sheets.

Which method should you use?

Choosing a way to combine data in Google Sheets
Your situationUse
Tabs in one file with the same columnsVSTACK inside QUERY
Same columns, different order on some tabsCHOOSECOLS inside VSTACK
Tabs in other spreadsheet filesIMPORTRANGE inside VSTACK
Add a column from a lookup tabVLOOKUP or XLOOKUP with ARRAYFORMULA
Every match for a key, not just the firstFILTER, or TEXTJOIN over FILTER
Only a total across tabsSUMIF per tab, or SUM over FILTER
Large files, or the stack has become slowApps Script that writes values
Some sources are Excel filesImport them as tabs, or combine outside Sheets

If you are working across Google Sheets and Excel at once, combining data from multiple sheets covers both, and the Sheets and Excel combiner stacks mixed files without formulas.

Troubleshooting

Common errors when combining data in Google Sheets
SymptomLikely causeFix
#REF! on an IMPORTRANGE cellAccess to the source file not yet grantedHover over the cell and click Allow access
#REF! saying the array result was not expandedCells below or to the right of the formula aren't emptyClear the area the result needs to spill into
Error about an array literal missing valuesRanges in { ; } have different numbers of columnsMake the widths match, or switch to VSTACK
#N/A cells in some columns after VSTACKOne range is narrower than the othersMatch the widths, or wrap the stack in IFERROR(..., "")
Blank rows between the stacked blocksOpen-ended ranges such as A2:D include empty rowsWrap in QUERY(..., "select * where Col1 is not null", 0)
QUERY shows empty cells in a column of numbersThe column mixes numbers and textConvert the column to one type before stacking
"Not found" for IDs that look identicalTrailing spaces, or a number on one side and text on the otherClean both keys with TRIM, and store them the same way
Formula parse error after copying a formulaLocale uses ; as the argument separatorUse the separator your spreadsheet's locale expects

Repeating the combine every month

  1. Name tabs predictably (2026-01, 2026-02 and so on). Each new month then means adding one range to the VSTACK, and the edit is easy to spot when someone reviews the formula.
  2. If you'd rather never edit the formula, keep one Data tab with a Month column and paste each new export below the last row. The summary QUERY reads the open-ended range and picks up the new rows on its own.
  3. When a month arrives as an Excel or CSV file, bring it in with File → Import, choose Insert new sheet(s), and rename the tab to match the pattern.
  4. Check the result: =COUNTA(Combined!A2:A) should equal the sum of COUNTA on each source tab's first column. A gap means a range was missed or a tab has an empty first cell on a filled row.

Every guide in this topic

Frequently asked questions

How do I combine multiple tabs into one in Google Sheets?
Use =VSTACK(Tab1!A2:D, Tab2!A2:D) or ={Tab1!A2:D; Tab2!A2:D}, and wrap it in QUERY(..., "where Col1 is not null") to remove blank rows.
How do I combine data from multiple Google Sheets files?
Use IMPORTRANGE for each file and stack the results with VSTACK. You need to allow access once per source file.
Does Google Sheets QUERY support joins?
No. QUERY works on one table at a time. Use XLOOKUP, VLOOKUP or FILTER to match rows from another sheet.
What's the difference between VSTACK and { ; } in Google Sheets?
They stack ranges the same way. VSTACK pads shorter ranges with #N/A, while an array literal returns an error if column counts differ.
How do I stack tabs whose columns are in a different order?
Reorder the out-of-order range inside the stack with CHOOSECOLS, for example =VSTACK(Jan!A2:D, CHOOSECOLS(Feb!A2:D, 2, 1, 3, 4)), so every block has the same column order.
Can Google Sheets add the same cell across tabs like Excel's 3D SUM?
No. Google Sheets doesn't support 3D references, so list each tab: =Jan!B2+Feb!B2+Mar!B2, or SUM over a VSTACK of the ranges.