Can be a pain to eliminate them
Microsoft Excel is a powerful tool, but keeping your Excel files tidy and functional is often difficult. Hyperlinks can be a helpful way to connect your spreadsheet with external references, but they can also be a pain.
Whenever a user clicks that cell to edit it, it will open the link in your web browser. And if you send your Excel file to someone else, some links may no longer work. In this tutorial, we’ll explain how to break links in Excel.
How to Break External Links in Excel
To break linked data:
- In the Data tab, select Edit Links.
- In the Edit Links dialog box, select the link you want to break. You can select multiple links by holding the Ctrl key and pressing them all. To select all links, press Ctrl + A.
- Select the Break Link button.
Note: You can use Excel’s VBA code to break links in your current workbook automatically. However, such a macro will only delete the links when you click Break Link as above, so setting it up isn’t worth the effort.
How to Find and Delete Links in Named Ranges
Excel professionals often give their ranges and cells defined names to make formulas and data validation drop-down rules easier to create. You can find links to external sources as follows:
- In the Formulas tab, select Name Manager.
- In the Name Manager pop-up box, select the name you want to change.
- Select the name.
- Select Delete then press OK.
How to Find Cells With External Links
If you aren’t sure where your links are, you can use the Excel Find feature to discover any external links:
- Press Ctrl + F to open the Find and Replace dialog box.
- Select Options.
- In the “Find what” box, type .xl — this will search for all Excel file formats.
- In the “Within” box, select Workbook or Sheet.
- In the “Look” box, select Formulas (this will search for all Excel formulas in that workbook or sheet).
- Select Find All.
Note: To find links within objects like graphs and pivot tables, you’ll have to open the properties for that object. For graphs, select the chart title. For pivot tables, select the Analyze tab, then select Change Data Source.
No More Links
Whenever you copy cells and worksheets from other Excel workbooks, you also copy any external links to other worksheets. These no longer work in your new workbook and can be a pain to eliminate when tidying up your Excel spreadsheet.
However, with these Excel tips, you can easily remove links in Excel and clean up your source files.