How to Link Excel Worksheets
Author: Patricia Lynn
To be able to use data in an Excel worksheet that already exists in another worksheet, Excel lets you link data across worksheets and even across workbooks.
Linking data has many applications. Prices can be stored in a master price list which other worksheets can link to. Linking is useful for creating summary worksheets as well. Consider a sales manager who has detailed worksheets for each salesperson and wants a summary worksheet to compare salesperson performance and calculate total sales.
The source worksheets provide the data, while the destination worksheet receives the data. When cell values change in the source worksheets, Excel automatically updates the destination worksheet the next time it is opened.
Linking the Cells
- Open all source workbooks and the destination workbook.
- Format the cells you will be linking to in the destination workbook.
- In the source worksheet, select the cell you want to link to and click the Copy button.
- Switch to the destination worksheet and click the cell where you want the link. Then:
- In Excel 2007, from the Home tab, click the down arrow below Paste and click Paste Link.
- In Excel 2003 and older, on the Edit menu, click Paste Special, and then click Paste Link.
- Go back to the source worksheet and press ESC to remove the squiggly lines.
You can also link a range of cells. Copy the range of cells and in the destination worksheet, click in the worksheet where you want the upper-left cell of range of cells to be located. Then Paste the link.
For our example, we could click on cell B6 on the Atlanta worksheet and click the Copy button. Then we would go to the Totals worksheet, click in cell B3, and Paste the link. If you look at the formula in the cell, it will contain the complete path. Below is a picture of the Atlanta and Totals worksheets.
Relocating Worksheets
Before creating your links, put some thought into where your Excel files are stored. If all of the worksheets are in the same workbook, moving the workbook should not be a problem. However, if the source and destination workbooks are in different folders and if you move a source workbook, the link will break.
Manually Entering Link Formulas
When linking cells in worksheets that are in the same workbook, you can enter the formula manually. Type =SheetName!CellReference. The formula in our example would be =Atlanta!B6.
If the workbooks are in the same folder, you can also enter the formulas manually. However, if both workbooks are open and a source link is changed, the destination link will not update immediately as it does with the Copy and Paste link method. This may not be a concern, however.
The formula for linking to another workbook or file in the same folder is =[Filename.xls]Sheetname!CellReference. If the workbooks are in different folders, do not enter the formulas manually.
Microsoft Office Security Warning
When you open the destination worksheet, you may get the security warning window below with newer versions of Excel:
Pressing the Options button will display the window below. To allow the linking process to proceed, you must click the radio button by "Enable this content" and then click OK.
If you do not see the need to go through this process each time you open the destination worksheet, click "Open the Trust Center" in the lower left corner of the window displayed above and click the External Content tab. In the second section, click Enable automatic update for all Workbook Links.
Protect Your Link Formulas
When you have formulas in worksheets, it is important to protect them from accidental deletion. All cells are locked by default, but the feature has no effect until you Protect the Worksheet. Therefore, before Protecting the Worksheet, make sure you unlock any cells that you do want to be able to edit or enter data into. You can do this in two ways.
- If only a few of the cells need to be unlocked, click on a cell, right-click Format Cells, click the Protection tab, and uncheck Locked. You can format multiple cells at one time by pressing and holding the Ctrl key while clicking in the cells. When finished, Protect the Worksheet.
- If most of the cells should be unlocked, click in the upper left corner of the sheet to select all of the cells. (or press Ctrl + A), right-click Format Cells, click the Protection tab, and uncheck Locked. Then manually lock the individual cells via the Protection tab.
To Protect the Worksheet in Excel 2007, press the Review tab on the ribbon and click Protect Sheet. Type a password if desired and click OK. Make sure you will remember the password or you will not be able to change the formulas in your own worksheet. For Excel 2003 and older, click Tools | Protection | Protect sheet. Type a password if desired and click OK.
When you click in a locked cell after Protecting the Worksheet, Excel will inform you that the cell is locked.
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-linking-worksheets.shtml">How to Link Excel Worksheets</a>
► Related tutorial: Excel Keyboard Shortcuts. Increase your productivity by using some of Excel's cool keyboard shortcuts.
► Featured tutorial: Your Credit Score! Don't be fooled by websites selling products! Get the facts with our comprehensive article.
