Microsoft Excel provides the ability for cells in one worksheet to be linked to cells in other worksheets in the same or different workbook.
In this tutorial, you'll learn how to link worksheet data in all versions of Excel. It's easy, and we'll show you several different ways to do it!
○ This tutorial contains links to products some readers may find helpful. As an affiliate partner, we may earn from qualifying purchases at no extra cost to you. See our disclosure policy to learn more. ○
Though we provide links to each section of this tutorial, we recommend it be read from start to finish.
Creating links between worksheets can be a huge productivity boost. Using external cell references in a worksheet can cut down on entering the same data in multiple worksheets.
For example, a Sales Manager can have a detailed sheet for each sales representative, and a summary sheet to compare team performance. Prices can be stored in a master sheet to which other worksheets link.
Creating links between multiple sheets greatly helps with data integrity. If prices are kept in one worksheet and others link to it, there is much less chance of data entry errors.
In addition, multiple Excel users aren't having to enter the same data, so it saves time and increases employee productivity.
First we'll show you exactly how linking works, then we'll show you, step-by-step, how to link worksheet data. You'll be a pro in no time!
When we link data, we tell Excel to display data from another worksheet. The worksheet that has the data that we want copied to our worksheet is called the source worksheet.
The worksheet that we're currently working in that wants the data is called the destination worksheet.
And the method in which we tell Excel to go to another worksheet and get the data is called a link formula. We show you three different ways of creating link formulas.
The link formula syntax is =SheetName ! CellReference where SheetName is the name of the source worksheet, and CellReference is the cell from the source worksheet that contains the data.
In the example below, we entered =Toronto!B6 in the destination sheet to display the units sold from the source worksheet Toronto. Study the image below of the two spreadsheets.
These two worksheets are in the same Excel workbook. We can see the worksheet names, Toronto and Summary, on the tabs at the bottom of the worksheet. Worksheet names can be edited.
Any time the data from the source worksheet changes, the destination worksheet will show the new data the next time it is opened.
We'll look at three methods for creating a link formula. These instructions assume that the source and destination worksheets are in the same workbook. If not, go to Linking to Worksheets in Different Workbooks below.
Also, make sure that you always format the cell that will contain the link formula before entering the formula.
In some ways, we prefer the manual way of entering the link formula we discussed above (Method 3) because we don't like to jump from worksheet to worksheet. But try each method to see which one you like the best.
This method begins in the destination worksheet. Format the cell that will contain the link formula, and keeping the mouse cursor in that cell, follow the directions below.
If done correctly, Excel returns to the destination sheet and displays the linked data.
With Method Two, we begin in the source and end in the destination sheet. We'll copy from the cell in the source worksheet and Paste Link in the destination sheet. Follow these steps.
The cell in the destination worksheet that contains the formula will show the copied and linked data, and the link formula displays in the formula bar (figure 4).
With these first two methods, Excel creates the formula =Paris!$B$6 and wraps the source cell with dollar signs (absolute cell reference).
* Copy methods: 1) keyboard shortcut Ctrl + C , 2) right-click menu, 3) copy button on the ribbon's Home tab.
Entering formulas manually is not difficult . see Link Formula Syntax above. Without any spaces in between, type equal sign, sheet name, exclamation mark, cell reference : =SheetName ! CellReference .
If you have complex or multiple formulas to enter, open a Notepad file, create the formulas, copy and paste them into the destination cell, and modify as needed.
Don't worry about capitalizing worksheet names. If the first letter of the worksheet name is capitalized and you enter the name in the formula without the capital letter, Excel will add it. And vice versa. See Naming and Renaming Worksheets.
In linking formulas, spaces or special characters must be wrapped in single quotes. If the worksheet name is Retail Prices, then a linking formula, manually created or generated by Excel, look like the following: ='Retail Prices'!B6 . Therefore, life is easier if worksheet names, on the tabs at the bottom of the workbook, are one word long.
Because of internal workings, Excel will automatically wraps worksheet names C or R in single quotes.
To link a range of cells, select the cells and click Copy. In the destination sheet, click the cell where the upper-left cell of the range should be located and click Paste ⇒ Paste Link on the Home tab.
Cell values in the range will be linked, and will display on the destination sheet. Each cell in the range will contain its own link formula which references the corresponding cell in the source worksheet.
If the worksheets are in different workbooks in the same folder or directory, the formula must also include the workbook name in brackets as shown.
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 this situation. Don't forget that versions older than Excel 2007 have a file extension of xls.
If the source and destination worksheets are in the same workbook, moving the workbook to a different folder or even a different computer should not be a problem.
However, if one of the worksheets is moved to a different workbook, the link will break. If the worksheets are in different workbooks and one or both are moved, results are unpredictable.
Companies that store shared worksheets on network servers have additional factors to consider that are outside the scope of this tutorial.
The next time you open the destination worksheet, you will undoubtedly see one of the following security alerts, warning you that Automatic update of links has been disabled.
Procedures for responding to this message and a subsequent security warning differ depending on your version of Excel.
For detailed information and the procedure for permanently disabling these security warnings, please see Automatic Updates When Linking Data and Charts.
Linking worksheet data in Excel is a great feature to know and use. Try the three different methods and choose the one that works best for you.
Pat Kearns, founder and author of the Keynote Support website, is a 19-year veteran of IBM, and brings decades of experience in Excel, accounting, and (Read more)
