If you work in an office environment, you have almost certainly encountered a scenario where your data is scattered across multiple different Google Sheets files. You might have one “master” spreadsheet containing a massive client database, and several smaller “team” spreadsheets that desperately need to reference that exact same data.
Copying and pasting data manually between workbooks is a terrible idea. It is slow, highly prone to human error, and the data instantly becomes outdated the second you paste it. Instead, you should completely automate this process using the powerful IMPORTRANGE function in Google Sheets.
What Does IMPORTRANGE Do?
The IMPORTRANGE function is designed to do exactly one thing: it pulls a specific range of cells from one Google Sheets file (the source) and displays it dynamically in another Google Sheets file (the destination).
Because the connection is dynamic, any changes you make to the source spreadsheet—such as updating a client’s phone number or adding a new row of sales data—will automatically appear in the destination spreadsheet within seconds.
Understanding the Syntax
The basic formula for IMPORTRANGE requires two specific arguments, both of which must be enclosed in double quotation marks.
=IMPORTRANGE("spreadsheet_url", "range_string")
- spreadsheet_url: The full, exact web address (URL) of the source spreadsheet you want to pull data from. (You can also use just the long string of random characters found in the middle of the URL, known as the Spreadsheet ID).
- range_string: The exact name of the sheet tab and the cell range you want to copy (e.g., “Sheet1!A1:D100”).
How to Write the IMPORTRANGE Formula
Writing your first cross-workbook formula is straightforward if you follow these steps precisely.
- Open your Source Spreadsheet (the one containing the data you want to copy).
- Click on the address bar in your web browser and completely copy the URL (it will look something like
https://docs.google.com/spreadsheets/d/1BxiMVs0X.../edit). - Open your Destination Spreadsheet (where you want the data to appear) and click on an empty cell. Make sure you have enough empty rows and columns below and to the right of this cell to fit the incoming data.
- Type the start of the formula:
=IMPORTRANGE(" - Paste the URL you copied, and close the quotation mark:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BxiMVs0X.../edit", - Type the name of the specific tab in the source file, followed by an exclamation mark, followed by the cell range, all enclosed in quotation marks:
"DataTab!A1:E500") - Press Enter.
The Crucial “Allow Access” Step
When you press Enter, the cell will not immediately display your data. Instead, you will see a frustrating #REF! error.
Do not panic; your formula is likely perfectly fine. Because you are linking two entirely separate files, Google Sheets requires explicit permission to bridge the gap between them for security reasons.
- Hover your mouse cursor directly over the cell containing the #REF! error.
- A small dialogue box will pop up stating, “You need to connect these sheets.”
- Click the blue Allow access button.
The error will disappear, and your destination sheet will instantly populate with the data from the source sheet. (Note: You only have to grant permission once per spreadsheet connection).
Pro Tip: Combining IMPORTRANGE with QUERY
While `IMPORTRANGE` is fantastic for copying raw data, pulling thousands of rows into a new sheet just to look at a few specific lines is highly inefficient and can severely slow down your workbook.
To pull only the exact data you need, power users frequently wrap the `IMPORTRANGE` function inside a `QUERY` function. This allows you to filter the incoming data before it even hits your destination sheet.
Example: =QUERY(IMPORTRANGE("URL", "Data!A1:E1000"), "SELECT * WHERE Col3 = 'Complete'")
This advanced formula pulls data from the source sheet, but only actually displays the rows where Column 3 contains the word “Complete”, making your destination spreadsheet significantly cleaner and faster.
