How to Call Data from Another Sheet in Google Sheets

Pulling data from another sheet in Google Sheets is a simple way to create powerful dashboards, consolidate reports, and keep your data organized without endless copy-pasting. Instead of manually updating information across multiple spreadsheets, you can link them together so your master files update automatically. This article walks you through how to reference data from other tabs and even entirely separate Google Sheets files using a few simple formulas.

Why Pull Data From One Sheet to Another?

Working with data across different sheets or files is a common need for anyone trying to analyze business performance. You probably download reports from different ad platforms, SaaS tools, and CRMs, each ending up in its own spreadsheet. Manually consolidating this data is a recipe for errors and tedious work.

Here are a few common scenarios where calling data from another sheet is a lifesaver:

Creating a Master Dashboard: You can pull key performance indicators (KPIs) from individual sheets - like weekly ad spend, monthly sales figures, and website traffic reports - into a single summary dashboard.
Centralizing Information: Keep a "single source of truth" like a master customer list or product inventory in one sheet. Other team members can then pull from this master list into their own reports to ensure everyone is working with the most current data.
Combining Raw Data Silos: You might have separate sheets for Facebook Ads, Google Ads, and email marketing performance. You can import the data from each into a central analysis spreadsheet to calculate overall ROI and see the full picture.
Controlling Data Access: Instead of sharing a massive spreadsheet with sensitive information, you can create a separate "public" sheet that uses a formula to pull only the specific, non-sensitive columns needed by other teams.

Method 1: The Simple Cell Reference (For Sheets in the Same File)

The easiest way to call data is when the sheets exist as different tabs within the same spreadsheet file. For this, you don't need a special function - just a specific syntax.

The format is: SheetName!CellReference

You simply type the name of the sheet, an exclamation mark, and the cell or range of cells you want to reference.

Example: Referencing a Single Cell

Imagine you have a Google Sheet file named "Q4 Sales Report." Inside this file, you have two tabs:

A tab named "Sales Data" with raw sales numbers.
A tab named "Summary" where you want to display the total sales figure.

In the "Sales Data" tab, let's say cell E2 contains the total sales amount of $50,000. To pull this value into your "Summary" tab, you would go to any cell in the "Summary" sheet and type:

='Sales Data'!E2

Press Enter, and the value $50,000 will instantly appear. Now, if the value in 'Sales Data'!E2 ever changes, the "Summary" tab will update automatically.

Note: If your sheet name includes spaces or special characters (like "Sales Data" or "Q4-Report"), you must wrap the sheet name in single quotes.

Example: Referencing a Range of Cells

You can also pull a whole block of data. For example, to pull the entire range from A1 to D10 from the "Sales Data" sheet into your "Summary" sheet, you would type:

='Sales Data'!A1:D10

The entire table of data from that range will appear in the "Summary" sheet, starting from the cell where you entered the formula.

Method 2: Using IMPORTRANGE (For Sheets in Different Files)

What if your data lives in a completely different Google Sheet file? This is where the powerful IMPORTRANGE function comes in. It's designed specifically to connect and pull data from one spreadsheet into another.

The syntax for this function looks like this:

=IMPORTRANGE("spreadsheet_url", "range_string")

Let's break down its two arguments:

spreadsheet_url: This is the full URL of the Google Sheet you want to pull data from. You just copy it directly from your browser's address bar. It must be enclosed in double quotes.
range_string: This tells Google Sheets which tab and which cells to import. The format is "SheetName!A1:B10". This also needs to be enclosed in double quotes.

A Step-by-Step Guide to Using IMPORTRANGE

Let's walk through an example. Suppose we want to import sales data from a spreadsheet named "Master Sales Ledger" into our "Q4 Sales Report" spreadsheet.

Step 1: Get the URL of the Source Sheet

Open the "Master Sales Ledger" spreadsheet and copy its full URL from the address bar. It will look something like this:

https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ123abcDEF/edit#gid=0

Step 2: Identify the Data Range

In the "Master Sales Ledger" sheet, locate the tab and cell range you want to import. For our example, let's say the data is on a tab called "SalesData" in the range A1:F50.

Step 3: Write the Formula

Now, go to the "Q4 Sales Report" spreadsheet - the destination sheet where you want the data to appear. Click on a cell (preferably A1) and type out the IMPORTRANGE formula using the URL and range string:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ123abcDEF/edit", "SalesData!A1:F50")

