How to Easily Remove Links in Microsoft Excel Spreadsheets

Working with data in Excel often requires inserting links to external files or webpages. But sometimes you need to remove these links to clean up your spreadsheet. In this comprehensive guide, I’ll walk through the various ways to delete links in Excel using simple tools everyone has access to.

Whether you’re dealing with a table of hyperlinks or defined name references, these methods will help you remove links and prevent associated errors. With just a few clicks you can strip external connections and declutter your workbook.

What Are Links in Excel?

There are two main types of links used in Excel spreadsheets:

• Hyperlinks – Clickable links to webpages, files or cells. Appear as colored underlined text.

• External References – Referenced cells in another workbook. Used in formulas like ='[Budget.xlsx]Sheet1′!A1

Links can be helpful but need removing if:

• Links lead to broken sources, causing errors

• You want to eradicate clutter and distractions

• Links present security risks from untrusted sources

• To permanently break dependence on an external source

• To create a shareable workbook without active links

So let’s look at how to remove both hyperlinks and external references in Excel.

Deleting Hyperlinks in Excel

Hyperlinks appear as clickable blue, underlined text that jumps you to webpages or files. To remove them:

1. Select Cell(s) Containing Hyperlinks

Highlight the cell or range with the hyperlinks you want to delete.

2. Right-click and choose ‘Remove Hyperlink’

This will instantly strip the hyperlink while keeping the text.

Alternatively use Ctrl+K to open the Edit Hyperlink dialog and click ‘Remove Link’.

Repeat for multiple cells. This quickly clears hyperlinks from your spreadsheet.

Eliminating External References in Excel

External references link to specific cells in other workbooks. For example:

=SUM('C:Reports[Sales.xlsx]Sheet1'!B2:B10)

To remove the external reference link:

1. Select Cell(s) with External Reference Formula

Click the cell(s) containing the external reference link you want to remove.

2. Press F2 to Edit the Formula

The formula will appear in the formula bar.

3. Delete the External Reference Syntax

Keep the formula itself but delete the workbook/sheet name syntax from the reference.

For the example above, remove ‘C:Reports[Sales.xlsx]Sheet1’! and leave just the B2:B10 cell address.

4. Press Enter to Save

This breaks the link but retains the local calculated value.

Removing Links with Range Names

Range names can also create dynamic links if referencing other files. To delete:

1. Go to Name Manager with Ctrl+F3

2. Select Name with External Reference

3. Click Delete

This will remove the named range and its underlying external link.

Turn Off Automatic Update of Links

You may want to keep links but stop them updating. Here’s how:

1. Navigate to Data > Edit Links

2. Click the link you want to break

3. Uncheck the ‘Automatic Update’ checkbox

Now it will retain the current value but not refresh.

Fix Broken Links

Instead of removing unreachable links, you can redirect them:

1. Go to Data > Edit Links

2. Select the broken link and click ‘Change Source’

3. Point to the new location of that workbook/data

This fixes the link to the new source.

Remove All Links from a Workbook

To strip every link from a file in one go:

1. Press Ctrl+A to Select All Cells

2. Go to Home > Clear > Clear Links

This will wipe every external reference and hyperlink in the entire workbook with just two clicks!

VBA to Quickly Remove Links

For frequent or bulk link removal, use VBA code to automate it:

Sub RemoveAllLinks()     ActiveSheet.Cells.Hyperlinks.Delete   For Each l In ActiveWorkbook.LinkSources     ActiveWorkbook.BreakLink l, 1   Next lEnd Sub

This macro will delete every hyperlink and break all external reference links in seconds.

Link Removal in Excel for the Web

The steps are mostly the same in Excel for web:

• Hyperlinks – Right-click > Remove Hyperlink

• External References – Edit formula to remove source path

• Links Sidebar – Lists links and allows breaking

The key differences are lack of Name Manager and VBA options in the web version.

When Printed/Exported Documents Retain Links

Note that removing links in Excel doesn’t delete them from printed or exported versions. Additional steps are required:

• Uncheck “Print links” in the output settings

• For exports like PDF, indicate no external link inclusion

• Use Find/Replace to remove links from the output documents

So that covers multiple ways to effectively remove hyperlinks, external references, automatic updates and named ranges. By understanding these Excel link deletion techniques, you can prevent link-related errors and clutter in your workbooks.