Google Sheets IMPORTRANGE: The Complete Guide

Ever played the "just one quick copy-paste" game with Google Sheets... and lost an hour of your life? What starts as a simple data transfer quickly snowballs into a nightmare when working with dynamic information. Those "quick fixes" become endless cycles of manual updates across multiple sheets. This is exactly where the IMPORTRANGE function will help you streamline your workflow.

The main function of IMPORTRANGE is to pull live data from multiple spreadsheets into one auto-updating table. However, there is a lot of power hidden in this formula. You can use it not only to merge sheets but also to filter data across spreadsheets, split a large Google sheet into smaller ones, and much more. If you want to learn how to optimize your work with spreadsheets and save hours with ready-to-use formulas, this guide is a must-read for you!

Syntax of the Google Sheets IMPORTRANGE function

In Google Sheets, the IMPORTRANGE function easily grabs data from one sheet and puts it right into another.

Keep in mind that this function is not simply copying and pasting. Any changes in the source spreadsheet are automatically reflected in your imported data. It's incredibly useful as you don't have to constantly switch between files or remember to refresh data.

The syntax of the IMPORTRANGE function is straightforward since each argument speaks for itself:

=IMPORTRANGE("spreadsheet_url", "range_string")

spreadsheet_url is the URL of the source Google Sheet.
range_string is the sheet name and cell range.

Note. The very first time you use IMPORTRANGE to connect two sheets, a #REF! error will appear. You must click Allow access to grant permission for the two sheets to communicate.

Using IMPORTRANGE in Google Sheets: key use cases

Let’s break down the most common ways to work with IMPORTRANGE — you know, those times when it really saves your bacon. I'll walk you through real examples to show how it really works. To keep things neat, the tables will be small to make it easy to follow. 👌

Example 1. How to import range from another Google Sheet

Consider this example: you are a sales manager preparing an annual report. Your raw sales data for the entire year of 2024 is stored in one master Google Sheet, but you want to pull this data into a separate sheet to create a summary without risking the original data.

To import all the sales data, follow these steps:

Open the source spreadsheet (in my example: "Sales data 2024").
Go to the address bar and copy the full URL. It will look something like this:
https://docs.google.com/spreadsheets/d/1Qr6Sp1oyFtKpVl7tjTvO3L01Eb7uMqH3F3bETNg5buc/edit?gid=196681429#gid=196681429

Note. The key identifier is the spreadsheet ID between /d/ and /edit: 1Qr6Sp1oyFtKpVl7tjTvO3L01Eb7uMqH3F3bETNg5buc. You can shorten your formula by using that ID string, or the minimal URL that ends after /edit: https://docs.google.com/spreadsheets/d/1Qr6Sp1oyFtKpVl7tjTvO3L01Eb7uMqH3F3bETNg5buc/edit.
Open the destination spreadsheet (in my example: "Sales Report").
Choose a place to import the result, then type a formula like this:
=IMPORTRANGE("1Qr6Sp1oyFtKpVl7tjTvO3L01Eb7uMqH3F3bETNg5buc","Sales_2024!A1:G14")

Let's look at each component in detail:
- The start of the function: =IMPORTRANGE.
- The ID of the source spreadsheet (found between /d/ and /edit in the URL):
  "1Qr6Sp1oyFtKpVl7tjTvO3L01Eb7uMqH3F3bETNg5buc".
