Google Sheets IMPORTRANGE With Criteria: How to Filter Data

Google Sheets’ IMPORTRANGE function does not have a built-in criteria argument; combine it with QUERY to import only rows that match a condition. For example, you can bring in orders whose status is “Open” with =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Orders!A1:D"),"select Col1, Col2, Col4 where Col3 = 'Open'",1).

How IMPORTRANGE and criteria work together

IMPORTRANGE retrieves a range from another Google Sheets file. Its basic form is =IMPORTRANGE(spreadsheet_url, range_string). The first argument identifies the source spreadsheet, and the second specifies a tab and range, such as Orders!A1:D. On its own, it imports the requested range; it does not let you specify a condition such as “only show rows where Status is Open.”

To filter the imported results, nest IMPORTRANGE inside a function that can apply criteria. QUERY is usually the most convenient choice because its query text can select columns, filter rows, and sort the output. The formula creates a live result in the destination spreadsheet; it does not delete or change rows in the source file.

In the example above, the range starts at row 1, and the query’s final argument, 1, tells Sheets that the first row contains headers. Within a query over an imported range, columns are referred to as Col1, Col2, and so on—not by their original sheet letters. Here, Col3 is the third column in the imported range, which is column C in the source range A:D.

Build a QUERY formula step by step

Assume the source tab is called Orders and has Order ID in column A, Customer in B, Status in C, and Amount in D. The first row contains headings. To show the order ID, customer, and amount only for rows with an Open status, use the formula in the first paragraph. Replace FILE_ID with the ID from the source spreadsheet’s URL. You can also use the full spreadsheet URL in the first argument.

The query text has two important parts: select Col1, Col2, Col4 chooses which imported columns to display, and where Col3 = 'Open' keeps rows where the status exactly matches Open. The third argument, 1, identifies one header row in the imported range. If you start the range at row 2, so it contains data only, use 0 instead.

To use a criterion stored in a cell, put the desired status in F1 and join that cell into the query text. With the same source layout and header row, use =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Orders!A1:D"),"select Col1, Col2, Col4 where Col3 = '"&F1&"'",1). If F1 contains Open, the resulting condition is equivalent to where Col3 = 'Open'. This approach is useful for a dropdown-driven report, but the cell value must be suitable for the query syntax.

For a numeric condition, do not put the number in quotes. For instance, to show rows where the fourth imported column is greater than 100, the query clause is where Col4 > 100. To show rows where a text column contains a word rather than exactly matching it, use a condition such as where Col2 contains 'North'. Query language has its own syntax, so it is not the same as typing a spreadsheet formula into the query string.

Choose the right range and header setting

The imported range determines both the data available to the query and how its columns are numbered. If you import A:D, column A is Col1 and D is Col4. If you import B:D, B becomes Col1, C becomes Col2, and D becomes Col3. This is a common source of confusing results: the query column number counts from the left edge of the imported range, not from the source sheet’s column A.

Include the header row when you want headings to appear in the output, then set the header argument to the number of header rows included. For a range such as Orders!A1:D with one heading row, that is 1. If you import only data beginning at row 2, set it to 0. An incorrect header count can make the first data row appear as a heading or lead to unexpected output.

Whole-column ranges are convenient when new records will be added, but they can ask Sheets to process many empty cells. If the source is large, use a realistic bounded range, such as Orders!A1:D5000, or keep the columns needed for filtering and display as narrow as possible. Expand the range when the source grows beyond the chosen limit.

Use FILTER when you want to keep every column

FILTER is another option when you want matching rows and prefer to return all columns in the imported range. With the same data layout, this formula imports columns A:D from row 2 onward and keeps rows whose column C says Open: =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Orders!A2:D"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Orders!C2:C")="Open").

The data range and condition range must cover corresponding rows and have compatible dimensions. This example starts both ranges at row 2, so the first status is checked against the first imported order. Because the formula calls IMPORTRANGE twice, it may be less efficient than using QUERY, especially for large files. Consider QUERY when you need to choose columns, sort results, or make one imported range do several jobs. Use FILTER when its row-condition structure is clearer for your task.

If no rows meet a FILTER condition, Sheets returns an error rather than a blank result. You can wrap the formula in IFERROR and supply a message such as “No matching orders,” but do so only if you also want to hide other errors. Otherwise, an error message can help reveal a broken range or permission problem.

Filter by dates, blanks, and multiple conditions

