sodcalculator

10 Tech How-To

How to Link Cells Between Sheets and Workbooks in Excel

Guide 10-46742 · Updated · Step by step

Linking cells in Excel lets you pull a value from one sheet or workbook into another, so when the source changes, the destination updates automatically. This guide covers the exact steps for both same-workbook and cross-workbook links, plus how to handle common issues.

What You’ll Need

  • Microsoft Excel (2016 or later for best compatibility, but steps work in most versions)
  • If linking between workbooks, both files must be saved (preferably in the same folder to avoid broken paths)
  • Basic familiarity with selecting cells and typing formulas

Step-by-Step

Linking Cells Within the Same Workbook (Between Sheets)

  1. Open the workbook that contains both the source sheet (the one with the data) and the destination sheet (where you want the link).
  2. Click on the destination cell where you want the linked value to appear.
  3. Type an equals sign = to start the formula.
  4. Click on the sheet tab of the source sheet (e.g., “Sheet1”). If the sheet is hidden, you can type its name manually.
  5. Click on the source cell that contains the value you want to link (e.g., A1). Excel will automatically insert a reference like Sheet1!A1 into the formula bar.
  6. Press Enter. The linked value appears in the destination cell. If the source cell changes, the destination updates automatically.

Alternative method: Instead of clicking, you can type the formula manually. Use the format =SheetName!CellReference. For example, =Sheet2!B5 pulls the value from cell B5 on Sheet2. If the sheet name contains spaces (e.g., “Sales Data”), enclose it in single quotes: ='Sales Data'!B5.

Linking Cells Between Different Workbooks

  1. Open both workbooks: the source workbook (contains the data) and the destination workbook (where you want the link).
  2. In the destination workbook, click on the cell where you want the linked value.
  3. Type an equals sign =.
  4. Switch to the source workbook (click its window or use Alt+Tab).
  5. Click on the sheet tab that contains the source cell, then click on the cell itself (e.g., C10). Excel will create a reference that includes the workbook name in square brackets, like =[SourceWorkbook.xlsx]Sheet1!C10.
  6. Press Enter. The value appears in the destination cell. If the source workbook is closed, the link still works as long as the file path is valid.

Note: If you type the formula manually, the format is ='[WorkbookName.xlsx]SheetName'!CellReference. For example, ='[Budget.xlsx]Sheet1'!A1. If the workbook name or sheet name has spaces, use single quotes around the entire bracket-and-sheet portion.

Common Mistakes

  • Forgetting to save the source workbook before linking – unsaved changes can cause the link to show stale values or break.
  • Moving or renaming the source file – this breaks the link path. Excel will prompt you to update the source when you open the destination.
  • Using absolute vs. relative references incorrectly – when copying a linked formula, the reference may change unless you use dollar signs (e.g., $A$1).
  • Linking to a cell that contains a formula – if the source formula returns an error, the link will show that error.
  • Typing sheet names without quotes when they contain spaces – this causes a #NAME? error.

Tips

  • Use the Paste Link feature: Copy a cell, right-click the destination, and under Paste Options, choose “Paste Link” (icon looks like a chain link). This creates a direct link without typing formulas.
  • To see all external links in a workbook, go to Data > Queries & Connections > Edit Links (or Data > Edit Links in older versions). This allows you to update, break, or fix links.
  • If you want to link a range of cells (e.g., A1:A10), select the entire range in the destination, type the formula for the first cell, then press Ctrl+Enter to fill the whole selection.
  • Use 3D references to link the same cell across multiple sheets: =SUM(Sheet1:Sheet3!A1) sums A1 from all sheets between Sheet1 and Sheet3.
  • Keep linked workbooks in the same folder to make the file paths shorter and more portable.

Troubleshooting

#REF! error: This usually means the source cell or sheet was deleted, or the link path is broken. Go to Data > Edit Links, select the broken link, and click “Change Source” to point to the correct file.

#NAME? error: Often caused by a missing apostrophe around sheet names with spaces. Check the formula bar and add single quotes if needed.

Link shows 0 or old value: The source might not be recalculated. Press F9 to recalculate the workbook, or check if calculation is set to manual (Formulas > Calculation Options).

Security warning about links: Excel may warn you about external links because they can be used to spread malicious code. If you trust the source, click “Enable Content” or “Update Links” when prompted.

Cannot open source workbook: If the source file is moved or deleted, Excel will ask for the new location. If you no longer need the link, use Edit Links to “Break Links” and replace the linked values with static numbers.

Frequently Asked Questions

Q: Can I link a cell from a closed workbook?
A: Yes, but Excel will show the last saved value until you open the source or trigger a refresh. The link still works when the source is closed, as long as the file path is correct.

Q: How do I link a cell without typing the formula?
A: Use the Paste Link feature: copy the source cell, right-click the destination, and under Paste Options, choose the chain-link icon.

Q: Why does my link show #VALUE!?
A: This often happens when the source cell contains text and the destination formula expects a number, or vice versa. Check the source data and ensure the link is used in the correct context.

Q: Can I link a cell from another sheet in the same workbook using a dynamic sheet name?
A: Yes, you can use the INDIRECT function: =INDIRECT("'"&A1&"'!B5") where A1 contains the sheet name. This allows you to change the sheet reference by editing a cell.

Q: How do I break a link and keep the current values?
A: Go to Data > Edit Links, select the link, and click “Break Links”. This replaces all linked cells with their current values as static numbers.

Q: What if the source cell is in a different file that I don’t want to open every time?
A: You can set the workbook to update links automatically when opened. Go to File > Options > Advanced, scroll to General, and check “Ask to update automatic links” or set it to update automatically. However, the source file must be accessible on your network or local drive.

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 →