Tracking all the external links in a workbook can be tedious; here is a short guide demonstrating several strategies for finding them quickly.
The first method involves creating a macro that searches for all Named ranges, Data Validation rules, Chart titles, and Conditional Formatting elements that refer to another workbook. While this approach is fast and effective, it won’t always find everything.
1. Find and Replace
Excel allows users to refer to data points located in other workbooks by creating external links or references, making data access faster and updates simpler without opening its source file. Unfortunately, if an original workbook is deleted, renamed, or moved, external links become ineffective for updating data – becoming problematic over time.
There are various approaches you can take to identify and break external links. You could take a look in the file properties, the Message box, or use manual methods – such as searching keywords in the File Name field, looking for specific words or phrases within an Excel file, looking at defined names, objects (such as text boxes and shapes) and chart data series to locate these external links.
Search and Replace allows you to quickly locate external links using worksheet selection followed by Ctrl+F. Type “xl” into the Find What box to search all file formats of an Excel workbook, including .xls (older workbooks) ,.xlsx (later versions of Excel), and.xlsm (macro-enabled workbooks). Within, you can search all sheets within Workbook, Sheet by Sheet, or Formula in this particular sheet – to group results in the way you desire, click one of those column headers, such as Sheet or Formula, and group results accordingly.
Use the Message box to locate external links. This will provide a list of workbooks linked from within a worksheet that you can choose between displaying in full-screen mode or changing/removing from. Once found, toggle either way using your keyboard arrow keys!
Check for external links by clicking the arrows beside each row in the Data tab and viewing a map displaying their locations. This can be particularly helpful if your workbooks are linked since yellow highlighted parts indicate data from another workbook.
2. Edit Links
Excel can link data from one workbook to another through named ranges, conditional formatting rules, and chart titles. Sometimes, these links refer to external sources such as files or web pages that need changing due to changes in their locations or other reasons – therefore, knowing how to locate and manage these external links within Excel is crucial for effective business operations.
Excel provides an easy way to locate external links through its ‘Find and Edit Links’ feature on the Data ribbon tab. Once activated, this dialog box displays all external links present in your spreadsheet or workbook; to select them, either click directly on individual links or press CTRL + A on your keyboard – once established, they can then be edited or removed as required.
The ‘Edit Links’ dialog box allows you to update any values linked to your current workbook, alter its destination with the click of a ‘Change Source’ button, or even break its connection entirely if necessary. However, be mindful that once broken links cannot be reversed, and do your due diligence before doing this, as any unintended repercussions cannot be reversed!
No matter the type of external link, you can customize how it appears to users by entering custom text into the “Text to Display as Link” field. This text will appear when users hover their mouse over it. However, remember that if your link connects to an external source that requires opening and saving before accessing its data, it prompts users when clicking on your link.
3. Find and Repair Broken Links
Excel users often struggle with broken links, which can result from deleted or moved files, rendering a spreadsheet inaccessible and leading to the loss of data. Therefore, broken links must be fixed as soon as possible using either find and replace or edit links commands – however, this process can take considerable time, so it may be quicker and easier if there is an alternative way of seeing and fixing all external links simultaneously via a macro.
To create a macro that detects and repairs broken links in Excel, open the workbook you wish to inspect. Select all cells with broken links before pressing Ctrl+F to open the Find and Replace Dialog Box. In the Find What box, enter any pattern (e.g., “.xl,” within), then choose Within in the Look In box; this will display all related sheets/workbooks linked with your workbook so you can select any worksheet containing broken links before clicking OK.
Using the Edit Links command, you can easily monitor and maintain linked workbooks using Excel. This feature is great for quickly finding and fixing broken links due to file relocation; click on the Edit Links button from the Connections group on the Data ribbon, and a dialog box will open with a cell relationship diagram showing all linked workbooks and sheets as well as an Options button which allows you to specify where this new source file should reside.
You can turn off the automatic updating of workbook links by selecting the Options button and clearing out the check mark from Update Links on Save. However, this option should be avoided as it could cause hyperlinks to break if the workbook is updated while offline – however, reenabling it if your outside data is accurate is an option as well.
4. Microsoft Excel Add-in
The Microsoft Excel Add-in is an invaluable tool that makes finding external links in a workbook a snap both on Windows and Mac platforms. In addition to finding these external links, this add-in also detects broken ones so they can be fixed – an invaluable asset when sharing workbooks with others.
Use this add-in to quickly identify all external references in your Excel sheet and replace them with current values or break them altogether. This tool can locate external links in your worksheet and formulas, defined names, objects (like text boxes or shapes), chart titles, and data series. Using it automatically refresh all Excel formulas within your workbook, or running a macro to search out all external references is another option.
Another way to quickly discover all of the external links within a sheet is by using the “Edit Links” option, which will open a dialog box listing all spreadsheets referenced by your Excel file. Opportunities in this window include “Update values (can be used for reloading data),” “Change Source (can change referenced Excel file),” and ‘Break links.”
To use this function, open your Excel sheet and navigate to the Data tab. Within the ‘Edit Links’ dialog box, click ‘Break links’ to convert all linked cells in your worksheet into values – thus replacing their existing values with something like 55 so your worksheet won’t connect with external files anymore.
Sometimes add-ins won’t appear in the ribbon despite being installed correctly, possibly due to macro-enabled files downloaded from the internet and located outside a trusted directory. To resolve this, unblocking an add-in file must first be performed using Windows Explorer; to do this, click its Properties on its General tab and check its Security section, where there will be an Unblock option under General.