For dates, make sure the source cells contain actual date values rather than text that only looks like a date. The query language uses date literals in the form date 'yyyy-mm-dd'. For example, a query condition for dates on or after January 1, 2026, can be written as where Col1 >= date '2026-01-01', assuming the date is in the first column of the imported range. Use the column number that matches your own range.

To test for missing values, query conditions can use is null or is not null, such as where Col3 is not null. This can help exclude rows that are empty in a key field. A cell containing a formula that returns an empty string may not behave exactly like a truly empty cell, so check the source data if a blank test produces unexpected results.

Combine conditions with and or or. For example, where Col3 = 'Open' and Col4 > 100 keeps open orders with amounts above 100. Parentheses can make combined logic easier to read when conditions are more involved. If the conditions will change often, consider putting the criteria in clearly labeled cells or adding data validation dropdowns to those cells.

Connect the source spreadsheet and manage access

The first time a destination spreadsheet tries to import from a source spreadsheet, Sheets may show #REF! with an option to allow access. Open the destination file with an account that can access the source, select the cell containing the formula, and click Allow access when prompted. The source owner may need to grant that account permission to view the source file first.

Think about permissions before sharing the destination. People who can edit a destination spreadsheet may be able to use its established IMPORTRANGE connection to retrieve other parts of the connected source spreadsheet. Do not treat the destination as a secure way to reveal only selected source columns to editors. Share the source and destination with appropriate people, and avoid connecting confidential data to a broadly shared file.

When a source is imported through other spreadsheets in a chain, updates can take longer to appear, and a problem in an earlier file can affect downstream results. For a report that matters, importing directly from the original source is usually simpler to maintain.

Common errors and practical fixes

#REF! or an access prompt: Confirm you have permission to view the source, then click Allow access in the destination. Check that the URL identifies the intended spreadsheet.
Cannot find range or sheet: Check the tab name and range spelling. If a sheet name contains spaces or special characters, put it in single quotes inside the range string, for example 'Sales 2026'!A1:D.
Query returns the wrong fields: Recount columns from the left edge of the imported range. A range starting at B makes source column B equal to Col1.
No rows appear: Check spelling, extra spaces, data types, and whether your condition is an exact match. Confirm that the selected column actually contains the expected values.
Formula parse error: Check quotation marks and parentheses in both the outer formula and the query text. Spreadsheet locale settings can affect whether argument separators are commas or semicolons.
Loading or performance problems: Reduce oversized ranges, avoid unnecessary duplicate imports, and limit chains of dependent IMPORTRANGE formulas. Google Sheets documents a 10 MB received-data limit per IMPORTRANGE request.

How this compares with Excel

IMPORTRANGE is a Google Sheets function, not an Excel function. The formulas in this guide are for Google Sheets in a web browser; Excel does not support the same IMPORTRANGE-and-QUERY setup. In Excel for Microsoft 365, importing and filtering data from another workbook typically involves different tools, such as Power Query or formulas that refer to workbook data. Those approaches have different connection, refresh, and sharing behavior, so do not paste a Sheets formula into Excel expecting it to work.

Within Sheets, QUERY is often a better fit than importing an entire source tab and manually hiding unwanted rows: the criteria are part of the result formula, and the source remains unchanged. However, it is not a permissions feature or a permanent snapshot. It creates a linked result that can update as the source changes and as Sheets recalculates it.

Frequently Asked Questions

Can IMPORTRANGE filter rows by itself?
No. IMPORTRANGE selects a source spreadsheet and range, but it has no criteria argument. Nest it inside QUERY or FILTER to return only rows meeting your condition.

Can I use a cell as the criterion?
Yes. Join a cell reference into the query text, as in the F1 example above. For text criteria, the query needs single quotes around the value. Be careful if the cell can contain apostrophes or other characters that affect query syntax.

Why does QUERY use Col1 instead of A?
QUERY numbers columns relative to the range it receives. If the imported range is A:D, source column C is Col3. If the range starts at C, source column C is Col1.

Will imported results update when the source changes?
IMPORTRANGE creates a connection rather than a one-time copy, and Sheets can refresh the result when source data changes. Updates may not be instantaneous, particularly when there are many dependent imports or large ranges.

Can I import from a spreadsheet I cannot open?
No. The account authorizing the connection needs access to the source. Ask the source owner to grant suitable permission; having the URL alone does not grant access.

Does filtering the imported data hide it from destination editors?
No. A query can limit what its formula displays, but it should not be treated as a security boundary. Review access to both spreadsheets and avoid connecting a sensitive source to a destination whose editors should not be able to retrieve its data.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.