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.
Written by Allen Wyatt (last updated October 17, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
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!
Need to get rid of the links in your workbook but save the values that were retrieved by those links? It could be easy or ...
Discover MoreIf you have linked information in your worksheets, you may want a way you can easily change the targets to which those ...
Discover MoreWhen you establish links between data on a target worksheet and data on a source worksheet, those links are typically ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-10-17 09:19:52
Charles Romer
No mention of dellinks?
--------------------------------------------------
MICROSOFT(R) EXCEL 97 DELETE LINKS WIZARD
June 1998
---------------------------------------------------
(c) Microsoft Corporation, 1998
The Delete Links Wizard is an add-in file for Microsoft Excel 97
that you can use to delete links to external files in one or more
workbooks. Currently the only way to perform this task is to
manually delete the links or to use a Microsoft Visual Basic for
Applications (VBA) macro to search, find, and delete the links.
These methods may require a significant amount of time if you want
to remove all the links to external files. Using the Delete Links
Wizard is more efficient than using a manual or running a macro
because the wizard contains options that make deleting multiple
links easier. These options allow you to easily delete of any one
of six kinds of links.
Used last with Excel 2000 in 2019
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments