How to Link Excel Spreadsheet Data
Microsoft Excel provides the ability for one worksheet to link to data in another worksheet. As you will see in a moment, this is a great productivity tool.
In this tutorial, you will learn how to link worksheet data in all versions of Excel. You will also learn hints and tips on how to work with this cool feature. We will use the terms worksheet and spreadsheet interchangeably.
If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We've have this course (over 9 hours of lessons) and find it very well done. Some of the chapters are available for preview online. Now - let's talk about linking worksheet data!
Linking Excel Worksheet Data Overview
In Excel, a link is a formula that dynamically pulls in data from a cell in another worksheet. The worksheet can be in the same workbook or a different workbook.
The destination worksheet contains the link formula, and it receives data from a cell in the source worksheet.
Any time the cell value in the source worksheet changes, the cell containing the link formula will be updated as well the next time it is opened. This is just one of many reasons the Excel software program is so powerful.
Why Link Spreadsheet Data?
The ability to create links often eliminates the need to have identical data entered and updated in multiple sheets. This saves time, reduces errors, and improves data integrity. 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.
Consider a Sales Manager who has a detailed spreadsheet for each salesperson, but would like a summary sheet to compare salespersons' performance and create grand totals. The summary sheet (destination) would bring in data from all the salespersons' sheets (source).
Now let's learn how to link some Excel worksheets together!
How to Create the Worksheet Link
Tip: Before creating the link, format the cell containing the link formula in the destination worksheet to equal the format of the source data.
- In the source worksheet, select the cell you want to link to and click the Copy button on the Home tab. Or press Ctrl+C, or right-click and select Copy.
- Switch to the destination spreadsheet and click the cell where you want the link. Then, depending on your version of Excel:
- Excel 2007, 2010, and 2013: On the Home tab, click the down arrow below Paste and click Paste Link. In newer versions you may also right-click and select the Paste Link from the Paste menu.
- Excel 2003 and older versions: On the Edit menu, click Paste Special, and then click Paste Link.
- Return to the source worksheet and press ESC to remove the animated border around the cell.
This is a fast method that works in a different order than Method One.
- In the destination worksheet cell that will contain the link formula, enter an equal sign (=).
- In the source worksheet, click in the cell that contains the data and press the Enter key.
Linking a Range of Cells
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 Link as directed above.
Link Formula Example
In the example below, using Method One, we click in cell B6 in the source worksheet and click Copy. Then, on the destination worksheet, we click in cell B3, and paste the link. The value ($3,500) automatically displays.
We follow the same steps to link the data from the Denver and Seattle worksheets to the Store Totals worksheet. And we first formatted the cells to display the data as Currency.
Manually Entering Link Formulas into Spreadsheets
When linking cells in worksheets that are in the same workbook, you can easily enter the formula manually. The link formula, after the equal sign (=), contains the sheet name followed by an exclamation mark (!), and then the cell reference or address: =SheetName!CellReference. The formula in our example is =Atlanta!B6.
If the worksheets are in different workbooks in the same folder (directory), the formula also contains the workbook name in brackets. The formula is: =[Filename.xlsx]Sheetname!CellReference. If you use the default file extension in Excel 2007, 2010, and 2013, the extension will be xlsx. Older version use a file extension of xls.
If the workbooks are in different folders, the formula would have to include the complete file path as well, so it is best to avoid manual formulas in this situation.
Recommendations When Creating Worksheet Links
Before creating your links, consider 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 computer should not be a problem. But if you move either the source or destination workbook to a different folder, the link will break!
Also, companies that store worksheets on network servers have additional factors to consider that are outside the scope of this tutorial.
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. Directions for locking cells and protecting worksheets are provided in our tutorial Formatting Cells in Excel
Notate Link Locations in Worksheets
There is no method in Excel to see which cells contain links, so notate in some manner the link locations. One method is to format the cells containing links with a particular fill color. Another way is to document somewhere on the spreadsheet which cells contain links.
Ensure Automatic Calculation is Turned On
Most Excel users want the link formula to automatically update when the source is updated. To ensure that this will happen, follow the following steps depending on your version of Excel:
In Excel 2007, 2010, and 2013:
- Click the Excel button in the upper left corner.
- Click the Excel Options button at the bottom of the window.
- Click Formulas on the left sidebar menu.
- In the "Calculation Options" section, make sure "Automatically" is checked.
In versions prior to Excel 2007:
- From the top menu line, click Tools.
- Click Options.
- Click the Calculation tab and click Automatic under Calculation.
Avoid Circular Links
A circular link is a condition where a link on Worksheet A references source data on Worksheet B, and a link on Worksheet B references source data on Worksheet A. This is not prohibited, but can make both worksheets very slow to open and update, so Microsoft recommends against it.
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"
We hope this Excel tutorial from Keynote Support has been helpful. PS: Don't miss the deep discounts in electronics Amazon's having through December 22nd. Happy holidays!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-basics/excel-linking-worksheets.shtml">How to Link Excel Spreadsheet Data</a>
- » Excel Made Easy - A Beginner's Guide
- » Formatting Cells in Microsoft Excel
- » Excel Math Basics: Writing Formulas and Expressions
- » Cell References in Microsoft Excel
- » Linking Worksheet Data in Excel
- » Microsoft Excel: Cool Keyboard Shortcuts
- » Using the Auto Fill Features of Excel
- » How to Create an Excel Chart
- » Chart Types: Pie, Column, Line, Bar, Area, Scatter
- » Customizing Charts: A Comprehensive Guide
Search Keynote Support