- A comma to separate the two key pieces of info: ,.
- The range string, "Sales_2024!A1:G14", which consists of three key pieces of information:
  - Sales_2024 is the name of the specific tab containing the data.
  - ! is a separator.
  - A1:G14 is the exact range of cells we want to import.
    
    Note. Make sure you use standard straight quotes (") not curved ones (”). Spreadsheet formulas can only understand straight quotes.
Press Enter to confirm.
On your first connection to a spreadsheet, the formula will likely return a #REF! error for a moment. Don't panic, as mentioned above, it's just IMPORTRANGE asking for access. Hover over the cell, and click the Allow access button.
After granting access, your data will show up automatically.

Example 2. How to import multiple ranges in Google Sheets

Merging data from several spreadsheets by hand is quite difficult. It’s just endless copying, pasting, and hoping nothing gets mixed up. Luckily, there are other options. In this chapter, I’ll describe two effective methods for combining data, so you can choose the best method for your needs.

Vertical stacking into a single array

The quickest way to combine datasets from different spreadsheets is to create a single array using curly braces {}. They combine data from multiple sources into one unified virtual table. This works best when all your datasets have matching columns.

The syntax for the array is straightforward:

={IMPORTRANGE("spreadsheet_url_1","range_string_1"); IMPORTRANGE("spreadsheet_url_2","range_string_2")}

Note. The entire operation must happen within a single cell and a single array formula. You cannot place one IMPORTRANGE formula in a cell and then a second one in a cell below it. This will not work, as Google Sheets will return a #REF! error in this case.

For example, you have sales data in two separate spreadsheets: one for 2023 and one for 2024. You need to combine them into one master sheet, so you don't have to keep flipping back and forth analyzing sales. Just add your specific file links and ranges like this:

={IMPORTRANGE("https://docs.google.com/spreadsheets/d/12_xJqvZXOrFa3p_6IBvBJ1Xa1DoMePvR6EHXHYkX9i/edit", "Sales_2023!A1:E6");IMPORTRANGE("https://docs.google.com/spreadsheets/d/15CJiyzbw-E9YpgLOEbWUWPttBJmrnI8lVIQHxz1WSv/edit","Sales_2024!A2:E6")}
Stack spreadsheets vertically using curly braces

Let's take a closer look:

={...} are the curly braces that act like a container.
IMPORTRANGE("https://docs.google.com/spreadsheets/d/12_xJqvZXOrFa3p_6IBvBJ1Xa1DoMePvR6EHXHYkX9i/edit","Sales_2023!A1:E6") connects to your first spreadsheet and imports the sales data from the "Sales_2023" sheet in the range of "A1:E6".
; is a vertical stacking operator, which tells Google Sheets to place the next dataset underneath.
IMPORTRANGE("https://docs.google.com/spreadsheets/d/15CJiyzbw-E9YpgLOEbWUWPttBJmrnI8lVIQHxz1WSv/edit",
"Sales_2024!A2:E6") is the second function that imports the data from the "Sales_2024" sheet in the range of "A2:E6".

All your imported data will combine into one master sheet.

Importing data with Combine Sheets

Formulas driving you nuts? The Ablebits suite of tools for Google Sheets is designed to replace them with easy-to-use applications. The add-ons act as an intuitive interface that builds complex formulas for you.

Combine Sheets is the perfect alternative to manually writing array formulas. The add-on itself generates a single, clean formula on your behalf.

It's easy to use, just follow these steps:

Select the sheets you want to combine. You can pick them straight from your Google Drive.
Choose the columns to combine.
Specify how to copy the data. Check the Use a formula to combine sheets box, and the tool will automatically generate the formula for you, keeping your data synced with the source.
Pick where to paste results. After that, you’ll need to click Allow access buttons for the merging sheets, as the add-on uses an IMPORTRANGE formula inside. With just a few clicks, you’ll get perfectly merged sheets!

Example 3. How to import filtered range Google Sheets

The standard filter feature in Google Sheets modifies the view for everyone using the sheet. But what if you need to create a personal, live, filtered view of that master sheet without disrupting anyone else's work?

IMPORTRANGE with FILTER in Google Sheets

Instead of filtering the original sheet, you can pull the data into a new sheet using a powerful combination of the IMPORTRANGE and FILTER functions.

You'll need this formula pattern:

=FILTER(IMPORTRANGE("spreadsheet_url","data_range"), IMPORTRANGE("spreadsheet_url","condition_range")="Your condition")

So, consider this scenario: a marketing manager needs a list of the team members and their start dates from the HR department to plan for anniversaries, but he must not see sensitive data like salaries from the Salary column. What should an HR manager do?
Employee data with a salary column

He can create a new, separate Google Sheet for the marketing manager. In A1, he uses a formula to import the data while carefully omitting the salary column:

=FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WGSPGamMzmwN8U9kY0XFKdak8J-sPx1ORHQvqEBTi6E/edit", "Employee Data!A2:D13"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WGSPGamMzmwN8U9kY0XFKdak8J-sPx1ORHQvqEBTi6E/edit", "Employee Data!C2:C13")="Marketing")
The filter function in action

Let's break down this formula:

=FILTER starts our filtering function, which will display only rows that meet our criteria.
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WGSPGamMzmwN8U9kY0XFKdak8J-sPx1ORHQvqEBTi6E/edit", "Employee Data!A2:D13") pulls the range A2:D13 from the "Employee Data" sheet.
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WGSPGamMzmwN8U9kY0XFKdak8J-sPx1ORHQvqEBTi6E/edit", "Employee Data!C2:C13")="Marketing" checks C2:C13 and only keeps rows where the department equals "Marketing".

This formula displays only the rows where the department is "Marketing" and only shows the columns A through D. The Marketing manager now has a live, auto-updating list of his team members without information about their salaries.

Formula-free way to filter Google Sheets

While the FILTER function is powerful, it can quickly become overwhelming. Luckily, we have an ultimate solution: the Filter and Extract Data add-on is designed for exactly this type of task. The best part? No formulas required.

Just see how easily we can complete the example:

Select the table you want to filter.
Choose which columns to include in your results. For our example, just uncheck the Salary box.
Set the conditions. To do so, just select the option from the dropdown instead of typing it into a formula.
Choose a place for the result.
Click Insert formula or Paste result.

I highly recommend trying the Filter and Extract Data add-on for Google Sheets. It can significantly simplify how you work with your data, as it allows you to build complex filters through a simple visual menu, completely removing the need to write and debug complicated formulas.

Example 4. How to import range if condition is met

Let's consider you have a large dataset in another spreadsheet and you only want to pull in the rows that meet certain conditions. For this, the most efficient method is to combine the QUERY function with IMPORTRANGE.

The basic formula for this case will look like:

=QUERY(IMPORTRANGE("spreadsheet_url", "range_string"), "SELECT ... WHERE ...")

Imagine you have a main sheet "Finance" where daily or weekly financial transactions are logged. You want to import into a new spreadsheet only those rows where the "Status" column (Column 6) says "Paid".
Filter paid transactions

The perfect formula for this:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19mA0-2Cph5BJ38NjgVeTmA3Wdbnhj2aacSol9nBJsHs/edit","Finance!A2:G10"), "SELECT * WHERE Col6 = 'Paid'")
Import only paid transactions

A step-by-step explanation is as follows:

QUERY(...) allows you to filter the data.
IMPORTRANGE("https://docs.google.com/spreadsheets/d/19mA0-2Cph5BJ38NjgVeTmA3Wdbnhj2aacSol9nBJsHs/edit","Finance!A2:G10") connects to your source sheet "Finance" and grabs the data in the range from cell A2 to G10.
SELECT * tells the function to return every column from the data that was imported.
WHERE sets the condition for which rows to show.
Col6 = 'Paid' is the condition itself. It instructs the formula to look at the 6th column of the imported data and only show the rows where the value in that column is 'Paid'.

Note. When using QUERY with IMPORTRANGE, you must refer to columns by number (Col1, Col2, Col3), not by letter (A, B, C).

Now the most important thing is that when you add new transactions to your original file, the other spreadsheet with imported data will be updated automatically without your participation.

Stuck on the formula? The Filter and Extract Data add-on can easily bail you out here too! Just set up your condition with a few clicks and select a place for the result. The tool will pull the clean, filtered data into a new sheet for you, creating the same live, auto-updating list without the headache.
One-click filtered data

Google Sheets IMPORTRANGE function not working: solutions

#REF! error

This is the error you'll see 90% of the time using the IMPORTRANGE function. It's a general error. This one may hint at several things:

IMPORTRANGE doesn’t have access to the file yet. You are trying to grab data from a spreadsheet for the first time, and Google needs your permission to link.
Solution: Hover over the cell with the #REF!, an Allow access button will appear. Click this button.
Incorrect URL or sheet/range name. The formula can't find the file or the specific tab/cells you told it to look for.
Solution: Go to the source spreadsheet and check its URL directly from your browser's address bar. You could have lost a couple of characters when you shortened the formula. Also double-check that the sheet name in the formula exactly matches the name of the tab in the source sheet and the range you've specified actually exists on that sheet.
You don't have permission to the source sheet. It may occur that the Google account you are currently logged in with does not have at least "View" access to the source spreadsheet.
Solution: Go to the source spreadsheet you are trying to import from and click the Share button in the top-right corner. Ensure that your email address is listed with at least "Viewer" permissions. If it's not, ask the owner of that sheet to share it with you.
Overwriting data. IMPORTRANGE needs enough empty cells below and to the right of it to display all the imported data. The error tells you that there is already something in one of those cells.
Solution: Look at the cell address mentioned in the error message. Go to that cell and delete its contents. The formula should then work correctly.

