sodcalculator

10 Tech How-To

Link Data Between Google Sheets Tabs: Easy Guide

Guide 10-56240 · Updated · Step by step

This guide shows you how to pull data from one tab (or sheet) into another within the same Google Sheets file, and also how to bring data in from a completely different spreadsheet file. You’ll learn the exact formulas to use, how to reference cells and ranges correctly, and how to fix the most common errors that come up.

What You’ll Need

  • A Google account with access to Google Sheets.
  • The spreadsheet file(s) containing the data you want to link.
  • The exact name of the tab (bottom of the window) you’re pulling from.
  • For cross-file links: the URL of the source spreadsheet.

Step-by-Step

Part 1: Linking Data Between Tabs in the Same File

  1. Open your Google Sheet. Make sure the file contains both the tab with the source data and the tab where you want the linked data to appear.
  2. Click on the destination tab (the one where you want the data to show up) at the bottom of the screen.
  3. Click on the cell where you want the first piece of linked data to appear.
  4. Type the equals sign (=) to start your formula.
  5. Switch to the source tab by clicking its name at the bottom of the window. This is the tab that contains the data you want to pull.
  6. Click on the cell that contains the data you want to link. For example, click on cell A1.
  7. Press Enter. Google Sheets will automatically switch you back to the destination tab, and the formula will appear as something like =Sheet2!A1. The value from that cell will now be displayed.

To link a whole range of cells:

  1. Instead of clicking a single cell, select a range (e.g., A1:C10) on the source tab.
  2. Press Enter. The destination cell will show the first value of the range. To see all the values, click and drag the blue square in the bottom-right corner of the destination cell down and across to fill the adjacent cells.

Part 2: Linking Data from a Completely Different Spreadsheet File

  1. Open the destination spreadsheet (the one where you want the data to appear).
  2. Copy the URL of the source spreadsheet (the file you’re pulling data from). You can find this in your browser’s address bar.
  3. Click on the destination cell where you want the data to start.
  4. Type the following formula: =IMPORTRANGE("spreadsheet_url", "sheet_name!range")
  5. Replace spreadsheet_url with the full URL you copied in step 2, keeping it inside the quotation marks.
  6. Replace sheet_name!range with the name of the tab and the cell range you want to pull. For example: "Sheet1!A1:C10". If the sheet name has spaces, put it in single quotes inside the formula, like this: "'Data Entry'!A1:C10".
  7. Press Enter. The first time you use IMPORTRANGE with a new source file, a pop-up will appear asking you to Allow access. Click it. This is required to connect the two files.
  8. Wait a few seconds for the data to load. It may take a moment, especially for large ranges.

Common Mistakes

  • Incorrect sheet name: The sheet name in the formula must match the tab name exactly, including spaces and capitalization. If the tab is named “Data 2024”, writing Data2024 will cause a #REF! error.
  • Forgetting the exclamation mark: The syntax requires an exclamation point after the sheet name, like Sheet1!A1. Missing it breaks the formula.
  • Using relative references when copying formulas: When you copy a formula like =Sheet2!A1 down a column, it will automatically change to =Sheet2!A2, =Sheet2!A3, and so on. If you want to always reference the same cell, use a dollar sign: =Sheet2!$A$1.
  • Not allowing access for IMPORTRANGE: If you skip the “Allow access” prompt, the formula will return a #REF! error. You must click “Allow access” for each new source file.
  • Mixing up row and column order: In a range like A1:C10, the first letter is the column and the number is the row. Reversing this (1A:10C) will cause an error.

Tips

  • Use named ranges for clarity: Instead of referencing Sheet2!A1:C10, you can name that range (Data > Named ranges) and then use =MyNamedRange. This makes formulas much easier to read and maintain.
  • Link entire columns for dynamic data: To pull an entire column that may grow over time, use a range like Sheet2!A:A. This will include any new data added to column A.
  • Use QUERY with IMPORTRANGE for specific data: To pull only certain rows or columns from another file, combine them: =QUERY(IMPORTRANGE("url","Sheet1!A1:D100"), "select Col1, Col3 where Col2 = 'Yes'"). This pulls only columns 1 and 3 from rows where column 2 is “Yes”.
  • Keep source tabs organized: If you’re linking to a tab that is frequently reordered or renamed, your formulas will break. Consider using a dedicated “Data” tab that you don’t rename.
  • Use the Explore feature for quick references: Type = in a cell, then type the name of the sheet and a cell. Google Sheets will often suggest the correct syntax.

Troubleshooting

  • #REF! error: This usually means the reference is invalid. Check the sheet name spelling, the cell range, and that you’ve allowed access for IMPORTRANGE. For IMPORTRANGE, this error also appears if the URL is malformed.
  • #NAME? error: This means Google Sheets doesn’t recognize the formula name. Check for typos in IMPORTRANGE or QUERY. Also, make sure you don’t have a space before the equals sign.
  • #VALUE! error: This often happens when you’re trying to link a range of multiple cells into a single cell. If you select A1:C10 on the source, you must also select a range of the same size on the destination (e.g., click cell A1, then drag to C10 before pressing Enter).
  • Data doesn’t update: IMPORTRANGE refreshes periodically, but it can take a few minutes. To force a refresh, make a small edit to any cell in the destination sheet (like adding a space to a cell and deleting it).
  • Circular dependency warning: This happens if the source tab references the destination tab, creating a loop. Check that your source data doesn’t contain a formula pointing back to the destination sheet.
  • Permissions issue with IMPORTRANGE: If you see a #REF! error with a message about permissions, the owner of the source spreadsheet may have restricted access. You need at least “Viewer” access to the source file.

Frequently Asked Questions

Can I link data from a tab in one file to a tab in a different file?

Yes. Use the =IMPORTRANGE() formula. You’ll need the full URL of the source file, the tab name, and the range. You’ll also need to grant access the first time.

What does the exclamation mark mean in a formula like =Sheet2!A1?

The exclamation mark separates the sheet name from the cell reference. It’s a standard part of the syntax that tells Google Sheets which tab to look in.

Can I link a whole row or column without typing every cell?

Yes. Use a range like =Sheet2!A:A for an entire column or =Sheet2!1:1 for an entire row. This is useful for data that grows over time.

Why does my IMPORTRANGE formula show “#REF!” and ask for access every time?

This usually means you haven’t clicked “Allow access” on the pop-up that appears the first time. If you’ve already clicked it, check that you have edit access to the destination file and view access to the source file.

How do I link data from a specific tab when the tab name has spaces?

Wrap the sheet name in single quotes inside the formula. For example: =IMPORTRANGE("url", "'My Data'!A1:B10").

Is there a way to link data that automatically updates when the source changes?

Yes, both =Sheet2!A1 and =IMPORTRANGE() update automatically. The same-file reference updates instantly. IMPORTRANGE updates periodically, usually within a few minutes, but you can force a refresh by making a small edit to the destination sheet.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →