How to Link Data from One Spreadsheet to Another

Juggling data across multiple spreadsheets can feel like a high-wire act where one wrong copy-paste can bring everything crashing down. If this sounds familiar, you'll be glad to know there's a much safer way: linking your spreadsheets directly. This guide will walk you through exactly how to connect data from one sheet to another in both Google Sheets and Microsoft Excel, turning your collection of files into a cohesive, automated system.

Why Bother Linking Spreadsheets?

Before jumping into the "how," let's quickly cover the "why." Linking data instead of manually copying it solves several major headaches:

• It creates a single source of truth. When your "master" data gets updated in one spreadsheet, all linked spreadsheets automatically reflect the change. No more wondering which version is the "correct" one.
• It saves an incredible amount of time. Manually updating weekly or monthly reports is a tedious, repetitive task. Linking automates this process, giving you back precious hours.
• It drastically reduces human error. Every time you manually copy and paste data, you risk grabbing the wrong cells, pasting over existing data, or making a typo. Linking removes that risk entirely.

Ready to stop the copy-paste-panic cycle? Let's connect some data.

How to Link Data in Google Sheets using IMPORTRANGE

Google Sheets makes it incredibly easy to pull data from one workbook into another with a single, powerful function: IMPORTRANGE. This function is your go-to for creating a live link between any two Google Sheets spreadsheets, no matter which Google Drive folder they're in.

Step-by-Step Guide to Using IMPORTRANGE

Let's say you have a "Master Sales Data" sheet and you want to pull a summary into your "Weekly Marketing Report" sheet. Here's how to do it.

Step 1: Get the URL of Your Source Sheet Open the spreadsheet you want to pull data from. In our example, this is the "Master Sales Data" sheet. Copy its full URL from your browser's address bar. It will look something like this:

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

You need the whole thing!

Step 2: Identify the Data Range You Want to Import Next, identify the specific cells you want to import. This is written as a text string that includes the sheet name and the cell range, like "SalesQ1!A1:D50".

• SalesQ1 is the name of the tab (the sheet name).
• A1:D50 is the range of cells.
• It's important to put this whole value in double quotes.

Step 3: Write the IMPORTRANGE Formula Now, go to the destination spreadsheet - where you want the data to appear (our "Weekly Marketing Report"). Click on the cell where you want the top-left cell of your imported data to start, and type the following formula:

=IMPORTRANGE("spreadsheet_url", "range_string")

Using our example, you'd replace the placeholders like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit#gid=0", "SalesQ1!A1:D50")

Press Enter.

Step 4: Allow Access The first time you link two new sheets, your cell will show a #REF! error. Don't panic! This is a security feature. Hover your mouse over the cell, and a small blue button will pop up asking you to "Allow access." Click it.

And that's it! Your data from the source sheet will instantly appear. Whenever the data in "Master Sales Data" from cell A1 to D50 is updated, your "Weekly Marketing Report" will update automatically.

Supercharge IMPORTRANGE with Other Functions

On its own, IMPORTRANGE is great for mirroring data. But its true power is unlocked when you combine it with other functions like QUERY to filter and organize the data as it comes in.

For example, if you only wanted to import sales data for "Campaign A" from your master sheet, you could wrap your IMPORTRANGE function inside a QUERY function:

=QUERY(IMPORTRANGE("URL", "SalesQ1!A1:D50"), "SELECT * WHERE Col1 = 'Campaign A'")

This command tells Google Sheets to first import the entire range and then run a query on that imported data to only show rows where the first column (Col1) contains the text "Campaign A."

How to Link Data in Microsoft Excel

Excel offers a couple of excellent ways to link data between workbooks, depending on your needs. One is a straightforward manual link, and the other is a more powerful and robust method using Power Query.

Method 1: Creating a Direct Cell Reference

This is the simplest way to link workbooks, especially if you just need to pull a few cells or a small range of data. This works best when the files are stored on your local computer or a shared network drive.

Step-by-Step Guide to Direct Linking

1. Open Both Workbooks. Have both the source workbook (the one with the data) and the destination workbook (the one where you want the data to go) open on your screen.
2. Start Your Formula. In your destination workbook, click the cell where you want to link the data and type an equals sign (=) to begin a formula.
3. Click the Source Data. Without pressing Enter, switch over to your source workbook. Navigate to the right sheet and simply click on the cell you want to link to.
4. Press Enter. Your cursor will jump back to the destination workbook. Press the Enter key.

Excel automatically writes the full formula for you. It will look something like this:

=[Budget_Q3.xlsx]Monthly!$A$1

This breaks down as:

• [Budget_Q3.xlsx] is the name of the source file.
• Monthly is the worksheet tab name.
• $A$1 is the specific cell being referenced (the dollar signs mean it's an absolute reference).

Now, whenever the value in cell A1 of the "Monthly" sheet inside "Budget_Q3.xlsx" is updated, this cell will update too. You'll need to open the destination file and allow links to be updated when prompted.

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

For larger datasets or if you need to clean, shape, or filter data before it lands in your workbook, Power Query is the modern, professional-grade solution. It's built into newer versions of Excel (Excel 2016 and later) on the "Data" tab.

Step-by-Step Guide to Using Power Query

1. Start from the Data Tab. In your destination workbook, go to the Data ribbon.
2. Get Data From File. Click on Get Data > From File > From Workbook.
3. Select Your Source File. A file browser window will open. Navigate to and select the Excel workbook you want to pull data from, then click Import.
4. Choose Your Data in the Navigator. The Navigator window will appear, showing you all the worksheets and named tables within the source workbook. Click on the sheet or table you want to import. A preview will appear on the right.
5. Load or Transform. You now have two main options:
6. Refresh at Your Convenience. Once the data is loaded, you can update it at any time by going to the Data tab and clicking Refresh All.

Best Practices and Common Issues

Linking spreadsheets can save you from a lot of problems, but you need to be aware of a few things to keep your workflow smooth.

• Keep Your Naming Conventions Clear. Use descriptive names for your files, worksheets, and any named ranges. SalesData_Q3_2024 is much better than Copy of sales (2).
• Beware of Broken Links (Excel). If you use the direct reference method in Excel, renaming or moving the source file will break the link. Excel will ask you to locate the file next time you update, but it's best to establish a solid folder structure first. This is a major reason why Power Query is a more robust option.
• Mind Your Permissions (Google Sheets). For IMPORTRANGE to work, the user needs at least "View" access to the source spreadsheet. If you share a sheet that uses IMPORTRANGE, make sure the recipient also has access to the sheet it's pulling from.
• Don't Overdo It. While powerful, importing huge amounts of data across dozens of files can slow your spreadsheets down, especially in Google Sheets. Only import the data you truly need. If performance becomes an issue, it's often a sign that you're outgrowing spreadsheets for your data analysis needs.

Final Thoughts

Linking spreadsheets, whether using IMPORTRANGE in Google Sheets or Power Query in Excel, lets you build powerful, automated reports that save time and eliminate errors. By treating one document as the primary source of truth, you can create a reliable system that keeps everyone on the same page without having to manually shuttle data back and forth.

Mastering this is a huge step up, but it often highlights another challenge: getting data into spreadsheets in the first place. For many of us, linking sheets is an essential workaround because our core business data is stranded in separate apps like Google Analytics, Shopify, Salesforce, and Facebook Ads. We built Graphed to solve this very problem by connecting directly to these sources. This way, you don't need a web of spreadsheets acting as middle layers, you can connect your data once, create dashboards automatically, and get answers using plain English. It's the next step beyond linking for anyone who just wants their data in one place.