About linking to another workbook or program

A link is a reference to another workbook, and is sometimes called an external reference. A link can also be made to another program, and this link is sometimes called a remote reference. Because data in another workbook or program may change and outdate the data in your workbook, there are options for controlling the updating of links.

ShowWhere links can be used effectively

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.

ShowWhat a link to another workbook looks like

Formulas with links to other workbooks are displayed in two ways, depending on whether the source workbook — the one workbook that supplies data to a formula — is open or closed.

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 (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) 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)

ShowThe 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) (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.), such as Microsoft Word, you can copy the information as either a linked object (linked object: An object that is created in a source file and inserted into a destination file, while maintaining a connection between the two files. The linked object in the destination file can be updated when the source file is updated.) or an embedded object (embedded object: Information (object) contained in a source file and inserted into a destination file. Once embedded, the object becomes part of the destination file. Changes you make to the embedded object are reflected in the destination file.).

Linked and embedded objects in a document

Callout 1 Embedded object: No connection to the source file.

Callout 2 Linked object: Linked to the source file.

Callout 3 Source file: Updates the linked object.

When to use linked objects     When you want the information in your destination file (destination file: The file that a linked or embedded object is inserted into. The source file contains the information that is used to create the object. When you change information in a destination file, the information is not updated in the source file.) to be updated when the data in the source file (source file: The file that contains information that was used to create a linked or embedded object. When you update the information in the source file, you can also update the linked object in the destination 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.

ShowControlling how links are updated

A linked object (linked object: An object that is created in a source file and inserted into a destination file, while maintaining a connection between the two files. The linked object in the destination file can be updated when the source file is updated.) is updated automatically by default every time you open the destination file (destination file: The file that a linked or embedded object is inserted into. The source file contains the information that is used to create the object. When you change information in a destination file, the information is not updated in the source file.) or any time the source file (source file: The file that contains information that was used to create a linked or embedded object. When you update the information in the source file, you can also update the linked object in the destination 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.

ShowLinks 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) (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.) 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 Editions Resource Kit.

ShowHow to obtain the Office 2003 Editions Resource Kit

The Office 2003 Editions 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 Editions Resource Kit wherever computer books are sold, or order direct from Microsoft Press, or online at the Office 2003 Editions Resource Kit Web site.

To locate your nearest source for Microsoft Press products worldwide, visit the Microsoft Press Web site.

 
 
Applies to:
Excel 2003