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
| Goal | Function | Example |
|---|---|---|
| Stack tabs in the same file | VSTACK or { ; } | =VSTACK(Jan!A2:D, Feb!A2:D) |
| Stack and filter in one step | QUERY over an array | =QUERY({Jan!A2:D; Feb!A2:D}, "where Col1 is not null") |
| Bring in data from another file | IMPORTRANGE | =IMPORTRANGE("url", "Sales!A1:D") |
| Match rows by a key | XLOOKUP | =XLOOKUP(A2, Customers!A:A, Customers!B:B) |
| Combine text from cells | TEXTJOIN 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
- Copy the URL of the source spreadsheet, or just its key (the long ID between /d/ and /edit).
- In the destination file, enter
=IMPORTRANGE("URL", "Sales!A2:D")on its own in an empty cell. - 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.
- Delete the test cell and build the stack:
=VSTACK(IMPORTRANGE("URL1", "Sales!A2:D"), IMPORTRANGE("URL2", "Sales!A2:D")). - 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.
| OrderID | CustomerID | Amount |
|---|---|---|
| 1001 | C-01 | 250 |
| 1002 | C-03 | 90 |
| 1003 | C-02 | 140 |
| 1004 | C-05 | 60 |
| CustomerID | Name | Region |
|---|---|---|
| C-01 | Atlas Café | North |
| C-02 | Bloom Florist | South |
| C-03 | Cedar Books | North |
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)| Region | Sales |
|---|---|
| North | 340 |
| Not found | 60 |
| South | 140 |
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)| Product | Units |
|---|---|
| Chair | 96 |
| Desk | 41 |
| Lamp | 27 |
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
| Limit | Value | What it means |
|---|---|---|
| Cells per spreadsheet | 10 million | Includes the combined tab and every source tab |
| Formula recalculation | Slows with large imports | Import only the columns you need |
| QUERY | One table per query | Use lookups to combine tables by key |
| Apps Script run time | 6 minutes per execution | Split 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?
| Your situation | Use |
|---|---|
| Tabs in one file with the same columns | VSTACK inside QUERY |
| Same columns, different order on some tabs | CHOOSECOLS inside VSTACK |
| Tabs in other spreadsheet files | IMPORTRANGE inside VSTACK |
| Add a column from a lookup tab | VLOOKUP or XLOOKUP with ARRAYFORMULA |
| Every match for a key, not just the first | FILTER, or TEXTJOIN over FILTER |
| Only a total across tabs | SUMIF per tab, or SUM over FILTER |
| Large files, or the stack has become slow | Apps Script that writes values |
| Some sources are Excel files | Import 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
| Symptom | Likely cause | Fix |
|---|---|---|
| #REF! on an IMPORTRANGE cell | Access to the source file not yet granted | Hover over the cell and click Allow access |
| #REF! saying the array result was not expanded | Cells below or to the right of the formula aren't empty | Clear the area the result needs to spill into |
| Error about an array literal missing values | Ranges in { ; } have different numbers of columns | Make the widths match, or switch to VSTACK |
| #N/A cells in some columns after VSTACK | One range is narrower than the others | Match the widths, or wrap the stack in IFERROR(..., "") |
| Blank rows between the stacked blocks | Open-ended ranges such as A2:D include empty rows | Wrap in QUERY(..., "select * where Col1 is not null", 0) |
| QUERY shows empty cells in a column of numbers | The column mixes numbers and text | Convert the column to one type before stacking |
| "Not found" for IDs that look identical | Trailing spaces, or a number on one side and text on the other | Clean both keys with TRIM, and store them the same way |
| Formula parse error after copying a formula | Locale uses ; as the argument separator | Use the separator your spreadsheet's locale expects |
Repeating the combine every month
- 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.
- 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.
- 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.
- Check the result:
=COUNTA(Combined!A2:A)should equal the sum ofCOUNTAon 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
- Coefficient alternatives for Google Sheets: a fair comparison
A fair look at Coefficient's Google Sheets connectors and the alternatives: Supermetrics, Connected Sheets, Apps Script, IMPORTDATA and browser tools.
- Excel vs Google Sheets for combining data, feature by feature
Power Query against IMPORTRANGE and QUERY: how Excel and Google Sheets compare for combining data, with size limits, joins and collaboration.
- How to combine multiple IMPORTRANGE formulas
Stack data from several Google Sheets files with VSTACK and IMPORTRANGE, filter it with QUERY, and fix access errors, slow loading and size limits.
- How to link two Google Sheets
Link two Google Sheets: reference cells, pull rows with IMPORTRANGE, and match records by ID with XLOOKUP or VLOOKUP — for one cell or a whole column.
- How to match Google Forms responses to a master list
Check which people on a master list have submitted a Google Form, bring their answers next to their names, and list who still needs a reminder.
- How to merge Google Sheets into one
Merge tabs or whole Google Sheets files into one: VSTACK and array literals, QUERY to drop blanks, a source column, and IMPORTRANGE for other files.
- How to merge sheets with Google Apps Script
A copy-ready Google Apps Script that merges tabs or files into one sheet with a source column, plus how to run it on a schedule with a time-driven trigger.
- How to SUMIF across multiple sheets in Google Sheets
Sum with a condition across several tabs in Google Sheets: add SUMIFs together, use QUERY over stacked tabs, or MAP with INDIRECT for a list of sheet names.
- How to use QUERY with multiple ranges in Google Sheets
Run one QUERY over several tabs or ranges in Google Sheets: stack them in { ; }, use Col1 notation, group and sum, and avoid the common errors.
- IMPORTRANGE not working: every error and how to fix it
Fix IMPORTRANGE #REF! errors, the 'You need to connect these sheets' prompt, permission errors, wrong range strings, endless Loading and size limits.
- Why QUERY across multiple Google Sheets returns errors or misses rows
Fix QUERY errors over stacked ranges in Google Sheets: NO_COLUMN, array literal errors, header rows mixed in and rows silently dropped by mixed types.
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.