How to Link Excel Worksheet Data

Excel provides the ability for one worksheet to link to data in other worksheets. In this tutorial, you will learn how to link worksheet data, and also learn hints and tips on how to work with this cool feature of Microsoft Excel.

Author: Patricia Lynn

If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We've reviewed this course (over 9 hours of hands-on lessons) and found it very well done.

Now - let's talk about linking worksheet data!

Overview

The linked worksheets can be in the same workbook or in different workbooks. The source worksheets provide the data, while a destination worksheet receives the data. When cell values change in the source worksheets, Excel automatically updates values in the destination worksheet the next time it is opened.

Why Do We Link Worksheet Data?

The ability to link data between Excel worksheets and workbooks can often eliminate the need to have identical data entered and updated in multiple worksheets. This saves time, reduces the chance for error, and improves overall data integrity.

The ability to link worksheets together in Microsoft Excel can have many applications. For example, a company's prices can be stored in a 'Master Price List' worksheet, and others needing pricing data can link to that worksheet.

Linking worksheet data is great for creating summary worksheets. Consider a Sales Manager who has detailed worksheets for each salesperson, but would like a summary worksheet that links to the individual salespersons' worksheets, compare's salesperson performance, and calculates grand totals.

Now let's learn how to link some Excel worksheets together.

Ready the Worksheets

Open a source worksheet and a destination worksheet. Pre-format each cell in the destination worksheet that will be linked to a cell in a source worksheet.

Linking the Cells

1. In the source worksheet, select the cell you want to link to and click the Copy button on the Home tab (or right-click and select Copy).
2. 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.
3. Return to the source worksheet and press ESC to remove the animated border around the cell.

To link a range of cells, select the cells and click the Copy button. In the destination worksheet, click the cell where you want the upper-left cell of the range of cells to be located. Then Paste the link as directed above.

In the example below, we clicked in cell B6 in the source worksheet and clicked Copy. Then, on the destination worksheet, we clicked in cell B3, and pasted the link. The value ($3,500) automatically displayed.

Notice that we set-up the destination worksheet ahead of time so we knew exactly where we wanted the Atlanta total to be located. We would follow the same steps to link the data from the Denver and Seattle worksheets to the Store Totals worksheet.

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 to a different folder or even a different PC should not be a problem. But if the source and destination workbooks are in different folders and if you move the 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 or directory is =[Filename.xls]Sheetname!CellReference. If you use the default file type in Excel 2007 and 2010, the extention would be xlsx.

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 a security warning that "Automatic update of links has been disabled" as shown in the image below. This often happens in the newer versions of Excel.

To bypass this warning, press the Options button in the upper right corner. Microsoft will display a Security Alert similar to the image below, warning that if you enable automatic update of links, your computer may no longer be secure.

Assuming there actually is no risk ... and to allow the linking process to proceed, click the radio button by "Enable this content" and click OK.

Disable Security Alerts When Linking Worksheets

If you do not want to go through this process each time you open a destination worksheet, disable the alert by following these steps:

• 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 the 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. For instructions, see "The Protection Tab" and "Protecting Worksheets and Workbooks" in Excel Made Easy. The link will take you directly to the appropriate sections of the tutorial.

We hope this article has been helpful. If you like video-based instruction, we recommend Excel 2010 Tutorial for Beginners. We've reviewed this course (over 9 hours of hands-on lessons), and find it well done. Cheers!

Back to Top