How to Link Data to Another Spreadsheet in Excel
Linking data between spreadsheets in Excel 2013, 2010 or 2007 ties cells on separate pages together, automatically updating a target cell whenever the source cell changes. By linking instead of copying data, you can leave your messy calculations on one sheet and display the result cleanly on another. Excel can also link data between entirely separate workbook files, as long as both files are on the same computer or network.
Select the cell or range of cells that currently contain the source data and copy it by pressing "Ctrl-C." If you need to link data from multiple non-consecutive cells, perform all steps on each cell separately.
Switch to the sheet where you want to display the data. If the sheet is in a separate workbook, open it from the File menu or by double-clicking it in Windows, but also leave the original workbook open in Excel until you finish the entire process.
Right-click the target cell for the link. When linking a range of cells, right-click the top-left-most cell. Choose "Paste Link" from the Paste Options to create the link.
Click any linked cell to see or modify the link's source in the formula bar. The formula for links begins with an equals sign, and separates the spreadsheet's name and cell with an exclamation point. For example, the formula "=Sheet1!D2" links to cell D2 on Sheet1. When linking to another workbook, the file name appears first in square brackets, such as "=[Book2.xlsx]Sheet1!D2."
Tips & Warnings
- Excel links data in only one direction. To update a linked cell, you have to update the data at its original source.
- Keep your related Excel workbooks in the same folder on your computer to prevent links between them from breaking if you rearrange your folders.
- If you email or share a workbook that contains links to another workbook, send both workbooks to keep the links working. Alternatively, paste the raw data values into the file, overwriting the links, before sending it.
- Click "New Window" on the View tab to open a second window showing your workbook. Place the two windows side by side to make working with two sheets easier.
- When linking between workbooks, renaming, moving or deleting the file containing the source data causes the link to break. To fix the links, either update the formula by hand or click "Edit Links" in the Connections group of the Data tab and press "Change Source" to locate the source file. Excel also checks for broken links when you open a file and prompts you to fix them.