Microsoft Excel is one of the most used spreadsheet software programs. It provides data visualization and analysis capabilities that allow users to refer to external data sources. As such, it is common to have an Excel worksheet that references sources outside the workbook. Unfortunately, Excel does not currently provide a solution to automatically identify all external references in a workbook.
Therefore, if you have a broken link to an external reference in your Excel file, you may have to rely on manual methods to locate the link. You can also break links to external references in your Microsoft Excel files. The steps below will help you find, break, and remove external link references in Excel.
How to find external link references in Microsoft Excel
As a data visualization and analysis tool, Microsoft Excel allows content in different forms. Therefore, you can see text and number features in formulas while seeing objects such as charts. Different types of content means that when it comes to finding external links, you may require different approaches. Here’s how to find references in Excel for different types of external references.
How to find the external link used in the formula
Microsoft Excel file extensions range from .XLS, .XLSX, .XLSM depending on the Excel version and the presence of the macro. However, in an Excel file that has a reference to another workbook, the file extension will be displayed as .XL format. Using the file extension name, you can search for external links in formulas. Here is how to do so.
Step 1: Click the Start button on the Taskbar and type excel to search for the application.
Step 2: From the results, click on the Microsoft Excel document or application to open it.
Step 3: On your keyboard, click the Control + F keys to launch the Find and Replace dialog box. Or, click the Find & Select button in the Home tab of the Microsoft Ribbon.
Step 4: Enter .XL in the Find What box.
Step 5: In the Inside section of the Find and Replace dialog box, click the drop-down menu and select Workbook.
Step 6: In the Look in section of the Find and Replace dialog box, click the drop-down menu and select Formulas.
Step 7: Click Find All at the bottom of the dialog box to run a search for external references in the workbook.
How to find outbound links used in defined names
In Microsoft Excel, you can name a cell or a range of cells. These named cells can be further referenced by name instead of cell reference. One benefit of this is that it makes the formulas in your worksheet easier to understand. If you’ve already defined names in your Excel workbook, here’s how to find them.
Step 1: On the Microsoft Excel Ribbon, click the Formulas tab.
Step 2: In the Defined Names group, click Name Manager.
Step 3: In the Name Manager dialog box, run through the References To column in your workbook to define the Defined Name with external reference.
How to break external link references in Microsoft Excel
One of the fastest ways to tell if your Microsoft Excel file has links is to use the Edit Links feature in the Microsoft Ribbon. This feature is only accessible when there is a link in the Excel workbook. With no links in the Excel workbook, the Edit Links feature will be grayed out. The Edit Links feature makes it easy to edit the links in the workbook including breaking them.
Step 1: On the Microsoft Excel Ribbon, click the Data tab.
Step 2: In 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 Link dialog box, click the link you want to break in the Sources list.
Step 4: Click the Break Link button and this will launch a pop-up window.
Step 5: Click the Break Links button on the pop-up window to confirm your action.
How to delete the name of the specified link in Microsoft Excel
If your Excel external reference uses the specified name, breaking the link will not automatically remove the specified name. To delete defined names, here is what you should do.
Step 1: On the Microsoft Excel Ribbon, click the Formulas tab.
Step 2: In 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.
View Version History in Microsoft Excel
If you mistakenly broke 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 some changes, the undo feature may not help reverse the breaking of the external link reference. Using the version history of Microsoft Excel might be a better option for keeping track of the changes made.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
