Having your data spread out across different spreadsheets is a common challenge. You might have sales figures in one file, inventory data in another, and customer information in a third sheet. When you need to pull that information together to build a dashboard or a summary report, the default solution is often manual copying and pasting.
What if your Google Sheets could talk to each other, automatically updating your reports whenever the source data changes?
In this guide, we’ll use the IMPORTRANGE and QUERY functions to import data between Google Sheets, and then filter, sort and summarize it.
For our examples, we’ll use a source Google Spreadsheet with a sheet named Sales. This sheet contains the order ID, customer name, region, sale date, sale amount and status of each sale.

Import Data with IMPORTRANGE
The IMPORTRANGE function in Google Sheets allows you to bring in data from an entirely different spreadsheet, maintaining a live connection between the source and destination. This live link keeps your reports and dashboards up to date without needing to manually copy data.
The only requirement for IMPORTRANGE is that the source spreadsheet must be accessible to the person importing the data. If you’re importing from a sheet you don’t own, ensure the owner has granted you editor or viewer access.
To use the IMPORTRANGE function, you need to provide the URL of the source spreadsheet and the range of cells you want to import. For example, if you want to import the data from the Sales sheet, the range would be something like Sales!A1:F50. If the sheet name contains spaces, you need to wrap it in single quotes like 'Q3 Sales'!A1:F50.
The syntax for the IMPORTRANGE function is simple. Both values must be enclosed in double quotes:
=IMPORTRANGE("spreadsheet_url", "range_string")

The first time you add this function to your spreadsheet cell, it will return a #REF! error. This simply means Google Sheets needs your permission to access the source spreadsheet. Hover your mouse over the error and click on Allow access to grant access. This authorization is remembered, but you’ll be asked again each time the spreadsheet imports from a new source.

Check who can edit your sheet. Once access is granted, any editor of the destination spreadsheet can use
IMPORTRANGEto pull data from any part of the source spreadsheet, not just the range you imported.
Going back to our previous example, if we wish to import the header row and the first 12 rows of data from the Sales sheet into a new sheet, our formula would look like this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1234567890/edit", "Sales!A1:F13")
Import New Rows Automatically
A fixed range like Sales!A1:F13 stops at row 13, so any rows you add to the source sheet later will not be imported. If your data grows over time, leave out the last row number to import the entire columns:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1234567890/edit", "Sales!A:F")
The examples in this guide use the range A1:F13 to match our sample data.
How Often Does IMPORTRANGE Update?
The destination spreadsheet refreshes when the source spreadsheet changes, and Google Sheets also checks for updates every hour while the document is open. There can be a short delay, particularly if you chain imports through several spreadsheets.
Combine Multiple Ranges with IMPORTRANGE
The power of IMPORTRANGE isn’t limited to pulling a single range. You can combine data from multiple ranges - even from different sheets or spreadsheets - into one continuous list using array literals {}.
The array syntax {} tells Google Sheets to build a custom array, or list, of data based on your instructions. There are two primary ways to arrange your combined data:
Vertical Stacking (Place data one on top of the other)
This is the most common way to combine data. By separating your IMPORTRANGE formulas with a semicolon ;, you can merge data from different sheets (or even different spreadsheets) into a single list where the data is placed one on top of the other.

For example, if you have sales data split between “Q3-Sales” and “Q4-Sales” sheets in your source spreadsheet, you can combine them vertically using the formula below. Replace URL with the URL of your source spreadsheet.
={IMPORTRANGE("URL","Q3-Sales!A1:F13"); IMPORTRANGE("URL","Q4-Sales!A2:F13")}
This formula first pulls the specified range from “Q3-Sales.” The semicolon then tells it to append the data from “Q4-Sales” directly below the Q3 data.
🔥 The number of columns selected in each IMPORTRANGE formula must be the same, otherwise Google Sheets will return an error saying In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

