Graphed LogoGraphed
Login

How to Pull Data from One Spreadsheet to Another

Cody Schneider•

Manually copying and pasting data between spreadsheets is a recipe for errors and wasted time. A better way is to create a live link between them, so your reporting sheet automatically pulls the latest information from your source sheet. This gives you a single source of truth and saves you from the tedious work of keeping multiple files in sync.

This tutorial will guide you through the best methods for pulling data from one spreadsheet to another in both Google Sheets and Excel. We’ll cover the go-to formulas and tools you need to connect your workbooks and automate your reporting.

Why Pull Data Between Spreadsheets?

Before diving into the "how," let's quickly cover the "why." Linking spreadsheets is a fundamental skill for anyone who works with data. It unlocks several powerful workflows:

  • Creating Dashboards: You can build a master dashboard that pulls key metrics from several different files - like sales data from one sheet, marketing metrics from another, and operational KPIs from a third.

  • Separating Raw Data from Reports: It's a best practice to keep your raw data file clean and untouched. You can import that raw data into a separate reporting spreadsheet for analysis, calculations, and visualizations, protecting the integrity of your original data source.

  • Collaborating Securely: Sometimes you need to share a summary report with a colleague but don't want to give them access to the entire raw dataset. You can import just the specific columns or rows they need into a separate, shareable sheet.

  • Reducing Manual Errors: Any time you rely on copy-pasting, you risk pasting over the wrong cells, grabbing the wrong range, or simply forgetting to update it. Linking your sheets removes this risk by creating an automated, live connection.

How to Pull Data in Google Sheets: The IMPORTRANGE Function

In the Google Sheets world, one function reigns supreme for this task: IMPORTRANGE. This elegant function is designed specifically to import a range of cells from one spreadsheet into another. It’s simple, powerful, and the primary way to connect your Sheets.

Mastering the IMPORTRANGE Formula

The syntax for the formula is straightforward. In your destination sheet (where you want the data to appear), you’ll type:

Let's break down the two arguments:

  • spreadsheet_url: This is the full URL of the source spreadsheet (the one you're pulling data from). Just copy it directly from your browser's address bar and paste it inside quotation marks.

  • range_string: This tells Google Sheets which cells to grab. It needs to include the sheet (tab) name followed by the cell range, like "Sheet1!A1:D100". Both the sheet name and range go inside one set of quotation marks.

Step-by-Step Guide to Using IMPORTRANGE

Let’s walk through an example. Imagine we have a "Source Sales Data" sheet and we want to pull a table of sales figures into our "Monthly Report" sheet.

Step 1: Get the Source Sheet URLOpen your source spreadsheet and copy its entire URL. Let's say it's:https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ_12345/edit

Step 2: Identify the Data RangeLook at your source sheet and figure out the exact sheet name and cell range you want. For our example, the data is on a tab called "SalesQ1" and occupies cells A1 through F50. So, our range string will be "SalesQ1!A1:F50".

Step 3: Write the Formula and Grant AccessNow, go to your destination sheet, click on the cell where you want the data to start (e.g., A1), and type the complete formula:

When you press Enter, you'll see a #REF! error. This is normal the first time. Hover your mouse over the cell, and a small button will appear asking you to "Allow access." Click it.

This is a one-time security step that connects the two sheets. Once you grant permission, the data from your source sheet will automatically populate into your report. Any changes made in the source sheet will now reflect in the destination sheet in real-time.

Pro Tips for IMPORTRANGE

While IMPORTRANGE is great on its own, it becomes even more powerful when combined with other functions.

  • Filter Your Data with QUERY: What if you only want to import rows that meet specific criteria (e.g., sales from a particular region)? You can wrap your IMPORTRANGE formula inside a QUERY function. The syntax is a bit more advanced but incredibly useful.

This formula imports the data first, then the QUERY function filters it to show only rows where the second column (Col2) is 'North America'.

  • Look Up Values with VLOOKUP: You can also use IMPORTRANGE inside of a VLOOKUP to search for a value in a completely different spreadsheet. It becomes the lookup table for your formula.

How to Pull Data in Excel

Excel offers a couple of excellent ways to link data between workbooks, from a simple, direct link to a much more robust connection using Power Query.

Method 1: Direct Cell Linking

The simplest way to link Excel workbooks is by creating a direct formula reference. This works best when you want to pull a single value or a small, static range of data.

Step-by-Step Guide:

  1. Open Both Workbooks: Have both the source file (where the data lives) and the destination file (where you want it to go) open on your computer.

  2. Start Your Formula: In the destination file, click the cell you want to populate and type a single equals sign (=).

  3. Click Over to the Source File: Without clicking anywhere else, switch to the source workbook. Click on the sheet tab and then click the exact cell you want to link. You'll see the cell outlined with flashing dotted lines.

  4. Press Enter: Hit the Enter key. Excel will automatically take you back to your destination sheet, and the cell will now display the value from the source cell.

If you look at the formula bar, you'll see something like this:

This formula tells Excel to get the value from cell A1 on Sheet1 inside the 'Source Data.xlsx' workbook. A major downside is that these links can be fragile. If you move or rename the source file, the link will break and you'll have to fix it manually.

Method 2: Using Power Query (Get & Transform Data)

For pulling entire tables or larger datasets, Power Query is by far the best method in modern Excel. It creates a stable, refreshable connection that can handle transformations like filtering, sorting, or cleaning the data before it even lands in your sheet.

Step-by-Step Guide using Power Query:

  1. In your destination workbook, go to the Data tab on the Ribbon.

  2. In the "Get & Transform Data" section, click on Get Data → From File → From Workbook.

  3. A file dialog box will open. Navigate to and select the source Excel file you want to pull data from, then click Import.

  4. The Navigator window will appear, showing you all the sheets and any named tables within that workbook. Select the sheet or table containing the data you need. A preview will appear on the right.

  5. You now have two options:

    • Load: Click this to pull the data directly into a new sheet in your workbook. It will appear as a specially formatted Excel Table.

    • Transform Data: This is the more powerful option. It opens the Power Query Editor, a tool that lets you clean your data. You can remove columns, filter rows, change data types, and perform hundreds of other transformations before loading it.

  6. Once your data is loaded, you have a live connection. To update it with the latest information from the source file, simply go to the Data tab and click Refresh All, or right-click on the table and choose Refresh.

Best Practices for Linked Spreadsheets

Regardless of whether you use Google Sheets or Excel, following a few best practices will save you from future headaches.

  • Use Named Ranges: Instead of using static ranges like "A1:G100," define them as a Named Range in your source file (e.g., "Sales_Data"). This way, if you add rows to your original dataset, the named range can expand with it, and your formulas won’t need to be updated. It also makes your formulas much easier to read.

  • Document Your Links: If you have a complex dashboard pulling from multiple sources, it can be easy to lose track. Use notes, comments, or even a dedicated "Documentation" tab to keep a record of what data is coming from where.

  • Understand Permissions: Remember that anyone you share the destination file with also needs at least "view" access to the source file. If they don't, they will see errors instead of the imported data.

  • Organize Your Files: If using Excel's direct linking, try to keep source and destination files in the same cluster of folders. Moving files around is the number one cause of broken links. Power Query is more resilient to this, but good organization is always wise.

Final Thoughts

Learning how to pull data from one spreadsheet to another moves you from a passive data user to an active report builder. Whether you choose the elegant IMPORTRANGE in Google Sheets or the robust Power Query in Excel, these skills help save you time, reduce manual errors, and create more reliable and dynamic reports.

While mastering these spreadsheet functions is a huge step up from manual copy-pasting, it's often just the first step. As your need for real-time, multi-source reporting grows, you'll still spend time connecting and maintaining these links. This is exactly why we built Graphed. Instead of wrestling with spreadsheet formulas, you connect sources like Google Sheets, Shopify, and Google Analytics once. Then, you can simply ask for a report or dashboard in plain English, and it’s created for you in seconds with data that updates automatically.