Finding and Deleting Hidden Links in Excel
Posted by TJ on Friday March 25, 2011 @ 06:44 PM
[Tags: guide, excel, accounting]

One my excel nuisances is spreadsheets that link to other external documents. If you plan on sending a file out, I feel that external links, should be avoided at all costs in the professional environment. External links may seem to work fine when the files are all your computer but once you give that file to another party it creates issues because excel can no longer find the file. Also changing the source document after you link to it also can create more issues.

So having said that, here are useful tips for finding and removing external links.
1) The easiest way to remove links in excel is using the "Edit Link" option on the Data ribbon. Here excel will list all the external documents that a spreadsheet is linking to. You can select each file listed then click "break links" and excel will replace the formulas that contain external links with values. The problem with this feature is you will not always know which formulas you are editing and the feature does not work on some links.

2) If you want to view each external link before editing it another option is to search for all external links. This can be achieved by using the "Find and Select" option on the Home ribbon. If you click the "option" button you can select to search the worksheet or the whole workbook. Now in the "find what" box type "[" or ".xls" without quotes.

3) Now if you tried the above and you still have external links this may be due to defined names that exist in workbook. You can see if you have defined names by clicking the "Name Manager" on the Formulas ribbon. Delete any names here that have errors or that are linking to external sources (make sure you correct any of the formulas using these names first, of course).

4) If you still can't find a link there is a handy tool created by Bill Manville that will find all hidden links and give you the option to delete each. You can download the file here: http://www.oaltd.co.uk/MVP/Default.htm

Reader Comments (Page 1 of 5) 1 | 2 | 3 | 4 | 5 | | Most Recent Comments
Post a Comment:
Security Code Security Code:
Comments with offensive language will be automatically deleted within 24 hours.