💡 The first range starts at cell A1 to include the column headers, while the second starts at A2. This avoids importing the headers twice, giving you a clean, continuous list of data.
Horizontal Stacking (Place data side-by-side)
This method is useful when you have data that is spread across multiple columns in different sheets. It allows you to combine data from different sheets into a wider table with more columns.
To place ranges next to each other horizontally, you use the same array syntax but separate the formulas with a comma inside the curly braces {}. Here you can have a different number of columns in each range, but each range of the IMPORTRANGE formula must have the same number of rows.
Let’s say you have the customer names in the Customer sheet and their address details in an Address sheet.
={IMPORTRANGE("URL","Customer!A2:B6"), IMPORTRANGE("URL","Address!A2:C6")}
The above formula pulls customer names from the first range and places the address details from the second range in the columns immediately to the right.
Conditional Imports with the QUERY function
Importing an entire dataset is often unnecessary. You typically only need specific rows – like inventory from a certain warehouse, or sales for a specific period.
By wrapping your IMPORTRANGE in a QUERY, you can import only the rows that meet specific conditions. The syntax for the QUERY function is as follows:
=QUERY(IMPORTRANGE("spreadsheet_url", "range"), "Your Query Statement", 1)
The spreadsheet_url is the URL of the spreadsheet from where data will be imported. The range tells Google Sheets exactly which sheet and which cells you want to import. The Query Statement is your filter (e.g., WHERE Col6 = 'Completed').
The optional last argument, 1, tells QUERY that your data has one header row. If you omit it, Google Sheets guesses the number of header rows. We’ve left it out of the examples below to keep them short, but it’s a good idea to include it in your own formulas.

Here are some practical examples using our Sales sheet.
Get All Data for a Specific Region
The IMPORTRANGE function will import every row, but we only want to import the data for the North region.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col3 = 'North'")
SELECT *means that we want to import all columns from the source sheet.WHERE Col3 = 'North'is the filter. It tells the query to only return rows where the third column (Col3, which is Region) is equal toNorth. Note that the text values are case-sensitive and must be in single quotes.
The QUERY function does not see the original spreadsheet but the final data array that is returned by the IMPORTRANGE function. Therefore, it refers to the columns of the resulting array by their order: Col1 is the first column of your imported range, Col2 is the second, and so on.
Combine Multiple Conditions (AND)
You need a report of all “Completed” sales in the “South” region. The AND keyword lets you chain multiple conditions. The query will only return rows that satisfy both the conditions:
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col3 = 'South' AND Col6 = 'Completed'")
Filter by a List of Possible Values (OR)
You want to see all sales data from either the “East” or “West” regions combined in one list. The OR keyword returns rows that match either condition, allowing you to check for multiple possible values in the same column.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col3 = 'East' OR Col3 = 'West'")
Filter by a Numerical Value
You need to generate a list of all high-value sales where the sale amount is over $1,000. You can use standard comparison operators >, <, >=, <=, = for numbers. Notice that numbers and currency values are not placed in quotes.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col5 > 1000")
Select Specific Columns with Sorting
You want a clean report showing just the Customer Name and Sale Amount for high-value sales, with the largest sales appearing first.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT Col2, Col5 WHERE Col5 > 1000 ORDER BY Col5 DESC")
SELECT Col2, Col5specifies that we only want to import the “Customer Name” and “Sale Amount” columns.ORDER BY Col5 DESCsorts the results based on the “Sale Amount” column (Col5) in descending order. UseASCfor ascending order.
Filter by a Date Range
You need to see all sales that occurred in the fourth quarter of 2024 (from October 1st to December 31st). When working with dates in QUERY, you must use the date keyword followed by the date in ‘YYYY-MM-DD’ format, enclosed in single quotes.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col4 >= date '2024-10-01' AND Col4 <= date '2024-12-31'")
Find Partial Text Matches
You want to find all sales made to any company with “Inc” in its name. The contains operator is perfect for finding a substring within a text field.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col2 contains 'Inc'")
You can also use the
starts withandends withoperators for plain text matches, or thematchesoperator to filter text values with regular expressions.
Limiting the Number of Results
You want to create a Top 5 leaderboard showing the largest sales. The LIMIT clause allows you to restrict the number of rows returned.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * ORDER BY Col5 DESC LIMIT 5")
Aggregating Data to Create Summaries

