[Tags: guide, excel, accounting]
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
- Related Articles:
- Too Many Cell Formats in Excel (Resolved)
- Bank of England provides best historical foreign exchange rates data
- Free Microsoft Excel Master Help Provided
Reader Comments (Page 3 of 5)
Friday June 19, 2015 @ 12:27 PM
Very Helpful. I found my issue in the Defined Name area.
Tuesday June 23, 2015 @ 06:49 PM
Thanks for this - ended up using #4, as had already tried all the rest, which thankfully worked in Excel 2010, and it found my issue - in Data Validation - Lists.
Tuesday July 14, 2015 @ 10:37 AM
That was awesome! so easy! thank you sooo much! :)
Tuesday July 28, 2015 @ 11:49 AM
Thank you so much found the link hidden in the name manager.
Tuesday August 11, 2015 @ 02:30 AM
Thank you! I used option 1 & 2 but still got the annoying message about updating values linked to external source when opening my Excel file. Didn't know the real cause was linking names. Option 3 helped me get rid of it. Really helpful!
Thursday August 20, 2015 @ 08:23 AM
Thanks man, i have finally lowered the size of my file from 8mb to 300kb. Only one small correction, the Name manager is not in data ribbon it's in formulas ribbon. Thanks again for your help
Wednesday August 26, 2015 @ 04:47 PM
Thanks JD for your comments... surprised it took 150,000 views to discover that error.
Wednesday September 16, 2015 @ 10:23 AM
Thanks! Great walk through, helped me quickly find and resolve the issue
Friday September 25, 2015 @ 10:24 AM
Many thanks - spent a while trying to fine these links
Saturday October 17, 2015 @ 11:21 AM
Thank you so much TJ, I found my link in the data validation.
Tuesday October 27, 2015 @ 01:17 PM
4th option where there are various tools, I used FindLink.zip, it really worked as wonder. I was stuck since weeks to fix this, but findlink fixed it. Thanks a lot.
Thursday October 29, 2015 @ 11:57 AM
YES !!!
Thanks a lot
Tuesday November 10, 2015 @ 05:59 PM
Thank you SO MUCH for the 'NAME MANGER' tip. I am a very experienced Excel user, but have never been able to overcome that particular external links issue... mystery solved.
Thursday January 14, 2016 @ 11:34 AM
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.
Friday February 5, 2016 @ 11:29 AM
After wracking my brain and downloading Bill Manville's code, which I had to let unlock all the sheets, it finally found the offending link on an action button. THANK YOU BILL! THANK YOU! THANK YOU!
Tuesday February 16, 2016 @ 12:27 PM
Great, I knew options 2 and 3 but I could not find the link yet. The add-in of option 4 brought the solution. It was in the source data of a chart. Thanks so much
Friday February 19, 2016 @ 09:02 AM
Thank yo so much!!!!
Monday February 22, 2016 @ 05:59 PM
Option 4 nailed it
Thursday March 3, 2016 @ 05:11 AM
Great Article thank you - enabled me to locate and remove an external link that I introduced when copying data from one sheet to another.
Thursday June 4, 2015 @ 10:34 PM
The link posted saved me HOURS of search!
The reference was a macro assignment in a button - and the macro was in a differnt work book...the tool did the job.