Step 4: Grant Permission to Connect the Sheets

The first time you connect two separate spreadsheets, Google Sheets will show a #REF! error with a small pop-up message: "You need to connect these sheets. Allow access."

This is a critical security step. Hover over the cell with the formula and click the blue "Allow access" button. Once you do, the sheets will be connected, and the data will instantly load. You only have to do this once for each new pair of sheets you connect.

Troubleshooting Common #REF! Errors with IMPORTRANGE

The #REF! error is the most common issue you'll encounter. Here are the usual culprits and how to fix them:

You Haven't Granted Access: If you see the "Allow access" button, you just need to click it. If you don't see the button and still have the error, it likely means you don’t have at least "Viewer" permissions for the source spreadsheet.
The URL or Range String is Incorrect: Double-check that you've correctly pasted the full URL and that your sheet name and cell range are spelled perfectly. Remember to wrap both arguments in double quotes. A common mistake is misspelling the sheet name (e.g., "Sheet 1" instead of "Sheet1").
Something Is Blocking the Data Output: IMPORTRANGE needs enough blank cells below and to the right to output the entire range you requested. If there's any data - even a single number or space - in a cell where it's trying to place the imported data, it will return a #REF! error with the message, "Array result was not expanded because it would overwrite data..." Clear the cells in the destination area, and the formula will work.

Power Tips: Combining IMPORTRANGE with Other Functions

IMPORTRANGE on its own is great, but its true power is unlocked when you combine it with other functions like QUERY and VLOOKUP. This allows you to filter, sort, and analyze your data as you import it, rather than pulling over an entire raw dataset.

Using IMPORTRANGE with QUERY

QUERY is arguably Google Sheets' most powerful function, letting you use SQL-like commands to manipulate data. By wrapping IMPORTRANGE inside QUERY, you can import only the data that meets certain criteria.

When you use QUERY with data from IMPORTRANGE, you can't refer to columns by their letters (A, B, C). Instead, you refer to them by column number: Col1, Col2, Col3, and so on.

Example: Filtering Imported Data

Let's say our "Master Sales Ledger" has sales data with the Product Category in Column C and Total Sale Amount in Column F. If we only want to import sales from the "Electronics" category where the sale amount was greater than $500, our formula would look like this:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ123abcDEF/edit", "SalesData!A1:F50"), "SELECT * WHERE Col3 = 'Electronics' AND Col6 > 500")

This single formula connects to the other sheet, imports the data, filters it down to only the relevant rows, and displays the clean, filtered list - all in one step.

Using IMPORTRANGE with VLOOKUP or XLOOKUP

Another incredibly common use case is looking up information from a master list in a separate file. For example, you have a sheet with new sales leads and want to pull in the assigned salesperson from a master "Territory Map" spreadsheet.

Example: Looking up Data from another Spreadsheet

Let's say Sheet1 of our active spreadsheet contains a list of state names in Column A. We have a separate master sheet ("Territory Map") that lists states (Column A) and the assigned salesperson (Column B).

We can use VLOOKUP with IMPORTRANGE to pull the salesperson's name for each state:

=VLOOKUP(A2, IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-territory-map-url/edit", "Territories!A1:B50"), 2, FALSE)

Let's break this down:

A2: This is the search key (the state name in our current sheet).
IMPORTRANGE(...): This imports the entire territory map (Columns A and B) from our master file to serve as our lookup table.
2: This tells VLOOKUP to return the value from the 2nd column of our imported range (the salesperson's name).
FALSE: This ensures an exact match for the state name.

Final Thoughts

Learning how to reference data across different tabs and files with simple formulas and IMPORTRANGE is a fundamental Google Sheets skill. It allows for organized, centralized data and the creation of dynamic, automated reports that reduce manual work and the risk of error.

While mastering spreadsheet formulas is a big step, it's still a very hands-on process. At some point, you may end up spending more time managing formulas and troubleshooting connections than analyzing the actual insights. We experienced this exact reporting gridlock ourselves, which is why we built Graphed. Instead of trying to wrangle IMPORTRANGE and QUERY formulas, our platform connects directly to your data sources - including Google Sheets, Google Analytics, Shopify, and your CRM - so you can build real-time, interactive dashboards just by describing what you want to see in plain English.