Keeping track of all external links in an Excel spreadsheet can be difficult, particularly if shared among several people who could alter them without warning.
Manually searching for all external links can be time-consuming, but there are ways to simplify the task. This article will discuss various techniques for finding external references in an Excel spreadsheet.
Method 1: Look in Formulas
Keeping track can be challenging for large spreadsheets containing many external links. Luckily, there’s an easy way to identify all the external links within them – using a macro. Once installed in Excel, go to Developer > Macro > Macro Settings and name your macro Fetch Links before pressing F5. Pressing F5 will create a sheet with all these external links in your workbook.
Excel external links can often be concealed within formulas, making them hard to spot when working with complex spreadsheets or workbooks shared among multiple people. You may receive a warning dialog box when opening such workbooks that alerts them of its existence.
Excel makes it easy to locate external links by using its Find feature. To open the Find and Replace dialog box, go to Home > Editing > Find & Select > Find. In the Find What field, enter a search term related to either file path or URL-referenced workbook(s), selecting all results to identify any external links and breaking them using the Format Cells menu if present.
Another method for finding external links in Excel is its Name Manager feature. This will display all named ranges within your file, and any that refer to other workbooks can be examined further by going to the Formula tab and viewing their Refs to Other Workbook area. Likewise, chart titles or data validation drop-down lists containing external links can be verified using the Chart Titles or Data Validation sections of the Formatting menu.
Method 2: Look in Named Ranges
Excel enables users to link with other workbooks and use data from them in formulas, making it a valuable and versatile feature, but it can also present problems when trying to update external links within workbooks. When Excel attempts to update external links in one workbook, it may encounter difficulties and not find its source; this may cause the workbook to stop functioning as expected or even crash altogether. Fortunately, there are ways of finding these external links and fixing them.
First, navigate to the Name Manager on the Formula tab and review its list. Any named ranges that refer to external links will be highlighted yellow for easy selection; from here, you can change its reference by simply selecting and editing its reference field.
Conditional formatting rules provide another great place to search for external links. To do so, open the Home tab and click Manage Rules; this will display all the laws in your workbook and highlight those that refer externally with an orange warning icon. Once identified, you can select them and modify their reference accordingly.
Finally, it is worth checking the chart titles and data series for external references. Many charts contain unintended file path links to other workbooks; using Excel’s built-in “break link” feature, you can break these.
If the error message that some external links cannot be updated still appears, it could be because you missed an identified range or conditional formatting rule that refers to externally. Once all named fields and rules referencing externally have been removed from the workbook, try updating links again; otherwise, you may be required to use different techniques or adding-ins that automatically locate and delete external links simultaneously.
Method 3: Look in Chart Titles
Sometimes, you need to pull data from another workbook into an Excel spreadsheet, which can be accomplished via external links. This method makes accessing data much more efficient without needing to copy-paste or manually type formulas for that information; this approach saves time and effort when working with large spreadsheets.
Step one of locating external references in an Excel sheet involves opening it. Next, search for cells containing external references using Excel’s Find and Replace feature or by typing “[*]” into its Search for field. When you have located these references, you can delete them or change their destination accordingly.
One way of finding external references within a worksheet is to check its chart titles. A chart title containing an outer reference will be marked with an icon, indicating that it links back to another workbook or file. You may also try looking in its formula bar or conditional formatting drop-down list for any external references that might exist there.
If you cannot locate external references manually, use the VBA “Go To” command to open a window showing all external references in your Excel worksheet. However, be careful, as breaking a link in this window cannot be undone!
Finding external links in a workbook using this method requires searching the Objects, though this may prove more challenging due to Excel objects not having the same format as cells. To do so, navigate to the Home ribbon and select Find & Select before choosing Go To Special. Finally, select the “Objects Radio Button ” in the Go To window and then OK.
Method 4: Look in Objects
Maintaining an inventory of external references used in a spreadsheet can be tedious, mainly if the number is enormous. Although Microsoft doesn’t offer an inbuilt function that helps find all external links, some workarounds may assist you in this search process.
One effective method is the Find and Replace feature found under the Edit tab of Data ribbon. In the ‘Find What’ box, type in [*] for an alphabetized list of external references encased by long brackets. Select Workbook in the Look In dropdown to search all files containing these references or Sheet to limit your search to the current sheet alone. Use All Open Workbooks, Private, or Exclude Local search criteria as appropriate in the ‘Within’ box before choosing Formulas as the Look For setting to retrieve a comprehensive list of external references used within the spreadsheet, which you can then group according to your liking before printing them or saving as a new file – another good option!
Another way of finding external references in your spreadsheet is using Excel’s Edit Links function, located under Queries and Connections on the Data tab. By clicking this, a dialog window will open with all workbooks containing external references or links; to break these, use the Break Links button located mid-right in this dialog window – however, be warned this action cannot be reversed but will instead change all external references back to their previous values automatically.
Be mindful that external links can compromise the integrity of your spreadsheet if they update automatically upon reopening, so ideally, remove or break them unless specifically informed otherwise by an authority that this workbook contains essential external references; then enable Prompt user on Automatic Update for Workbook Links under Data Tab in Queries and Connections Group in Excel ribbon for that workbook.
