Therefore, if you have a malfunctioning link to an external reference in your Excel file, you may have to rely on manual methods to identify this link. At the same time, you can also break a link to an external reference in your Microsoft Excel file. The steps below will help you to find, break and remove external link references in Excel.
How to Find External Link References in Microsoft Excel
As a data analysis and visualization tool, Microsoft Excel allows content in different forms. Therefore, you are likely to see text and numbers feature in formulas while seeing objects such as charts. The different type of content means that when finding external links, you may require different approaches. Here’s how to find the references in Excel for different types of external references.
How to Find External Links Used in Formulas
The extension of a Microsoft Excel file ranges from .XLS, .XLSX, .XLSM depending on the Excel version and the presence of macros. However, within an Excel file with reference to another workbook, the file extension will be shown as .XL format. Using the file extension name, you can search for external links within formulas. Here’s how to do so. Step 1: Click the Start button on the Taskbar and type excel to search for the app.
Step 2: From the results, click on the Microsoft Excel app or document to open it. Step 3: On your keyboard, click on the Control + F keys to launch the Find and Replace dialog box. Alternatively, click on the Find & Select button in the Home tab of the Microsoft Ribbon.
Step 4: Type the .XL in the Find What box.
Step 5: In the Within section of the Find and Replace dialog box, click the drop-down and select Workbook.
Step 6: In the Look in section of the Find and Replace dialog box, click the drop-down and select Formulas.
Step 7: Click Find All at the bottom of the dialog box to run the search for external references in the workbook.
How to Find External Links Used in Defined Names
In Microsoft Excel, you can name a single cell or a range of cells. These named cells can further be referenced by name instead of cell referencing. One benefit of this is that it makes formulas in your worksheet easier to understand. If you have defined names in your Excel workbook, here’s how to find them. Step 1: On the Microsoft Excel Ribbon, click on the Formulas tab.
Step 2: Within the Defined Names group, click Name Manager.
Step 3: In the Name Manager dialog box, run through the Refers To column in your workbook to identify Defined Names with external references.
How to Break External Link References in Microsoft Excel
One of the quickest ways to know if your Microsoft Excel file has links is by using the Edit Links feature in the Microsoft Ribbon. The feature is only accessible when there are links within the Excel workbook. Without links in the Excel workbook, the Edit Links feature will appear greyed out. The Edit Links feature makes it easy to edit the links within a workbook including breaking the link. Step 1: On the Microsoft Excel Ribbon, click on the Data tab.
Step 2: Within the Queries & Connections group of the Data tab, click the Edit Links button to launch the Edit Links dialog box.
Step 3: In the Edit Links dialog box, click on the link you would like to break within the Source list.
Step 4: Click the Break Link button and this should launch a pop-up.
Step 5: Click the Break Links button on the pop-up to confirm your action.
How to Delete the Name of a Defined Link in Microsoft Excel
If the external reference in your Excel uses a defined name, breaking the link will not automatically remove the defined name. To delete the defined name, here’s what you should do. Step 1: On the Microsoft Excel Ribbon, click on the Formulas tab.
Step 2: Within the Defined Names group, click Name Manager.
Step 3: In the Name Manager dialog box, click the name you want to delete.
Step 4: Click Delete at the top of the dialog box.
Step 5: Click OK to confirm your action.
Viewing Version History in Microsoft Excel
If you mistakenly break an external link reference in your Excel file, you can click the undo button to reverse the change. However, if the Excel file has undergone several changes, the undo feature might not help reverse the breaking of an external link reference. Using the version history of Microsoft Excel may be a better option for tracing changes made. The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
