LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 - Working with external links

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

 

   Open or change source workbooks of external references (links)

   Create an external reference (link) to a cell  range in another workbook

   Control when external references (links) are updated

   Find external references (links) in a workbook

   Break a link to an external reference

   Create, edit, and control OLE objects

 

Open or change source workbooks of external references (links)

An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook. You create external references between workbooks to bring information from a source workbook into a destination workbook.

From a destination (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.) workbook, you can easily open the source (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.) workbook of an external reference. For example, you might want to verify that it is the workbook that you want the external reference to point to, or make changes to the source workbook. You can also easily change the source workbook in an external reference. For example, the source workbook may have been moved and you need to update the external reference to reflect the change.

What do you want to do?

Open a source workbook

Change the source workbook for all external references that use it

Change the source workbook of one external reference without affecting other external references

Open a source workbook

  1. Open the destination workbook.
  2. On the Data tab, in the Connections group, click Edit Links.

  1. In the Edit Links dialog box, click the source workbook that you want to open.
  2. Click Open Source.

 Top of Page

Change the source workbook for all external references that use it

  1. Open the destination workbook.
  2. On the Data tab, in the Connections group, click Edit Links.
  3. In the Edit Links dialog box, click the source workbook that you want to change.
  4. Click Change Source.
  5. In the Change Source dialog box, click the source workbook that you want to refer to. You may need to browse to find the workbook that you want.

 Top of Page

Change the source workbook of one external reference without affecting other external references

  1. Find the workbook that you want to use as the new source for the external reference, and note its location.
  2. In the destination workbook, select the cell with the external reference that you want to change.
  3. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , look for a reference to another workbook, such as C:\Reports\[Budget.xlsx], and replace that reference with the location of the new source workbook.

 Top of Page

 

See Also

 

 

Excel > Formula and name basics > Working with external links

Create an external reference (link) to a cell range in another workbook

Excel 2007

You can refer to the contents of cells in another workbook by creating an external reference. An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook. You can refer to the specific cell range, a defined name for the cell range, or define a name for the external reference.

What do you want to do?

Learn more about external references

Create an external reference between cells in different workbooks

Create an external reference to a defined name in another workbook

Define a name that contains an external reference to cells in another workbook

Learn more about external references

Although external references are similar to cell references, there are important differences. You use external references when working with large amounts of data or complex formulas that are spread across several workbooks. You create them in a different way and they appear differently in a cell or in the formula bar.

Where external references can be used effectively

External references are especially useful when it is not practical to keep large worksheet models together in the same workbook.

Ways to create external references

If you use a cell reference to create an external reference, you can then also apply formulas to the information. By switching between the various types of cell reference, you can also control which cells are linked to if you move the external reference. For example, if you use a relative reference, when you move the external reference, the cell or cells that it links to change to reflect its new position on the worksheet.

When you create an external reference from one workbook to another workbook, you should use a name to refer to the cells that you are linking to. You can create an external reference by using a name that is already defined, or you can define the name when you create the external reference. By using a name, it is easier to remember the contents of the cells that you are linking to. External references that use defined names do not change when you move them because a name refers to a specific cell or range of cells. If you want an external reference that uses a defined name to change when you move it, you can change the name that is used in the external reference, or you can change the cells that the name refers to.

What an external reference to another workbook looks like

Formulas with external references 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 external reference 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.

External reference

=SUM([Budget.xlsx]Annual!C10:C25)

When the source is not open, the external reference includes the entire path.

External reference

=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)

 Note    If the name of the other worksheet or workbook contains nonalphabetical 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.xlsx.

External reference

=SUM(Budget.xlsx!Sales)

 Top of Page

Create an external reference between cells in different workbooks

  1. Open the workbook that will contain the external reference (the destination (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.) workbook) and the workbook that contains the data that you want to link to (the source workbook).
  2. In the source workbook, click Save on the Quick Access Toolbar.
  3. Select the cell or cells in which you want to create the external reference.
  4. Type = (equal sign). If you want to perform calculations or functions on the external reference value, type the operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) or function that you want to precede the external reference.
  5. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link to.
  6. Select the cell or cells that you want to link to.
  7. Return to the destination workbook, and notice that Excel adds the reference to the source workbook and the cells that you selected in the previous step.
  8. Optionally, in the destination worksheet, edit or change the formula.
  9. Press CTRL+SHIFT+ENTER.

 Top of Page

Create an external reference to a defined name in another workbook

  1. Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
  2. In the source workbook, click Save on the Quick Access Toolbar.
  3. Select the cell or cells in which you want to create the external reference.
  4. Type = (equal sign). If you want to perform calculations or functions on the external reference's value, type the operator or function that you want to precede the external reference.
  5. On the View tab, in the Window group, click Switch Windows, click the source workbook, and then click the worksheet that contains the cells that you want to link to.

  1. Press F3, and then select the name that you want to link to.

 Top of Page

Define a name that contains an external reference to cells in another workbook

  1. Open the destination workbook and the source workbook.
  2. In the destination workbook, on the Formulas tab, in the Defined Names group, click Define Name.

  1. In the New Name dialog box, in the Name box, type a name for the range.
  2. In the Refers to box, delete the contents, and then keep the cursor in the box.

If the name contains a formula, enter the formula, and then position the cursor where you want the external reference. For example, type =SUM(), and then position the cursor between the parentheses.

  1. On the View tab, in the Window group, click Switch Windows, click the source workbook, and then click the worksheet that contains the cells that you want to link to.

  1. Select the cell or range of cells that you want to link to.
  2. In the New Name dialog box, click OK.

 Top of Page

 

See Also

 

 

Excel > Formula and name basics > Working with external links

Control when external references (links) are updated

Excel 2007

When you create external references (also called links), you can control whether the external references stay up to date, and when they are updated. If another user has changed a value in a cell, Microsoft Office Excel does not look for the new value unless you tell it to.

What do you want to do?

Learn more about external references

Control links to other workbooks

Manually update all of the links or none of the links in a workbook

Manually update only some of the links to other workbooks

Control the startup prompt for updating all of the links

Don't ask whether to update links when I open any workbook, and update links automatically

Don't ask to update links for this workbook, and let me control whether links are updated

Decide whether to update links when prompted at startup

If you don't remember creating links

If you know you are not connected to the source

If you want the data as it already exists, and don't want it replaced with new data

If you tried to update last time, and it took too long

If someone else created the workbook, and you don't know why you are seeing this prompt

If you answer the prompt the same way every time, and don't want to see it again

If you know the workbook Excel is trying to connect to is available

If you are using a parameter query

Learn more about external references

The workbook that contains the links is called the destination workbook (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.), and the workbook that is linked to it is called the source workbook (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.). If the workbook that you have opened (called a 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.)) contains links to other workbooks or files (called source files (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.)), and the source files have changed, then the links in workbook that you are opening may display information that is out of date.

The destination workbook contains the external reference.

The external reference (or link) is a reference to a cell range in the source workbook.

The source workbook contains the cell and the actual value that is retrieved and placed in the destination workbook.

 Top of Page

Control links to other workbooks

When the source workbook and the destination workbook are open on the same computer, links are updated automatically. When you open a destination workbook, and the source workbook is not open, you may be alerted by the Trust Bar whether to update the links. You can control whether the Trust Bar alerts you, and whether to update all links when the alert does not appear. You can also update only some of the links, if the workbook contains more than one link.

Manually update all of the links or none of the links in a workbook

  1. Close all workbooks. If one source workbook is open, and others are closed, the updates will not be uniform.
  2. Open the workbook that contains the links.
  3. To update the links, on the Trust Bar, click Options, and then click Enable this Content.

 Top of Page

Manually update only some of the links to other workbooks

  1. Close all workbooks.
  2. Open the workbook that contains the links.
  3. On the Data tab, in the Manage Connections group, click Edit Links.

  1. In the Source list, click the linked object that you want to update.

To select multiple links, hold down CTRL and click each linked object.

To select all links, press CTRL+A.

  1. Click Update Values.

 Top of Page

Control the startup prompt for updating all of the links

You can decide whether to update links automatically at startup or after startup.

Don't ask whether to update links when I open any workbook, and update links automatically

This option is for the current user only, and affects every workbook opened. Other users are not affected. This option also affects links to other programs.

  1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
  2. Under When calculating this workbook, clear the Ask to update automatic links check box. If this check box is clear, the links are automatically updated, and no prompt is displayed.

 Top of Page

Don't ask to update links for this workbook, and let me control whether links are updated

Warning  This option affects all users of the workbook. If you choose not to update links, and not to prompt, users of the workbook will not know that the data is out of date.

  1. On the Data tab, in the Connections group, click Edit Links.
  2. Click Startup Prompt.
  3. Select the option that you want.

 Note    Even if you specify that Excel should not ask whether to update links, you will still be notified if there are any broken links.

 Top of Page

Decide whether to update links when prompted at startup

There are a number of decisions you can make.

If you don't remember creating links

Click Don't Update. It is possible to accidentally create a link by moving or copying a range, worksheet, or chart between workbooks. Open the workbook, and then find the links that were created, and either break the link or replace it with a value.

Break a link to a source

Important When you break a link to a source, all formulas that use the source are converted to their current value. For example, the link =SUM([Budget.xls]Annual!C10:C25) would be converted to =45. Because this action cannot be undone, you may want to save a version of the file.

  1. On the Data tab, in the Connections group, click Edit Links.
  2. In the Source list, click the link that you want to break.

To select multiple linked objects, hold down CTRL and click each linked object.

To select all links, press CTRL+A.

  1. Click Break Link.
  2. If the link used 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.), the name is not automatically removed. You may want to delete the name as well.

How to delete a name

    1. On the Formulas tab, in the Named Cells group, click Name Manager.
    2. In the Name column, click the name that you want to delete, and then click Delete.
  1. If you use an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.), a parameter of a query may also use data from another workbook. You may want to check for and remove any of these type of links.

Replace a single formula with its calculated value

 Caution    When you replace a formula with its value, Excel permanently removes the formula. If you accidentally replace a formula with a value and you want to restore the formula, click Undo immediately after you enter or paste the value.

  1. Select the cell that contains the formula.

If the formula is an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), select the range that contains the array formula.

How to select a range that contains an array

    1. Click any cell in the array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) range.
    2. On the Home tab, in the Editing group, click Find & Select, and then click Go To Special.
    3. In the Go To Special dialog box, click Current Array.
  1. On the Home tab, in the Clipboard group, click Copy .
  2. Click Paste .
  3. Click the arrow next to Paste Options , and then click Values.

 Top of Page

If you know you are not connected to the source

Click Don't Update. Excel cannot update from a source that is not connected. For example, the source may be on a network, and you may not be connected to that network.

 Top of Page

If you want the data as it already exists, and don't want it replaced with new data

Click Don't Update.

 Top of Page

If you tried to update last time, and it took too long

Click Don't Update. If the data does not need to be the most current, you can save time by not updating all of the links. After opening the workbook, on the Data tab, in the Manage Connections group, click Edit Links, and then update the links only from the sources that you need.

 Top of Page

If someone else created the workbook, and you don't know why you are seeing this prompt

Click Update. Contact the owner of the workbook. You can also investigate what links are in the workbook. On the Data tab, in the Connections group, click Edit Links.

 Top of Page

If you answer the prompt the same way every time, and don't want to see it again

You can answer the startup prompt in a consistent way, and avoid seeing the startup prompt for this workbook.

Don't prompt for all workbooks I open, and update the links automatically  

This option is for the current user only, and affects every workbook opened. Other users of the workbook are not affected.

  1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
  2. Under General, clear the Ask to update automatic links check box. If this check box is cleared, the links are automatically updated, and no alert is displayed.

Prompt in the same way for every user of this workbook  

Warning  This option affects all users of the workbook. If you choose to not update links, and not to prompt, users of the workbook will not know that the data is out of date.

  1. On the Data tab, in the Connections group, click Edit Links.
  2. Click Startup Prompt.
  3. Select the option that you want.

 Note     You will still be notified if there are any broken links.

 Top of Page

If you know the workbook Excel is trying to connect to is available

Click Update. This ensures that you have the latest data.

 Top of Page

If you are using a parameter query

A link to a parameter query cannot be updated unless the source workbook is open.

  1. Click Don't Update.
  2. Close the destination workbook.
  3. Open the source workbook.
  4. Open the destination workbook.
  5. Click Update.

 Top of Page

 

 

Excel > Formula and name basics > Working with external links

Find external references (links) in a workbook

Excel 2007

There is no automatic way to find external references (also called links) that are used in a destination (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.) workbook. However, external references use brackets [ ] to enclose the source  (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.) workbook name when the workbook is closed, for example =SUM([Budget.xls]Annual!C10:C25), so you can use those characters to locate external references.

To find all external references in a destination workbook, you need to look in cells, names, objects (such as a text box or shape), chart titles, and chart data series.

What do you want to do?

Find external references that are used in cells

Find external references that are used in names

Find external references that are used in objects, such as a text box or shape

Find external references that are used in chart titles

Find external references that are used in chart data series

Find external references that are used in cells

  1. Close all source workbooks, and then open the destination workbook.
  2. On the Home tab, in the Editing group, click Find & Select, and then click Find.

The Find and Replace dialog box is displayed.

  1. Click Options.
  2. In the Find what box, enter [.
  3. In the Within box, click Workbook.
  4. In the Look in box, click Formulas.
  5. Click Find All.
  6. In the list box that is displayed, look in the Formula column for formulas that contain [.

Tip  Click the Formula column header to sort the column, and to group all of the external references together.

  1. To select the cell with an external reference, select the row in the list box.

 Top of Page

Find external references that are used in names

  1. Close all source workbooks, and then open the destination workbook.
  2. On the Formulas tab, in the Defined Names group, click Name Manager.
  3. Check each entry in the list, and look in the Refers To column for external references. External references contain a reference to another workbook, such as [Budget.xls].

Tip  Click the Refers To column header to sort the column and to group all the external references together.

 Top of Page

Find external references that are used in objects, such as a text box or shape

  1. Close all source workbooks, and then open the destination workbook.
  2. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

  1. Click Objects, and then click OK.
  2. Press the TAB key, and then look in the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) for a reference to another workbook, such as [Budget.xls]. The name of the object appears in the Name box.
  3. Repeat step 4 for each object in the worksheet.
  4. Repeat steps 2 through 5 for each worksheet in the workbook.

 Top of Page

Find external references that are used in chart titles

  1. Close all source workbooks, and then open the destination workbook.
  2. Click the chart title on the chart that you want to check.
  3. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , look for a reference to another workbook, such as [Budget.xls].
  4. Repeat steps 2 and 3 for each chart with a chart title in your workbook.

 Top of Page

Find external references that are used in chart data series

  1. Close all source workbooks, and then open the destination workbook.
  2. Select the chart that you want to check.
  3. On the Layout tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that you want to check.

  1. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , look for a reference to another workbook, such as [Budget.xls] in the SERIES function.
  2. Repeat step 3 for each data series in the chart.
  3. Repeat steps 2 through 5 for each chart in the workbook.

 Top of Page

 

 

Excel > Formula and name basics > Working with external links

Break a link to an external reference

Excel 2007

Important  When you break a link to the source workbook (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.) of an external reference, all formulas that that use the value in the source workbook are converted to their current values. For example, the external reference =SUM([Budget.xls]Annual!C10:C25) would be converted to =45. Because this action cannot be undone, you may want to save a version of the destination workbook (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.).

  1. On the Data tab, in the Connections group, click Edit Links.

 Note    The Edit Links command is unavailable if your file does not contain linked information.

  1. In the Source list, click the link that you want to break.

To select multiple linked objects, hold down CTRL, and then click each linked object.

To select all links, press CTRL+A.

  1. Click Break Link.
  2. If the link used 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.), the name is not automatically removed. You may want to delete the name as well.

How to delete a name

    1. On the Formulas tab, in the Defined Names group, click Name Manager.
    2. In the Name Manager dialog box, click the name that you want to change.
    3. Click the name to select it.
    4. Click Delete. You can also press DELETE.
    5. Click OK to confirm the deletion.

Tip  If you use an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.), a parameter of a query may also use data from another workbook. You may want to check for and remove any of these type of links.

 

 

Excel > Formula and name basics > Working with external links

Create, edit, and control OLE objects

Excel 2007

You can use 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.) to include content from other programs, such as Microsoft Office Word, in Microsoft Office Excel.

What do you want to do?

Learn more about OLE

Linked and embedded objects

When to use linked objects

When to use embedded objects

Changing the way that an OLE object is displayed

Link or embed content from another program by using OLE

Create a link to content from another program

Embed content from another program

Link or embed partial content from another program

Change the way that an OLE object is displayed

Control updates to linked objects

Set a link to another program to be updated manually

Set a link to another program to be updated automatically

Update a link to another program now

Edit content from an OLE program

Edit a linked object in the source program

Edit an embedded object in the source program

Edit an embedded object in a program other than the source program

Select an OLE object by using the keyboard

Issue: When I double-click a linked or embedded object, a "cannot edit" message appears

Learn more about OLE

OLE is supported by many different programs, and OLE is used to make content that is created in one program available in another program. For example, you can insert an Office Word document in an Office Excel workbook. To see what types of content that you can insert, click Object in the Text group on the Insert tab. Only programs that are installed on your computer and that support OLE objects appear in the Object type box.

 Top of Page

Linked and embedded objects

If you copy information between Excel or any program that supports OLE, such as 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.). The main differences between linked objects and embedded objects are where the data is stored and how the object is updated after you place it in 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.). Embedded objects are stored in the workbook that they are inserted in, and they are not updated. Linked objects remain as separate files, and they can be updated.

Linked and embedded objects in a document

An embedded object has no connection to the source file.

A linked object is linked to the source file.

The source file updates the linked object.

 Top of Page

When to use linked objects

If 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 can be updated automatically if you change the original data in the source file. For example, if you select a paragraph in a Word document and then paste the paragraph as a linked object in an Excel workbook, the information can be updated in Excel if you change the information in your Word document.

 Top of Page

When to use embedded objects

If 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. If you copy information as an embedded object, the destination file requires more disk space than if you link the information.

When a user opens the file on another computer, he 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.

 Top of Page

Changing the way that an OLE object is displayed

You can display 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 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.) in a workbook exactly as it appears in the source program (source program: The program used to create a linked object or embedded object. To edit the object, you must have the source program installed on your computer.) or as an icon. If the workbook will be viewed online, and you don't intend to print the workbook, you can display the object as an icon. This minimizes the amount of display space that the object occupies. Viewers who want to display the information can double-click the icon.

 Top of Page

Link or embed content from another program by using OLE

You can link or embed all or part of the content from another program.

Create a link to content from another program

  1. Click in the worksheet where you want to place the 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.).
  2. On the Insert tab, in the Text group, click Object.

  1. Click the Create from File tab.
  2. In the File name box, type the name of the file, or click Browse to select from a list.
  3. Select the Link to file check box.
  4. Do one of the following:
    • To display the content, clear the Display as icon check box.
    • To display an icon, select the Display as icon check box.

To change the default icon image or label, click Change Icon, and then click the icon that you want from the Icon list, or type a label in the Caption box.

 Note    You cannot use the Object command to insert graphics and certain types of files. To insert a graphic or file, on the Insert tab, in the Illustrations group, click Picture.

 Top of Page

Embed content from another program

  1. Click in the worksheet where you want to place the 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.).
  2. On the Insert tab, in the Text group, click Object.

  1. If the document does not already exist, click the Create New tab. In the Object type box, click the type of object that you want to create.

If the document already exists, click the Create from File tab. In the File name box, type the name of the file, or click Browse to select from a list.

  1. Clear the Link to file check box.
  2. Do one of the following:
    • To display the content, clear the Display as icon check box.
    • To display an icon, select the Display as icon check box.To change the default icon image or label, click Change Icon, and then click the icon that you want from the Icon list, or type a label in the Caption box.

 Top of Page

Link or embed partial content from another program

  1. From a program other than Excel, select the information that you want to copy as a linked (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 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.).
  2. On the Home tab, in the Clipboard group, click Copy.

  1. Switch to the worksheet that you want to place the information in, and then click where you want the information to appear.
  2. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
  3. Do one of the following:
    • To paste the information as a linked object, click Paste link.
    • To paste the information as an embedded object, click Paste. In the As box, click the entry with the word "object" in its name. For example, if you copied the information from a Word document, click Microsoft Word Document Object.

 Top of Page

Change the way that an OLE object is displayed

  1. Right-click the icon or object, point to object type Object (for example, Document Object), and then click Convert.
  2. Do one of the following:
    • To display the content, clear the Display as icon check box.
    • To display an icon, select the Display as icon check box.

To change the default icon image or label, click Change Icon, and then click the icon that you want from the Icon list, or type a label in the Caption box.

 Top of Page

Control updates to linked objects

You can set links to other programs to be updated in the following ways: automatically, when 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.); manually, when you want to see the previous data before updating with the new data from 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.); or when you specifically request the update, regardless of whether automatic or manual updating is turned on.

Set a link to another program to be updated manually

  1. On the Data tab, in the Connections group, click Edit Links.

 Note    The Edit Links command is unavailable if your file does not contain links to other files.

  1. In the Source list, click the linked object that you want to update. An A in the Update column means that the link is automatic, and an M in the Update column means that the link is set to Manual update.

Tip  To select multiple linked objects, hold down CTRL and click each linked object. To select all linked objects, press CTRL+A.

  1. To update a linked object only when you click Update Values, click Manual.

 Top of Page

Set a link to another program to be updated automatically

  1. On the Data tab, in the Connections group, click Edit Links.

 Note    The Edit Links command is unavailable if your file does not contain links to other files.

  1. In the Source list, click the linked object that you want to update. An A in the Update column means that the link will update automatically, and an M in the Update column means that the link must be updated manually.

Tip  To select multiple linked objects, hold down CTRL and click each linked object. To select all linked objects, press CTRL+A.

  1. Click OK.

Issue: I can't update the automatic links on my worksheet

  1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
  2. Under When calculating this workbook, make sure that the Update links to other documents check box is selected.

 Top of Page

Update a link to another program now

  1. On the Data tab, in the Connections group, click Edit Links.

 Note    The Edit Links command is unavailable if your file does not contain linked information.

  1. In the Source list, click the linked object that you want to update.

Tip  To select multiple linked objects, hold down CTRL and click each linked object. To select all linked objects, press CTRL+A.

  1. Click Update Values.

 Top of Page

Edit content from an OLE program

While you are in Excel, you can change the content linked or embedded from another program.

Edit a linked object in the source program

  1. On the Data tab, in the Connections group, click Edit Links.

 Note    The Edit Links command is unavailable if your file does not contain linked information.

  1. In the Source file list, click the source for the 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.), and then click Open Source.
  2. Make the changes that you want to the linked object.
  3. Exit the source program (source program: The program used to create a linked object or embedded object. To edit the object, you must have the source program installed on your computer.) to return to the destination file.

 Top of Page

Edit an embedded object in the source program

  1. Double-click the 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.) to open it.
  2. Make the changes that you want to the object.
  3. If you are editing the object in place within the open program, click anywhere outside of the object to return to 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.).

If you edit the embedded object in the source program in a separate window, exit the source program to return to the destination file.

 Note     Double-clicking certain embedded objects, such as video and sound clips, plays the object instead of opening a program. To edit one of these embedded objects, right-click the icon or object, point to object type Object (for example, Media Clip Object), and then click Edit.

 Top of Page

Edit an embedded object in a program other than the source program

  1. Select the 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.) that you want to edit.
  2. Right-click the icon or object, point to object type Object (for example, Document Object), and then click Convert.
  3. Do one of the following:
    • To convert the embedded object to the type that you specify in the list, click Convert to.
    • To open the embedded object as the type that you specify in the list without changing the embedded object type, click Activate.

 Top of Page

Select an OLE object by using the keyboard

  1. Press CTRL+G to display the Go To dialog box.
  2. Click Special, select Objects, and then click OK.
  3. Press TAB until the object that you want is selected.
  4. Press SHIFT+F10.
  5. Point to Object or Chart Object, and then click Edit.

 Top of Page

Issue: When I double-click a linked or embedded object, a "cannot edit" message appears

This message appears when 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.) or source program (source program: The program used to create a linked object or embedded object. To edit the object, you must have the source program installed on your computer.) can't be opened.

Make sure that the source program is available   If the source program is not installed on your computer, convert the object to the file format of a program that you do have installed.

Ensure that memory is adequate   Make sure that you have enough memory to run the source program. Close other programs to free up memory, if necessary.

Close all dialog boxes   If the source program is running, make sure that it doesn't have any open dialog boxes. Switch to the source program, and close any open dialog boxes.

Close the source file   If the source file is a linked object, make sure that another user doesn't have it open.

Ensure that the source file name has not changed   If the source file that you want to edit is a linked object, make sure that it has the same name as it did when you created the link and that it has not been moved. Select the linked object, and then click the Edit Links command in the Connections group on the Data tab to see the name of the source file. If the source file has been renamed or moved, use the Change Source button in the Edit Links dialog box to locate the source file and reconnect the link.

 

                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us