Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Uncovering and Removing Links.

Uncovering and Removing Links

by Allen Wyatt
(last updated January 19, 2019)

4

It can be frustrating to open an Excel file and be continually asked if you want to update linked information, particularly if you are not sure what information is linked. If you want to get rid of links in a workbook, there are several things to try.

First, choose Edit Links on the Data tab of the ribbon, if the option is available. (It will only be available if Excel recognizes explicit links in the workbook.) From the resulting Links dialog box you cannot delete links, but you can change the links so that they point to the current workbook. When you later save and again open your workbook, Excel will recognize the self-referential links and delete them.

Another way you can find links is to search for either the left bracket ([) or right bracket (]) in your workbook. The brackets are used by Excel when putting together the links to other files. For instance, this is a link to an external file, as it would appear in a cell:

=[Book1.xls]Sheet1!$D$7

When you find links similar to the above, all you need to do is delete them. Make sure that you search each worksheet in your workbook.

Another place to look for links is in the defined range names maintained by Excel. This is a particularly common place for links if you are working with a workbook that contains worksheets copied or moved from other locations. The defined names, rather than pointing to a cell range in the current workbook, could be pointing to a range in a different workbook. Click the Name Manager tool on the Formulas tab of the ribbon, then step through each defined name, examining the address to which it refers. Delete or change any that refer to other workbooks.

Another place to check is your macros. It is possible to assign macros to graphics in a worksheet. Click on any graphics and see if you get an error. If you do, this is a good indication that the graphic is linked to a macro contained in a different file. If you delete the graphic, or change the macro assignment, the link problem should go away.

Still another possible location for wayward links is in PivotTables. When you create a PivotTable, it can refer to data on a different worksheet in your workbook. If you later move that source worksheet to a different workbook, your PivotTable will be linked to the external data source. The only solution here is to delete the PivotTable, copy the source data back to the current workbook, or move the PivotTable to the external workbook.

Finally, you should check graphs and charts. If you recently moved worksheets out of your current workbook into another workbook, it is possible that charts and graphs remaining in your current workbook now refer to data on a worksheet you moved to another workbook. If this is the case, you will need to either remove the graph or chart, move it to the other workbook, or copy the source data back into the current workbook.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11890) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Uncovering and Removing Links.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Nesting IF Worksheet Functions

The IF worksheet function is very handy to make conditional evaluations. You are not limited to a single IF comparison, ...

Discover More

Controlling the Printing of Highlighting

Using Word's built-in highlighter tool can be a great way to add markup to a document and attract a reader's eyes to ...

Discover More

Selecting Tabs in Dialog Boxes

Dialog boxes normally present information in a series of tabs. If you want to move from tab to tab without taking your ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Showing Text when a Cell is Empty

Wouldn't it be great if you could have Excel display some text in a cell only when that cell is empty? Unfortuantely, ...

Discover More

Capitalizing Just a Surname

Changing the capitalization of text is, believe it or not, a common task in Excel. Common or not, it can be frustrating ...

Discover More

Using an Input Mask

When you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 7?

2019-05-21 16:40:15

Dennis Costello

When dealing with complex workbooks, tracing the web of dependencies among cells is one of the most important, and possibly difficult things we have to do. To be clear, as Microsoft stated in https://support.office.com/en-ie/article/find-links-external-references-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1, a link is a reference from one Excel workbook to another - an "external reference". The article shows how they can be contained in formulas, defined names, objects (like text boxes or shapes), chart titles, and chart data series. As Pieter pointed out (and has driven me crazy any number of times) they can also be found in data validations.

Allen's Edit Links suggestion is a good way out of this mess, but only when the target workbook has the same worksheets/tabs as the current workbook. What Edit Links does is allow you to select one of the external workbooks, and then find and cycle through all the references in the current workbook to that external workbook, and replace them. It has 5 options:
- Update Values: Excel will reach into the external workbook and refresh the values in all formulas and other places that reference the selected external workbook: this makes sense only if the external workbook isn't already open as otherwise these values are made current with every recalc.
- Change Source: Changes the workbook referenced by the formulas, defined name definitions, etc. If, as Allen implied, you choose the current workbook to be the new target workbook, all of those links become simple ones (i.e., WorkSheet!Range without the [WorkBook] part). Otherwise it just swaps out the [WorkBook] part of the reference with one pointing to the new target workbook. Note that, either way, it does this only where it can: the new target workbook has to have a worksheet with the same name or the link wouldn't make any sense. If it's able to change all of the references to the selected external workbook, it will remove this link from the Edit Links box. If not, both the original and new target workbooks are still in the list of Links.
- Open Source: This just opens the external workbook
- Break Link: This replaces all the references to the external workbook with the cached corresponding values.
- Check Status: Will update the "Status" column in the selection box.

Here's the fun part: Change Source reliably changes references in formulas (where as I said above the new target has a WorkSheet with the same name), and is probably also reliable with defined names, objects, chart titles, and chart data series. But it definitely doesn't touch data validations. If you have any of those, the old external workbook name will stay in the Edit Links selection box after a successful Change Source, and you'll have to chase them down and "heal them" by hand.

This whole topic is made more confusing because "link" has two different meanings: it could either be an individual reference in one workbook to another (a formula, defined name definition, chart series definition, etc.) or a member of a list of external workbook names that Excel maintains as a property of the workbook. Edit Links, for instance, clearly populates its selection box by walking down the list of external workbooks in the current workbooks Links property. But it doesn't directly add or deleted items from that list: Excel will remove an item from that list only when it knows there are no more links to the external workbook, or add one when the first such reference is created. From VBA, you can walk down that list of property values and change one of them - but this triggers the same action as doing Change Source from the Edit Links dialog box, so it removes the old reference only if when it has cycled through all the current references it was able to update all of them.

By the way, it's tempting to think that the formulas used in Conditional Formats could cause the same problem as do Data Validations - but "You may not use references to other workbooks for Conditional Formatting criteria." So that's one place, at least, where you don't need to look for problems.


2019-01-21 03:30:19

Louise Race

I have found the following link really useful in finding and removing links to other Excel spreadsheets.

http://www.manville.org.uk/software/findlink.htm.

This add-in will find all links and either list or delete them for you.


2019-01-19 05:49:10

Alex B

With the use of Excel Tables becoming more common and hence the use of structured references, doing a find on square brackets gives too many false positives. You are better of searching for the "xls"


2019-01-19 05:17:02

Pieter de la Court

Thanks for this useful Excel tip! One more type of difficult to find links I come across sometimes is in cells with data validation. If you have cells that have data entry restriction set to "List" (referenced to a range in the worksheet) and you copy them from one workbook to another, the data validation still refers to the range in the original workbook. Remove or change this by removing or editing the data validation of the copied cells.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.