To import and stack multiple tabs from an external Google Sheet, wrap multiple IMPORTRANGE functions inside an array {} separated by semicolons (to stack vertically). Nest this entirely inside a QUERY function to strip out the blank rows.
Excel
=QUERY({IMPORTRANGE("Sheet_URL", "Tab1!A2:E"); IMPORTRANGE("Sheet_URL", "Tab2!A2:E")}, "SELECT * WHERE Col1 IS NOT NULL")The Syntax Breakdown
{ ... }(Array Literals): The curly brackets convert disparate data sources into a single array.;(Semicolon): The vertical stacking operator inside the array. It forces the secondIMPORTRANGEto append directly below the first one. (Note: using a comma,would place them side-by-side).IMPORTRANGE("Sheet_URL", "Tab1!A2:E"): Pulls the data from the source sheet. We start at row 2 (A2:E) to intentionally exclude the header rows from the raw data. You only need to type your headers once on the master sheet.QUERY(...): Wraps the combined array to filter the resulting dataset."SELECT * WHERE Col1 IS NOT NULL": Because open-ended ranges (A2:E) pull in hundreds of empty rows at the bottom of the first tab, this SQL-style statement forces the formula to ignore any row where Column 1 is blank, ensuring your stacked data is flush.
Real-World Example: Consolidating Regional Q3 Sales Data
You manage a national sales team where the East Coast and West Coast teams log their Q3 deals in separate tabs of a shared regional workbook. You need a master dashboard that dynamically stacks both tabs together as new deals are logged.
Here is the raw data in the source workbook:
Tab Name: “East_Region”
| Column A (Rep) | Column B (Client) | Column C (Deal Size) |
| Jenkins | TechCorp | $15,000 |
| Torres | AlphaLogix | $8,500 |
Tab Name: “West_Region”
| Column A (Rep) | Column B (Client) | Column C (Deal Size) |
| Kim | InnoSystems | $22,000 |
| Patel | GlobalData | $14,000 |
The Application:
In your new Master Dashboard sheet, type your headers in Row 1. Then, in cell A2, paste the formula:
Excel
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/1A2B3C...", "East_Region!A2:C"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/1A2B3C...", "West_Region!A2:C")}, "SELECT * WHERE Col1 IS NOT NULL")The Output:
The formula dynamically pulls both tables, stacking them vertically while ignoring the thousands of blank rows beneath the active data in the East tab.
| Rep | Client | Deal Size |
| Jenkins | TechCorp | $15,000 |
| Torres | AlphaLogix | $8,500 |
| Kim | InnoSystems | $22,000 |
| Patel | GlobalData | $14,000 |
Common Errors & How to Fix Them
#REF!Error (You need to connect these sheets): You cannot grant permissions to multipleIMPORTRANGEformulas simultaneously when they are nested inside an array. Fix: Take oneIMPORTRANGEsnippet out of the formula, paste it into an empty cell by itself, hit enter, and click the blue Allow Access button. Repeat for the second tab, delete the standalone formulas, and your combined array will now work.#VALUE!Error (Array arguments are of different size): Your imports do not have the same number of columns. You cannot vertically stack a 5-column range on top of a 4-column range. Fix: Ensure the range references exactly match in width (e.g.,A2:EandA2:E, notA2:EandA2:D).- Massive blank gaps between the datasets: You used the array brackets
{}but forgot to wrap the formula in aQUERY. Fix: Add theQUERY(..., "SELECT * WHERE Col1 IS NOT NULL")wrapper to automatically filter out the empty rows inherited from the source sheets.
