Can’t Break Links in Microsoft Excel? Try These 6 Fixes

If you’re working in Excel, you might have encountered a situation where you can’t break links to external references. This can be frustrating, especially if you want to update your data sources or share your workbook without sharing the source files.

But don’t worry, there are some fixes you can try to break those stubborn links, free your Excel data, and make your workbook independent. If you can’t break links in Microsoft Excel, follow the steps below.

How to Break Links in Excel (and Why It Stops Working)

Before we dive into the fixes, let’s understand why break links might not work in the first place. When you link data from another workbook or worksheet, Excel creates a connection that allows you to update the data automatically. However, sometimes you might want to break this connection and keep only the values in your workbook.

To do this, you can use the Edit Links option on the Data tab. This will show you a list of external references that are linked to your workbook. You can then select the link you want to break and click Break Link. This will replace the formulas that use the link with their current values.

However, sometimes this command might not work or might not show all the links. This could be because:

• The link is used on a protected sheet or workbook.
• A named range or a defined name uses a protected external link.
• A chart or a chart element has protected external links.
• The link is used in a data validation rule or a conditional formatting rule.
• The link is created by a different file type or in an older (or newer) version of Excel, causing compatibility issues.

If any of these scenarios apply to your workbook, you’ll need to try some of the methods we’ve outlined below to try and break the link.

How to Unprotect an Excel Sheet

One of the simplest fixes you can try is to unprotect the sheet or workbook that contains the link. This will allow you to edit or delete the link without any restrictions.

To unprotect a sheet, right-click on the sheet tab and select Unprotect Sheet. If prompted, enter the password and click OK. You’ll need to repeat this for any other sheets that contain external links that you can’t break.

To unprotect an entire workbook, press File > Info. Next, press Protect Workbook > Protect Workbook Structure, then press Protect Workbook > Encrypt with Password. Delete the password from the file for both options (when prompted to) and click OK.

When you’re done, press Save, then close the workbook and reopen it (just to check that the password is removed).

After unprotecting the sheet or workbook, try opening the Data > Edit Links menu again to see if the Break Link option is grayed out. If it isn’t, use it to remove any external links that remain.

How to Delete Named Ranges

Another possible fix is to delete any named ranges or defined names that use external references. A named range is a group of cells that has a name assigned to it. A defined name is a formula that has a name assigned to it. You can use these names in formulas instead of cell references.

To delete named ranges or defined names, follow these steps:

1. From the ribbon, press Formulas > Name Manager.

2. In the Name Manager menu box, select any existing external references.
3. Click Delete and confirm.

Repeat this for any other names that use external references. Once you’re done, press Data > Edit Links and check that the Break Link option remains grayed out for any remaining external links. If it isn’t, remove the links from your spreadsheet.

How to Remove External Links from Excel Charts

Another possible fix is to remove any external links from charts in Excel that you’ve created. A chart or a chart element might use an external reference as its source data or as its formatting option.

You may need to set up your data first. For instance, if you’re using external data from another sheet, you may need to move or copy the data to your existing sheet to break the link.

To remove external links from charts or chart elements in Excel, follow these steps:

1. Select the chart or chart element that uses an external reference.
2. Press Chart Design > Select Data.

3. In the Select Data Source menu, select Edit under Legend Entries (Series) or Horizontal (Category) Axis Labels.

4. In the Edit Series or Axis Labels box, change the Series name or Axis label range to a cell reference within your workbook that isn’t external.
5. Click OK and close the dialog boxes.

Repeat these steps for any other charts or chart elements that use external references. After removing the external links from your charts, press Data > Edit Links to see if you can break any links that remain.

How to Remove External Links from Excel Data Validation Rules

You may need to check and remove external links from data validation rules. A data validation rule might use an external reference as its source data or as its criteria.

For example, you might have a drop-down list that depends on a named range in another workbook, or a rule that restricts the values based on values in another worksheet. To remove external links from data validation rules, follow these steps:

1. Select the cell or range of cells that use a data validation rule with external references.
2. Press Data > Data Validation.

3. In the Data Validation menu, select Settings.
4. In the Allow box, select Any value.
5. Click OK to save your changes.

Repeat for any further external links, then check that you can remove them by pressing Data > Edit Links > Break Link.

How to Remove External Links from Conditional Formatting Rules in Excel

Another possible fix is to remove any external links from conditional formatting rules. A conditional formatting rule might use an external reference as its source data or as its criteria. For example, you might have a rule that changes the color of a cell based on a value in another workbook or worksheet.

To remove external links from conditional formatting rules, follow these steps:

1. Select the cell or cell range that uses conditional formatting rules with external references.
2. Next, press Home > Conditional Formatting > Manage Rules.

3. In the Conditional Formatting Rules Manager menu, press Edit.

4. In the Edit Formatting Rule box, change the Formula or Source to a value or range within your workbook.
5. Click OK and close the boxes, then repeat for any further cell ranges.

After removing external links from conditional formatting rules, press Data > Edit Links > Break Link to remove the links completely.

How to Manually Remove External Links from Excel

Still having trouble? One quick way to manually remove external links from your Excel file is to temporarily convert your Excel file into a zip file instead. You can do this by renaming the file extension in File Explorer manually, but only if the file isn’t encrypted so the Excel data can still be viewed, but not edited.

Once you’ve changed the extension, you can extract the contents and remove a hidden folder (called externalLinks) from the zip file. This folder stores the information about the external references in your workbook. Once you’re done, you can then compress the file and restore the original Excel file type.

Before you begin, make sure you make a copy of your Excel file first. You don’t want to do this with your original file, just in case the file becomes corrupted.

1. To do this, you’ll need to first rename the copied file in File Explorer from file.xls or file.xlsx to file.zip. Once you’re done, extract the contents of the zip file to a new folder.

2. Open the new folder and delete the externalLinks folder in the xl folder. If this doesn’t work in File Explorer, try using an alternative like 7-Zip instead.

3. Next, select all of the other files and folders, right-click them, then select Compress to Zip folder from the context menu.

4. Rename the zip file with your original .xls or .xlsx extension.

Once you’ve renamed the zip file, open it in Excel and check that it’s still working. You should be able to check that the links are removed by opening the Edit Links menu—if they aren’t, you should be able to remove them at this point.

Managing Your Excel Data

Using the steps above, you can quickly try and find ways to break external links in your Microsoft Excel workbook. You can also try merging data from your Excel workbooks into a new file that lacks external links. Alternatively, you could save your file using a different Excel file type, such as XLSX from XLS (or vice versa).

Struggling to find troublesome data? You can always use the search functionality in Excel to help you.