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.

Table of Contents

    How to Break External Links in Excel

    To break linked data:

    1. In the Data tab, select Edit Links.
    How to Break Links in Microsoft Excel image 1
    1. 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.
    2. Select the Break Link button.
    How to Break Links in Microsoft Excel image 2

    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:

    1. In the Formulas tab, select Name Manager.
    How to Break Links in Microsoft Excel image 3
    1. In the Name Manager pop-up box, select the name you want to change.
    2. Select the name.
    3. Select Delete then press OK.
    How to Break Links in Microsoft Excel image 4

    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:

    1. Press Ctrl + F to open the Find and Replace dialog box.
    2. Select Options.
    How to Break Links in Microsoft Excel image 5
    1. In the “Find what” box, type .xl — this will search for all Excel file formats.
    2. In the “Within” box, select Workbook or Sheet.
    3. In the “Look” box, select Formulas (this will search for all Excel formulas in that workbook or sheet).
    4. Select Find All.
    How to Break Links in Microsoft Excel image 6

    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.

    Leave a Reply

    Your email address will not be published. Required fields are marked *