Instead of a long list of sales, you want a summary table showing the total sales value for each region. The GROUP BY clause aggregates rows and lets you perform calculations on them with functions like SUM(), COUNT(), and AVG().
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT Col3, SUM(Col5), AVG(Col5) GROUP BY Col3")
The GROUP BY Col3 clause scans the Region column and finds all the unique values (North, South, East, West). It then creates a single output row for each region. The aggregate functions SUM(Col5) and AVG(Col5) then calculate the total and average sales amount for all rows that belong to that region.
Other useful aggregate functions include
COUNT(),MAX(), andMIN().
Rename Column Titles in QUERY output
Google Sheets automatically names the columns in the output of a QUERY function based on the formula used. For example, if you use SELECT Col3, SUM(Col5) GROUP BY Col3, the output will have column titles like sum Sale Amount that are not very descriptive. The LABEL clause lets you rename these output headers for better readability.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT Col3, SUM(Col5) GROUP BY Col3 LABEL SUM(Col5) 'Total Sales', Col3 'Sales Region'")
The syntax is LABEL column_to_rename new_column_name. You can chain multiple labels with a comma. This only changes the header in your output, it does not affect the original source data in any way.
Formatting Date and Numbers
You can control how numbers and dates are displayed directly within your query output using the FORMAT clause. This is great for applying currency symbols or standardizing date formats without manually formatting the entire column.
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT Col3, SUM(Col5) GROUP BY Col3 LABEL SUM(Col5) 'Total Sales' FORMAT SUM(Col5) '$#,##0.00'")
The pattern '$#,##0.00' tells Sheets to display the number with a dollar sign, a comma for the thousands separator, and two decimal places. This is purely visual, the underlying value is still a number. You could format a date with a pattern like mmm d, yyyy to get Jul 15, 2025.
Summarize Data with PIVOT
You can use the PIVOT clause in your QUERY function to transform your data from a list into a wide, cross-tabulated summary, similar to a traditional Pivot Table. This is particularly useful when you want to see how values in one column (like sales amounts) are distributed across categories from another column (like regions).
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT Col3, SUM(Col5) GROUP BY Col3 PIVOT Col6")

This query does three things:
GROUP BY Col3creates a unique row for each region.SUM(Col5)decides what value will be calculated for the cells.PIVOT Col6takes the unique values from the “Status” column (Completed, Shipped, Pending) and turns them into their own columns. The result is a table showing the total sales value for each region, broken down by status.
Using a Cell Reference as a Condition
You can make your queries dynamic and interactive by building the query string to reference another cell. This is the foundation for creating dashboards that react to user input.
To filter by text from a cell (e.g., cell G1 contains the region name), use this formula:
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col3 = '"&G1&"'")
Notice how we use the ampersand & operator to concatenate the value from cell G1 into the query string. Text values need to be enclosed in single quotes within the QUERY string.
If you were filtering by a number from cell G1, the syntax would be simpler:
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col5 > " & G1)
If cell G1 contains a date, use the TEXT function to convert it to the yyyy-mm-dd format that QUERY expects:
=QUERY(IMPORTRANGE("URL", "Sales!A1:F13"), "SELECT * WHERE Col4 >= date '"&TEXT(G1,"yyyy-mm-dd")&"'")
If your dashboard has several formulas like these, point them to a single raw data tab instead of repeating the IMPORTRANGE function in each one.
Use QUERY with Multiple Ranges
While the QUERY function only takes a single data range as its input, you can use the curly braces {} syntax to create a single combined range from multiple sheets and then apply the QUERY function to filter that combined data. Here is an example:
=QUERY(
{
IMPORTRANGE("URL", "Q3-Sales!A1:F13");
IMPORTRANGE("URL", "Q4-Sales!A2:F13")
},
"SELECT * WHERE Col3 = 'North'",
1
)
Here’s how this formula works step by step:
- Google Sheets evaluates the expressions inside the curly braces first. This means it executes both IMPORTRANGE functions and combines their results into a single unified table.
- The semicolon between the IMPORTRANGE functions tells Sheets to stack the results vertically - the second range will appear directly below the first range.
- Once the data is combined into a single vertical table, the QUERY function processes this merged dataset as if it were a single table.
Important: When stacking ranges this way, each IMPORTRANGE must return the same number of columns.
Open-ended ranges like A2:F also bring in the empty rows at the bottom of each sheet, which leaves a gap between the two datasets. To stack sheets that keep growing, use a WHERE clause to remove the blank rows:
=QUERY(
{
IMPORTRANGE("URL", "Q3-Sales!A1:F");
IMPORTRANGE("URL", "Q4-Sales!A2:F")
},
"SELECT * WHERE Col1 IS NOT NULL",
1
)
Import Data from the Same Spreadsheet
While IMPORTRANGE is essential for pulling data from separate files, what if your data resides in a different tab within the same spreadsheet? For this scenario, you don’t need the IMPORTRANGE function but directly reference the cell in the source sheet.
To reference a single cell from another sheet, you simply type =, followed by the sheet name, an exclamation mark, and the cell you want to pull the data from.
=Sales!E14
To reference a range of cells from another tab, you use the same syntax as above but wrap it in curly braces {}. This instructs Google Sheets to output the entire array of data, not just a single value.
={Sales!A1:D10}
A common pattern is to combine local cell referencing with the QUERY function. This allows you to keep your raw data in one tab while creating multiple, filtered reports in other tabs – all within a single, fast loading spreadsheet.
When you use QUERY on a range from within the spreadsheet, you can directly use the actual column letters (A,B,C) in your QUERY statement, instead of using the column numbers (Col1, Col2, Col3). This is because QUERY is referencing the local sheet directly and can see its structure.
=QUERY(Sales!A1:F13, "SELECT A, B, E WHERE C = 'North' ORDER BY E DESC", 1)
The above QUERY function pulls the data from the Sales sheet and filters it to only include the rows where the Region column is equal to North. It then sorts the results by the Sale Amount column in descending order.

Improve Performance with a Raw Data Tab
You can use multiple IMPORTRANGE formulas in one Google Sheet, but each formula creates a connection to an external file. If you have dozens of these functions in a single sheet, it can slow down your calculations.
Google Sheets also downloads the entire imported range and caps it at 10 MB of data per request, so import only the columns you need from very large sheets.
For optimal performance, create a dedicated tab named Raw Data in your report and use one IMPORTRANGE formula to bring in the dataset:
=IMPORTRANGE("URL", "Sales!A:F")
Then, use multiple QUERY or FILTER formulas in your dashboard that reference this local tab:
=QUERY('Raw Data'!A:F, "SELECT C, SUM(E) WHERE C IS NOT NULL GROUP BY C", 1)
=FILTER('Raw Data'!A2:F, 'Raw Data'!C2:C = "North")
This way you only create one external connection, and all subsequent filtering is done inside your sheet, which is significantly faster.
Troubleshooting Common Errors
#REF!- You need to connect these spreadsheets: Hover over the error and click Allow access.#REF!- You don’t have permissions to access that sheet: Ask the owner of the source spreadsheet to give you access.#REF!- Array result was not expanded: The formula needs empty cells to display its output. Clear any data in the cells below or to the right of the formula.In ARRAY_LITERAL, an Array Literal was missing values: The ranges inside the curly braces don’t have matching sizes. Stacked ranges need the same number of columns, and side-by-side ranges need the same number of rows.- The QUERY returns no results: Check that the
Colnumber points to the right column, and that the text matches exactly because QUERY is case-sensitive. - Some rows or values are missing: If a column has mixed data types, QUERY uses the majority type and treats the other values as null. For example, a text value like
N/Ain the Sale Amount column will be ignored. - The formula is stuck on Loading: You may be importing too much data at once. Reduce the size of the range, or use a raw data tab.
Importing Data - Which Method to Use?
Whether your data lives in another spreadsheet or in another tab of the same sheet, Google Sheets can import it automatically and keep your reports and dashboards up to date.
| What you want to do | What to use |
|---|---|
| Import data from a different spreadsheet | IMPORTRANGE |
| Import only some rows, or sort and summarize them | QUERY wrapped around IMPORTRANGE |
| Combine multiple ranges into a single table | Curly braces {} around IMPORTRANGE functions |
| Pull data from another tab of the same spreadsheet | A direct reference like Sales!A1:F13 |
Also see: