Break a link to an external reference

 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, if you break the link to the external reference =SUM([Budget.xls]Annual!C10:C25), it is 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.

The Connections group on the Data tab

 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.

ShowHow to delete a name

  1. On the Formulas tab, in the Defined Names group, click Name Manager.
  1. In the Name Manager dialog box, click the name that you want to change.
  2. Click the name to select it.
  3. Click Delete. You can also press DELETE.
  4. 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.

Applies to:
Excel 2010