#ERROR! error

There is a syntax mistake in your formula. Google Sheets cannot understand the instructions you've written.

Solution: The most common cause is the missing quotation marks. Double-check both the spreadsheet_url and the range_string. They must be enclosed in double straight quotation marks ("). Also, make sure there is a comma separating the URL from the range string.

#VALUE! error

It often means the error isn't with the import itself, but with a function you've wrapped around it (like QUERY, FILTER, or other). But sometimes an access problem may show this error.

Solution: Separately copy each IMPORTRANGE(...) part of your formula and paste it into a new, empty cell. Does it work on its own? One of these cells will likely show a #REF! error. Hover over that cell, and a button will appear asking you to Allow access. You must grant permission for each unique spreadsheet you are linking to.
Fixing the error #VALUE!: ARRAY_LITERAL

If it returns the data correctly, you know the problem is 100% in your outer function. Check the wrapper. For example, the QUERY function has very strict rules: a small syntax error may cause the whole formula to fail.

#N/A error

This error means that no data matches your filter conditions.

Solution: The first step is to confirm the data is arriving correctly. As with the #VALUE! error copy just the IMPORTRANGE(...) part of your formula and check if it returns #REF! error (the problem with access to the spreadsheet may exist here too). If it's a #REF!, use the solutions earlier in this article to fix the connection first.

If the import works, the problem is with your FILTER logic. Look closely at the condition you are filtering by. Is there a typo? Does any of the data actually meet the criteria you've set?

To make your filtered data more presentable when no results are found, wrap your formula with IFNA. In this case, the standard #N/A error will be replaced by a custom message. For example:

=IFNA(FILTER(IMPORTRANGE("spreadsheet_url","data_range"), IMPORTRANGE("spreadsheet_url","condition_range")="Your condition"),"No matching records")

Shows No matching records when empty

The formula works exactly as before when results exist, but now displays "No matching records" instead of an error when the filter comes up empty. You can replace this text with any message you prefer, or use empty quotes "" to leave cells blank.

"Loading..." issue

If the "Loading..." message hangs indefinitely or your spreadsheet responds very slowly, a formula may be stuck. The syntax and formula structure are correct, but the issue occurs due to data limits or an unexpected bug. This can make your whole spreadsheet slow or even freeze completely.

Google's official documentation warns that the "Loading..." issue can be caused by too many requests. You may see the message: "Error Loading data may take a while because of the large number of requests."

To resolve this, Google recommends reducing the number of import functions used across your spreadsheets.

The official documentation points out key official limits to be aware of:

10MB of received data per request.
The 600-user share limit for the source sheet.

But the official documentation doesn’t cover the bulk of the difficulties when you encounter this error. Let’s have a look at the most frequent user-reported loading issues and their solutions:

You hit a data wall. User experience suggests a limit of around 50,000 cells. In our tests, with text-heavy cells, this number drops to around 15,000 cells.
Solution: Import only what’s necessary (For example, specify ranges like A1:D100 rather than entire columns).
Too many separate requests. Each IMPORTRANGE is quite a heavy request. Having too many individual formulas affects performance and can trigger usage limits and cause errors.
Solution: Use a single IMPORTRANGE formula to bring all the necessary data into a dedicated sheet, rather than making additional IMPORTRANGE calls in individual cells. Then, you can refer to that dedicated sheet as much as you need.
Complex calculations. The receiving spreadsheet cannot display imported data until every calculation is completed in the source spreadsheet.
Solution: Filter/aggregate in the source sheet first and only then import the data (For example, sum rows there in the source sheet and import the total). For maximum control in complex workflows, especially if live feed is not necessary, consider using dedicated tools. Add-ons like Combine Sheets, Filter and Extract Data, or Split Sheet can import static data to the target spreadsheet, which eliminates refresh delays.
Imports that depend on other imports. Your final sheet must wait for every other sheet in the chain to finish loading first.
Solution: Create a "Data_Hub" sheet with one IMPORTRANGE to pull all source data and reference this local copy for all calculations instead of repeating imports.
Unpredictable bugs. Sometimes random "Loading..." freezes without clear triggers. 😓
Solution: Refresh your browser or wait several minutes for the spreadsheet to recover.
Incorrect file format. If you are trying to import data from an Excel file (.XLSX) stored in Google Drive, it can cause persistent loading failures.
Solution: Convert to Google Sheets. Avoid .XLSX files and save as native Google Sheets format.

Frequently Asked Questions (FAQ)

Q1: What is the difference between IMPORTRANGE and simple copy-pasting?

A: When you are copy-pasting information, you create a static copy, like a screenshot, with no link back to the original file. Even if someone updates something later, your copy won't change. IMPORTRANGE, on the other hand, keeps a real-time connection with the source file. Any updates in the original sheet automatically appear in your sheet.

Q2: Does IMPORTRANGE have a data limit?

A: The IMPORTRANGE formula can only import data of 10 MB maximum. If the range you select is too large, you will get an error. Moreover, while Google's official documentation is not explicit on a hard limit, there's a practical cap on performance. Historically, a single sheet could reliably pull data from up to 50 different source spreadsheets. Going over this limit can slow down your sheet and cause errors. To overcome this, you can use a dedicated tool. For example, the Combine Sheets add-on is perfect for large-scale projects or reports.

Q3: How to import ranges from one sheet to another and preserve formatting?

A: IMPORTRANGE copies data only without any of the original formatting (like colors, fonts, or borders). You'll need to manually reformat the imported data to match your style. If keeping the original formatting is important to you, it would be useful to try out the Combine Sheets add-on. It merges data while perfectly preserving all the source formatting, saving you time and hassle.

Q4: Why do I keep getting the #REF! error?

A: This is almost always a one-time permission issue. For security, Google Sheets requires you to grant access for one sheet to pull data from another. You only have to do this once for each unique spreadsheet you connect to.

Q5: Is there any difference between IMPORTRANGE and IMPORTDATA?

A: Yes. IMPORTRANGE imports data from another Google Sheet into your current sheet. IMPORTDATA brings in data from public web files like CSV or TSV formats. They're both for importing, but work with completely different sources: IMPORTRANGE for spreadsheets, IMPORTDATA for web data files. The big differences? IMPORTRANGE needs sheet access and updates every 30 minutes, while IMPORTDATA works with public links but refreshes only hourly.

Q6: My formula is making my sheet slow. What can make it work quicker?

A: If your sheet is slow due to IMPORTRANGE, try importing only what’s necessary. This will reduce data load. For complex tasks like merging or filtering large datasets, use optimized tools like our Filter and Extract Data or Merge Sheets add-ons instead of manual formulas — they’re designed for speed!

Q7: Is there a formula-free way to combine data?

A: Absolutely. While you can manually stack data with array formulas like ={IMPORTRANGE(...); IMPORTRANGE(...);...}, this can be tricky to debug. This is a perfect scenario for using a dedicated tool. For a much simpler, no-formula solution, add-ons like Combine Sheets or Merge Sheets are designed to merge multiple sheets in a few clicks, saving you time and preventing errors.

Q8: How to split Google Sheets?

A: You may create new Google Sheets for each split category. Then, use IMPORTRANGE in each new sheet to import only the matching rows. But it’s time-consuming if you have many splits. The best way is to use special add-ons like Split Sheet. It generates the correct formula in each new sheet it creates, giving you a complete set of live, auto-updating sheets in seconds.

Q9: How to archive data in Google Sheets?

A: This is a simple but crucial two-step process. First, you pull the live data using IMPORTRANGE. Second, you must break this link: select all the imported cells, copy them, and then Paste as values only. The formula will be replaced by the static data, creating an archive.

Hope this guide makes IMPORTRANGE easier to use! If anything’s still unclear or you’ve got a tricky use case — drop your question in the comments below. Your questions could shape the next post 😉