Sometimes, however, these external links can cause issues. They may be out of date, be broken, or have other issues. You may also not want to include external references if you’re sending a file to someone else who doesn’t have access to the files that they reference. Getting rid of external references from your Excel spreadsheets is usually fairly straightforward, but you may find that there are some stubborn ones that are harder to get rid of. If you want to know how to find and remove external links in Excel, follow the steps below.
How to Break External Links in Excel
Excel has a feature that helps you to find external links and gives you the option to break those links if you no longer need them. Whilst this is very useful, it doesn’t always find all of the links if your document, particularly if links are somewhere other than in a cell. However, this is definitely a good place to start if you want to find and remove your Excel external links. To break external links in Excel:
How to Find and Remove External Links in Objects
Unfortunately, the method above may not remove all of the external links in your spreadsheet. There may still be some lurking in other places. If this is the case, you can find and remove them manually. One place that you may find some is in Excel objects, such as shapes or text boxes. To find and remove external links in Excel objects:
How to Find and Remove External Links in Charts
External links can also be found in your Excel charts. There’s no simple way to search for these links in charts, so you’ll need to work through your charts manually. To find and remove links in Excel charts:
How to Find and Remove External Links in Pivot Tables
Pivot tables are another place where external links may be hiding. Once again, you’ll need to examine each pivot table in your spreadsheet; there’s no simple way to find search them all at once. To find external links in Excel pivot tables:
How to Find and Remove External Links in Named Ranges
A clever way to quickly refer to multiple cells in Excel is to create named ranges. These named ranges can be referenced in formulas or even just to quickly navigate to the appropriate cells. Sometimes these named ranges may refer to cells outside of your current spreadsheet. In other words, they contain external links. To find external links in named ranges in Excel:
How to Find and Remove External Links in Cells
You can also search for external links in cells manually using Excel’s Find and Replace tool. This is worth trying if you’ve tried all of the above, but there are still some stubborn external links hiding in your spreadsheet. To find external links in Excel cells:
Excel at Excel
Learning how to find and remove external links in Excel ensures that you can remove all the unnecessary or dead links from your spreadsheet. It also means that if you send the spreadsheet to someone else, you won’t have any issues with missing references. There are plenty of other Excel skills that you can learn to take your spreadsheets to the next level. For example, you can learn how to count cells containing text in Excel. You can learn how to enable or disable macros in Excel. You can even learn how to create a random number generator. I usually start looking by creating a copy of the workbook, then right click on a worksheet to see if unhide is not grey’d out , and unhide as needed. then select all the sheets (select the first, shift and select the last in the tabs along the bottom of the window) and use find for the files named in the links list FindAll is a helpful option, expand the window, and use alt+prtscr to capture the panel’s window then paste that into a new .rtf file, or temporarily into a new email Select just a single sheet as indicated by that list – (with all the sheets selected, any change can be applied to the indicated cell of all the selected sheets – and at this stage you would just be looking to see what can just be set to the “Value” rather than needing to be set to something differennt – maybe a new sheet with the variable values indicated in it, and the external links from other sheets set to data cells in that new ‘linksToGet’ worksheet. So go through the links creating data value cells in the workbook and commentary etc, for the external links that were found! Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
