Linking Data in Microsoft Excel

Excel is a powerful tool with hundreds of features and variations, making Microsoft Excel Training a must for anyone who wants to use it to its fullest. One of the neat features is the ability to “link” data. For example, if you want to base a worksheet off of data from another worksheet or even from data in an external workbook, you can do so. While that's helpful, the real power comes when Excel automatically updates the new worksheet whenever you make changes to the original data. This means that you can change data at the source and the sheets that reference this data change too.

Let's take a look at this concept in action. Let's say we have a spreadsheet with two worksheets: a price list and a detailed product listing. The price list names the product and the price whereas the detailed product listing contains other details such as a description of the item, the price, and who manufactures it. Both pages have fields for the price so if the price is changed in one worksheet, it makes sense that the second worksheet should also be updated for accuracy. By linking the price field, this can happen automatically.

To do this, copy the price from the first worksheet and paste it into the second worksheet using the Paste Special command. To get to the Paste Special dialog, right-click the destination cell and choose it from the submenu. Click the Paste Link button. Now, anytime you make a change to the product's price in the price list, the price will change automatically in the product description worksheet.

Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation

It's important to note that this is one-way linking. Changes made in the product description worksheet will not be updated at the source. To prevent problems, protect the destination worksheet.