Linking is especially useful when it is not practical to keep large worksheet models together in the same workbook.
- Merge data from several workbooks You can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook. That way, when the source workbooks are changed, you won't have to manually change the summary workbook.
- Create different views of your data You can enter all of your data into one or more source workbooks, and then create a report workbook that contains links to only the pertinent data.
- Streamline large, complex models By breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets. Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.
What a link to another workbook looks like
Formulas with links to other workbooks are displayed in two ways, depending on whether the source workbook
When the source is open, the link includes the workbook name in square brackets, followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on. For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.
| Link |
|---|
| =SUM([Budget.xls]Annual!C10:C25) |
When the source is not open, the link includes the entire path.
| Link |
|---|
| =SUM('C:\Reports\[Budget.xls]Annual'!C10:C25) |
Note If the name of the other worksheet or workbook contains nonalphabetic characters, you must enclose the name (or the path) within single quotation marks.
Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!), and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xls.
| Link |
|---|
| =SUM(Budget!Sales) |
The difference between linking and embedding other programs in Excel
When you copy information between Microsoft Excel or any program that supports Object Linking and Embedding (OLE), such as Microsoft Word, you can copy the information as either a linked object or an embedded object.
Embedded object: No connection to the source file.
Linked object: Linked to the source file.
Source file: Updates the linked object.
When to use linked objects When you want the information in your destination file to be updated when the data in the source file changes, use linked objects.
With a linked object, the original information remains stored in the source file. The destination file displays a representation of the linked information but stores only the location of the original data (and the size if the object is an Excel chart object). The source file must remain available on your computer or network to maintain the link to the original data.
The linked information is updated automatically if you change the original data in the source file. For example, if you select a range of cells in an Excel workbook and then paste the cells as a linked object in a Word document, the information is updated in Word if you change the information in your workbook.
When to use embedded objects When you don't want to update the copied data when it changes in the source file, use an embedded object. The version of the source is embedded entirely in the workbook.
When someone opens the file on another computer, they can view the embedded object without having access to the original data. Because an embedded object has no links to the source file, the object is not updated if you change the original data. To change an embedded object, double-click the object to open and edit it in the source program. The source program (or another program capable of editing the object) must be installed on your computer. If you copy information as an embedded object, the destination file requires more disk space than if you link the information.
Controlling how links are updated
A linked object is updated automatically by default every time you open the destination file or any time the source file changes while the destination file is open. When you open the workbook, a startup prompt appears, asking if you want to update the links. This is the primary way to update links, although you can also manually update them. You can also control the response to the prompt, or whether to display it at all.
If you use a formula to link data from other programs, that data is updated automatically in Microsoft Excel whenever it is changed in the other program.
Links that use Dynamic Data Exchange (DDE)
Security Dynamic data exchange (DDE) is an older technology that is not secure. If possible, use a more secure alternative to DDE, such as object linking and embedding (OLE).
You can also create a link using Dynamic Data Exchange (DDE) if the program you are linking to supports it. To determine whether your program supports DDE or to learn the DDE name and topic information for the program, contact the software vendor of the program. For more information about DDE, see the Microsoft Office 2003 Resource Kit.
How to obtain the Office 2003 Resource Kit
The Office 2003 Resource Kit is the definitive guide to installing, configuring, and supporting Microsoft Office in your organization. Designed for system administrators, consultants, and power users, this guide offers complete coverage whether you're running Microsoft Office on Windows or the Macintosh.
You can obtain the Office 2003 Resource Kit wherever computer books are sold, or order direct from Microsoft Press, or online at the Office 2003 Resource Kit Web site.
To locate your nearest source for Microsoft Press products worldwide, visit the Microsoft Press Web site.