Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Uncovering and Removing Links.

Uncovering and Removing Links

by Allen Wyatt
(last updated July 6, 2013)

19

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 Links from the Edit menu, 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 that were 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. Choose Insert | Name | Define to display the proper dialog box. 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 toolbar buttons (older versions only) or to graphics in a worksheet. Click on any custom toolbar buttons or graphics and see if you get an error. If you do, this is a good indication that the button or graphic is linked to a macro contained in a different file. If you delete the button or 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 (1925) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Getting Rid of Section Breaks, but Not Section Formatting

Word allows you to change the character of how your pages are designed by using multiple sections in a document. If you want ...

Discover More

Rounding to the Nearest Half Dollar

When working with financial data, it's easy to round values to the nearest dollar. What if you want them rounded to the ...

Discover More

Specifying a Label Stock for Saved Documents

When you create a document designed to be printed on a particular type of label stock, it might be helpful if Word remembered ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Getting Rid of Workbook Links

Excel allows you to easily link information from one workbook to another. If you want to get rid of links that may be within ...

Discover More

Easily Changing Links

If you have linked information in your worksheets, you may want a way you can easily change the targets to which those links ...

Discover More

Updating Links

When you establish links between data on a target worksheet and data on a source worksheet, those links are typically updated ...

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 4 + 1?

2016-11-09 05:22:37

sachin dhavale

It was really useful.. the data validations were the culprit..

Many Thanks


2016-08-09 05:21:17

Paul

The Link was hidden in the validation rules of a sheet I had copied into my workbook. Very hard to spot this! I selected the copied sheet, cleared all validation rules, saved and quit the workbook. When I re-opened it the link was (finally) gone!
Many thanks for the detailed article and comments!
Paul


2016-07-24 06:03:49

Suryakant Randeri

Sir,
I have deleted one unwanted excel file that was previously down loaded. File Name" water97_V13.xla"
Now every time when I open any excel file the pop up inform that file " water97_97v13.xla" not available if have deleted or changed the location ..... etc. etc.
Its irritating to confirm Ok button every time you open any excel file in my compute.
Please advise how to get rid off this irritating pop up message

Regards

Suryakant


2016-04-22 02:28:56

Martin

Really useful site. Helped me solve a problem that was not in the Microsoft help. Thank you!


2016-04-01 23:44:19

Ashish

Thanks for the help. Defined names were the culprit in my case.


2016-02-01 23:06:34

Inesen

Managed to remove the links from Name Manager under Formulas tab. Thanks!


2016-01-14 11:35:22

Heyromey

To manually break links in Microsoft Excel 2013:
1. Go to Data >> Connections: Edit Links.
2. Take a screenshot of the links you want to break. Close the Edit Links dialog box.
3. Save a copy of your Excel file and close all Excel windows.
4. Rename the extension to .zip ("Excel Workbook.xlsx" will become "Excel Workbook.zip").
5. Open the zip file to explore the contents.
6. Go to xl >> worksheets. You should see an xml file for each of the sheets in your workbook.
7. Copy all the worksheets to a temporary folder on your hard drive.
8. Open the folder with windows explorer.
9. Press Ctrl + E and search for the file name you took a screenshot of in the Edit Links dialog box. You could also just search for “.xlsx”
10. Open the files that the string is found in. I’m using Notepad ++.
11. Search for the string from within the file editor.
12. Modify the filepath by adding a random letter in the middle of it.
13. Save and close the .xml file.
14. Copy and replace the file into the .zip folder.
15. Close the .zip application you’re using to view the files.
16. Rename the “.zip” file to “.xlsx”.
17. Open the file.
18. Excel will tell you about a problem it found and ask you if you want to recover it. Click Yes.
19. Excel will tell you about the repairs it made. Click Close.
20. Check your Data >> Connections: Edit Links.


2016-01-08 00:08:29

Cris Jumz

Break/Remove links in Conditional Formatting! Effective. Thanks for the idea..


2016-01-05 09:22:15

User

" Mohamed Abdel-Aziz 05 Jul 2015, 09:11
Also there is another place, if you made a drop down list from "data validation" you must check the links."

^^This was my ultimate problem. Looked everywhere. Thanks so much!!


2015-09-22 06:38:20

Martin

Brilliant - solved the problem.
I tried what Stein suggested first (the natural goto was just to delete the links) but it did not help in my case. Redirecting the links to the current workbook would not play so then I tackled the named ranges and cleared the problem. Thanks again Allen


2015-08-06 14:42:45

Stein

The best solution is not given here, suprisingly. In Excel 2010, just go to File/Info and then Edit Links. You can delete all links in a single move, which converts cell contents from formulas to values.


2015-07-05 09:11:21

Mohamed Abdel-Aziz

Also there is another place, if you made a drop down list from "data validation" you must check the links.


2015-06-13 03:06:38

Jose Lourenco

Great tip! I was struggling where the link to external file was. Had tried everything, except the range name definitions...BINGO
Thanks! Great stuff!


2015-05-01 05:31:00

Barry

Having tried to break a stubborn link (which turned out to be a Named range), in my research I also found that you can have links in Conditional Formatting.


2015-05-01 01:28:17

Peter

How do you keep the data connected to that link in your current workbook?


2015-03-15 18:09:58

Paul Mel

Appreciate this smart solution..


2014-05-13 08:24:13

Dory Gordon

Clear, fast and easy (as usual) - thanks, Allen! This is my go-to site for all Excel questions.


2013-12-18 04:21:54

Morten

Thanks, but what if the link is to itself (i.e. the current workbook)? How do I remove the warning?


2013-08-07 06:05:00

Nuno Costa

Problem solved!
Help much apreciatted!
Thank you very much!


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.