How to Link Excel Spreadsheet Data

Excel provides the ability for one spreadsheet (or worksheet) to link to data in other spreadsheets. 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. We will use the terms worksheet and spreadsheet interchangeably.

Author: Keynote Support

If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We've have a copy of this course (over 9 hours of hands-on lessons) and find it very well done. And you can preview several chapters online!

Now - let's talk about linking worksheet data!

Overview

The linked spreadsheets can be in the same workbook or in different workbooks. The source spreadsheets provide the data, while a destination spreadsheet receives the data. When cell values change in the source spreadsheets, Excel automatically updates values in the destination spreadsheet or worksheet the next time it is opened. This is one of the many features that make the Excel software program so powerful.

Why Do We Link Spreadsheet Data?

The ability to link data between Excel spreadsheets and workbooks can often eliminate the need to have identical data entered and updated in multiple sheets. 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 a detailed spreadsheet for each salesperson, but would like a summary spreadsheet or worksheet that links to the individual salespeople's sheets, 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. In other words, if a cell in the destination spreadsheet will be filled with currency data, format that cell ahead of time with a data type of currency and set the decimal places and other specifications accordingly.

Linking the Spreadsheet Cells

1. In the source spreadsheet or 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 spreadsheet 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 in the spreadsheet, 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 Excel 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 into Spreadsheets

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 spreadsheet, 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 Microsoft 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 in Your Spreadsheets

When you have formulas in spreadsheets, 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 Excel tutorial has been helpful. If you like video-based instruction, we recommend Excel 2010 Tutorial for Beginners. We have a copy of this course (over 9 hours of hands-on lessons), and find it very well done. Cheers!

Return to the top of How to Link Excel Spreadsheet Data