How to Pull Data from Another Sheet in Google Sheets

Working with data spread across multiple tabs or even separate files is a daily reality in Google Sheets. Instead of manually copying and pasting information between them, you can create a direct, live link. This article will show you exactly how to pull data from another sheet, whether it’s in the same spreadsheet or a completely different one.

Why Pull Data From Another Sheet?

Before we get into the "how," let's quickly cover the "why." Linking sheets is a powerful way to organize your work and create a single source of truth. Common reasons include:

• Creating a Master Dashboard: You can consolidate key metrics from various departmental sheets (e.g., Sales, Marketing, Operations) into one dynamic dashboard for a high-level overview.
• Summarizing Data: If you have raw data in one sheet, you can pull it into another to create summaries, charts, and pivot tables without cluttering up your original dataset.
• Combining Reports: You might have separate sheets for monthly sales reports. You can pull data from each month into a single "Annual Summary" sheet to track year-over-year performance.
• Separating Data Input from Reporting: Allow team members to input data onto a specific sheet, then pull that data into a protected, report-only sheet that they cannot edit accidentally.

Method 1: The Simple Cell Reference (For Tabs in the Same Spreadsheet)

If the data you need is just in a different tab within the same Google Sheets file, the process is straightforward. This method creates a direct reference to a cell or range in another sheet.

How It Works

The syntax for referencing another sheet is simple: SheetName!CellReference.

Let's say you have a sheet named "Q1 Sales" and you want to pull the value from cell B2 into your current sheet. The formula would be:

='Q1 Sales'!B2

Important Note: If your sheet name includes spaces or special characters, you must enclose it in single quotes. For example, if your sheet name is "Q1 Sales Data", the single quotes are required:

='Q1 Sales Data'!B2

Step-by-Step Example

The easiest way to do this is to let Google Sheets write the formula for you.

1. Navigate to the cell in your "destination" sheet where you want the new data to appear. For this example, let's call it the "Summary" sheet.
2. Type the equals sign (=) but don't press Enter yet.
3. Click on the tab of the "source" sheet (e.g., "Q1 Sales").
4. Click on the cell or drag your cursor over the range of cells you want to reference. You'll see Google Sheets automatically write the formula in the formula bar.
5. Press Enter. You will be taken back to your "Summary" sheet, and the data will appear.

When you look at the formula in your "Summary" sheet, it will now correctly reference the source sheet, like 'Q1 Sales'!B2:D10.

Pros and Cons of This Method

• Pro: It's incredibly simple and fast for referencing data within the same workbook.
• Pro: Updates are instantaneous. When a value changes in the source sheet, the referenced cell updates immediately.
• Con: It only works for sheets that are located in the same Google Sheets file. For pulling data from a completely different spreadsheet, you need another function.

Method 2: Using the IMPORTRANGE Function (For Different Spreadsheets)

When you need to pull data from an entirely separate Google Sheets file, the IMPORTRANGE function is your solution. It lets you import a specific range of cells from any spreadsheet you have access to.

Understanding the IMPORTRANGE Syntax

The function is structured like this:

IMPORTRANGE("spreadsheet_url", "range_string")

It takes two arguments, and both must be enclosed in double quotes:

• "spreadsheet_url": This is the full URL of the Google Sheet you want to pull data from. You copy this directly from the address bar of your browser.
• "range_string": This tells Google Sheets which tab and which cells to grab, using the SheetName!CellRange format we discussed earlier (e.g., "Sales Data!A1:G50").

Step-by-Step Guide to Using IMPORTRANGE

Let’s walk through the process of pulling sales data from a spreadsheet called "Master Sales Data" into our "Regional Report" spreadsheet.

Step 1: Get the URL of the Source Spreadsheet

Open the spreadsheet you want to pull data from. In our case, this is the "Master Sales Data" file. Copy its full URL from your browser's address bar. It will look something like this:

https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuV-XYZ/edit#gid=0

Step 2: Identify the Source Data Range

In that same source spreadsheet, identify the tab and cell range you need. Let’s say our data is on a tab called "2024 Sales" and occupies cells A1 through F150. So, our range string will be "2024 Sales!A1:F150".

Step 3: Write the Formula in Your Destination Sheet

Now, go to your "destination" spreadsheet (our "Regional Report"). Click a cell, and type out the IMPORTRANGE formula using the URL and range string you just identified.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuV-XYZ/edit", "2024 Sales!A1:F150")

Step 4: Grant Access Between the Spreadsheets

The first time you connect two separate spreadsheets, you must give permission. After you enter the formula, the cell will show a #REF! error with a small pop-up that says, "You need to connect these sheets."

Simply hover over the cell and click the blue "Allow access" button. This is a one-time security step for each new connection. Once you grant access, the data will instantly populate your sheet.

Troubleshooting Common IMPORTRANGE Errors

• #REF! Error: If you've already granted access, this error usually means the sheet name or cell range is incorrect in your range_string. Double-check for typos and ensure the sheet exists. For example, "Sales" is not the same as "sales".
• Formula Parse Error: This typically happens when you forget the double quotes around the URL or the range string. Both arguments must be treated as text.
• "You don't have permission to access that sheet": This means the Google account you're using doesn't have at least "View" access to the source spreadsheet. Open the source sheet and ensure it is shared with you.

Combine IMPORTRANGE with QUERY for More Power

Pulling an entire dataset is useful, but often you only need a subset of it. Instead of importing hundreds of rows and then filtering them, you can wrap your IMPORTRANGE function inside a QUERY function. This lets you filter, sort, and select specific columns before they even hit your new sheet, which is far more efficient.

The QUERY function lets you use SQL-like language to manipulate your data.

Example: Pulling Only Specific Data

Imagine your "Master Sales Data" sheet has columns for Date (A), Sales Rep (B), Region (C), and Sale Amount (D). You only want to pull sales from the "North" region.

Your formula would look like this:

=QUERY(IMPORTRANGE("spreadsheet_url", "2024 Sales!A1:D150"), "SELECT * WHERE Col3 = 'North'")

Let's break that down:

• The IMPORTRANGE function works just as before, acting as the data source for the query.
• The QUERY part is "SELECT * WHERE Col3 = 'North'".

Pro Tip: When using QUERY with IMPORTRANGE, you must refer to columns as Col1, Col2, Col3, etc., instead of A, B, C. This is because IMPORTRANGE turns the data into a virtual array, which doesn't have column letters.

Final Thoughts

Mastering how to pull data between sheets is a foundational skill for anyone serious about using Google Sheets for reporting and analysis. For combining tabs in the same document, a simple ='Sheet Name'!A1 reference is perfect. For connecting entirely separate spreadsheets to build a centralized dashboard, the IMPORTRANGE function is the professional-grade tool for the job.

While these formulas are powerful, managing dozens of connections and complex queries can still become a manual chore, especially when your data lives not just in other sheets but across different platforms like Google Analytics, Shopify, Facebook Ads, or your CRM. At some point, the spreadsheet wrangling takes away from actual analysis. At Graphed, we've built a solution for this. We help you connect all your data sources with a few clicks, so instead of troubleshooting formulas, you can just ask questions in plain English, like "Show me a chart of Shopify sales versus Facebook Ads spend" and get a live, interactive dashboard instantly. If you're ready to spend less time building reports and more time acting on them, you might want to give Graphed a try.