When data is linked between two worksheets what happens when the data is changed in one of the linked cells?

Microsoft Excel provides the ability for cells in one worksheet to be linked to cells in other worksheets in the same or different workbook.

Inhaltsverzeichnis Show

✦ Link Worksheet Data - Method One ✦
✦ Manually Write Formulas - Method 3 ✦
Linking to Worksheets in Different Workbooks
Automatically data in another sheet in Excel
Two methods of linking data in different worksheets
Update cell on one worksheet based on a cell on another sheet
Update cell on one sheet only if the first sheet meets a condition
Update cell on one sheet from another sheet with a drop-down list

In this tutorial, you'll learn how to link worksheet data in all versions of Excel. For information about our related tutorials, see Linking Worksheet Data Tutorials.

○ This tutorial contains links to products some readers may find helpful. As an Amazon affiliate partner we may earn from qualifying purchases. See our disclosure policy to learn more. ○

Creating links, or external cell references, can eliminate having the same data maintained in multiple sheets. This saves time, reduces errors, and improves data integrity. A Sales Manager can have a detailed sheet for each rep, and a summary sheet to compare performance. Prices can be stored in a master sheet to which other worksheets link.

When we link data, we tell Excel to display data from another worksheet. The worksheet that has the data is the source worksheet and the sheet that wants the data is the destination worksheet. We tell Excel what data to bring forward with a link formula.

The link formula syntax is =SheetName!CellReference and specifies the worksheet and cell. In the example below, we entered =Toronto!B6 in the destination sheet to display the units sold from the Toronto spreadsheet.

Any time the source data changes, the destination worksheet will show the new data the next time it is opened.

A KeynoteSupport.com Tutorial

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 section 5. And always format the cell which will contain the link formula before beginning.

✦ Link Worksheet Data - Method One ✦

Start in the destination sheet and end in the source worksheet.

In the destination worksheet, click in the cell that will contain the link formula and
type an equal sign
, but do NOT press Enter (figure 1).
In the source worksheet, click in the cell with the data to link (figure 2) and press Enter. Excel returns to the destination sheet and displays the linked data. Excel creates a link formula with relative cell references:
=Paris!B6
.

Copy from the source worksheet and Paste Link in the destination sheet.

In the source worksheet, copy* the data to be linked. As in the example above, we are bringing in the value of cell B6 from the Paris worksheet.
In the destination sheet, click the cell where you want the link formula, and click
Paste
⇒
Paste Link
on the Home tab - see figure 3. (Old versions of Excel: Edit ⇒ Paste Special ⇒ Paste Link)
The destination worksheet displays the formula value, and the link formula displays in the formula bar (figure 4). With this method, Excel creates the formula with an absolute cell reference:
=Paris!$B$6
.
Return to the source worksheet and press
ESC
key to remove the animated border.

* Copy methods: 1) keyboard shortcut Ctrl + C, 2) right-click menu, 3) copy button on the ribbon's Home tab.

✦ Manually Write Formulas - Method 3 ✦

affiliate link

Entering formulas manually is not difficult: equal sign, sheet name, exclamation mark, and cell reference: =SheetName!CellReference. If you have multiple formulas to enter, open a Notepad file, type the formula, and copy, paste, and modify.

Don't worry about capitalizing worksheet names. If 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; e.g. ='Retail Prices'!B6. (Excel 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.

Linking to Worksheets in Different Workbooks

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.

=[BookName.xlsx]SheetName!CellReference

=[BookName.xlsx]SheetName!
CellReference

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.

A KeynoteSupport.com Tutorial

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. But if either worksheet is moved 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.

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.

↑ Return to the top

Automatically data in another sheet in Excel

We can link worksheets and update data automatically. A link is a dynamic formula that pulls data from a cell of one worksheet and automatically updates that data to another worksheet. These linking worksheets can be in the same workbook or in another workbook.

One worksheet is called the source worksheet, from where this link pulls the data automatically, and the other worksheet is called the destination worksheet that contains that link formula and where data is updated automatically.

Remember one thing that formatting of cells of source worksheet and destination worksheet should be the same otherwise the result could be viewed differently and can lead to confusion.

Two methods of linking data in different worksheets

We can link these two worksheets using two different methods.

Copy and Paste Link
- From source worksheet, select the cell that contains data or that you want to link to another worksheet, and copy it by pressing the Copy button from the Home tab or press CTRL+C.
- Go to the destination worksheet and click the cell where you want to link the cell from the source worksheet. On the Home tab, click on the drop-down arrow button of Paste, and select Paste Link from “Other Paste Options.” Or right-click in the cell on the destination worksheet and choose Paste Link from Paste Options.
- Save the work or return to the source workbook and press ESC button on the keyboard to remove the border around the copied cell and save the work.
Enter formula manually
- In the destination worksheet, click on the cell that will contain link formula and enter an equal sign (=)
- Go to the source sheet and click on the cell that contains data and press Enter on the keyboard. Save your work.

Using these two methods, we can link a worksheet and update data automatically depending upon your requirements. In this article, we will discuss some examples using the following cases.

Update cell on one worksheet based on a cell on another sheet

Suppose we have a value of 200 in cell A1 on Sheet1 and want to update cell A1 on Sheet2 using the linking formula. We can do that by using the same two methods we’ve covered.

Using Copy and Paste Link method

Copy the cell value of 200 from cell A1 on Sheet1.

Go to Sheet2, click in cell A1 and click on the drop-down arrow of Paste button on the Home tab and select Paste Link button. It will generate a link by automatically entering the formula =Sheet1!A1.

Or right-click in the cell on the destination worksheet, Sheet2, and choose Paste Link from Paste Options: It will generate linking formula automatically.

Entering formula manually

We can enter the linking formula manually in cell A1 on the destination worksheet Sheet2 to update data by pulling it from cell A1 of Sheet1.

In cell A1 on Sheet2, manually enter an equal sign (=) and go to Sheet1 and click on cell A1 and press ENTER key on your keyboard. The following linking formula will be updated in destination sheet that will link cell A1 of both sheets.
=Sheet1!A1

Update cell on one sheet only if the first sheet meets a condition

By entering the linking formula manually, we can update data in cell A1 of Sheet2 based on a condition if the cell value of A1 on Sheet1 is greater than 200. We can do that by entering this logical condition in an IF function. If cell A1 on Sheet1 meets this condition then IF function returns the value in cell A1 on Sheet2 otherwise it will return blank cell.

Here is the formula to link the cells of both sheets based on this condition. We will enter this formula manually in cell A1 of Sheet2
=IF(Sheet1!A1>200,Sheet1!A1,"")

Update cell on one sheet from another sheet with a drop-down list

Suppose we have a drop-down list in cell A1 of Sheet1 and we can update cell A1 on Sheet2 by entering link formula in cell A1 on Sheet2.

In cell A1 on Sheet2, we will manually enter this linking formula to update data automatically based on the cell value selected from the drop-down list.
=Sheet1!A1

Linking data in a real data set is more complex and depends on your situation. You might need to use techniques other than those listed above. If you are in a rush and want your problem answered by an Excel expert, try our service. The experts are available to help you 24/7. The first question is free.