如果您使用Excel工作，您可能会遇到无法断开外部引用链接的情况。这可能会令人沮丧，尤其是当您想要更新数据源或共享工作簿而不共享源文件时。
但不用担心，您可以尝试一些修复来打破这些顽固的链接，释放Excel数据，并使您的工作簿独立。如果您无法断开Microsoft Excel中的链接，请按照以下步骤操作。
如何在Excel中断开(Excel)链接(Links)（以及它停止工作(Stops Working)的原因）
在我们深入研究修复之前，让我们首先了解为什么断开链接可能不起作用。当您链接其他工作簿或工作表中的数据时，Excel会创建一个连接，允许您自动更新数据。但是，有时您可能想要中断此连接并仅保留工作簿中的值。
为此，您可以使用“数据”(Data)选项卡上的“编辑链接”(Edit Links)选项。这将显示链接到您的工作簿的外部参考列表。然后，您可以选择要断开的链接并单击“断开链接”(Break Link)。这会将使用链接的公式替换为其当前值。
但是，有时此命令可能不起作用或可能无法显示所有链接。这可能是因为：
- 该链接用于受保护的工作表或工作簿。
- 命名范围或定义的名称使用受保护的外部链接。
- 图表或图表元素已受保护的外部链接。
- 该链接用于数据验证规则或条件格式规则。
- 该链接是由不同的文件类型或在较旧（或较新）版本的Excel中创建的，导致兼容性问题。
如果这些情况中的任何一种适用于您的工作簿，您将需要尝试我们在下面概述的一些方法来尝试断开链接。
如何取消对 Excel 工作表的保护
您可以尝试的最简单的修复方法之一是取消对包含该链接的工作表或工作簿的保护。这将允许您不受任何限制地编辑或删除链接。
要取消保护工作表，请右键单击工作表选项卡并选择取消保护工作表(Unprotect Sheet)。如果出现提示，请输入密码并单击“确定”。您需要对包含无法破坏的外部链接的任何其他工作表重复此操作。
要取消保护整个工作簿，请按File > Info。接下来(Next)，按Protect Workbook > Protect Workbook Structure，然后按Protect Workbook > Encrypt使用密码(Password)加密” 。从文件中删除(Delete)两个选项的密码（出现提示时），然后单击“确定”。
完成后，按“保存”(Save)，然后关闭工作簿并重新打开它（只是为了检查密码是否已删除）。
取消对工作表或工作簿的保护后，尝试再次打开Data > Edit Links菜单，查看“断开链接”(Break Link)选项是否灰显。如果不是，请使用它删除剩余的任何外部链接。
如何删除命名范围
另一个可能的修复方法是删除使用外部引用的任何命名范围或定义名称。命名区域是一组分配了名称的单元格。定义的名称是分配有名称的公式。您可以在公式中使用这些名称而不是单元格引用。
要删除命名范围或定义的名称，请执行以下步骤：
- 从功能区中，按Formulas > Name Manager。
- 在“名称管理器”(Manager)菜单框中，选择任何现有的外部引用。
- 单击删除并确认。
对使用外部引用的任何其他名称重复此操作。完成后，按Data > Edit Links，然后检查“断开链接”(Break Link)选项对于任何剩余的外部链接是否保持灰显状态。如果不是，请从电子表格中删除链接。
如何从Excel 图表中删除(Excel Charts)外部链接(External Links)
另一个可能的修复方法是从您创建的Excel 图表中(charts in Excel)删除所有外部链接。图表或图表元素可以使用外部引用作为其源数据或其格式选项。
您可能需要先设置数据。例如，如果您使用其他工作表中的外部数据，则可能需要将数据移动或复制到现有工作表中以断开链接。
要从Excel(Excel)中的图表或图表元素中删除外部链接，请按照下列步骤操作：
- 选择使用外部参考的图表或图表元素。
- 按图表设计 > 选择数据。
- 在“选择数据源”(Select Data Source)菜单中，选择“图例条目(Legend Entries)（系列(Series)）”或“水平(Horizontal)（类别(Category)）轴标签”(Axis Labels)下的“编辑”(Edit)。
- 在“编辑系列(Edit Series)或轴标签”(Axis Labels)框中，将系列(Series)名称或轴(Axis)标签范围更改为工作簿中非外部的单元格引用。
- 单击“确定”(Click OK)并关闭对话框。
(Repeat)对使用外部引用的任何其他图表或图表元素重复这些步骤。从图表中删除外部链接后，按Data > Edit Links以查看是否可以断开剩余的任何链接。
如何从Excel 数据验证规则中删除(Excel Data Validation Rules)外部链接(External Links)
您可能需要检查并从数据验证规则中删除外部链接。数据验证规则可以使用外部引用作为其源数据或其标准。
例如，您可能有一个取决于另一个工作簿中的命名范围的下拉列表，或者一个根据另一个工作表中的值限制值的规则。要从数据验证规则中删除外部链接，请执行以下步骤：
- 选择使用带有外部引用的数据验证规则的单元格或单元格区域。
- 按数据 > 数据验证。
- 在“数据验证”(Data Validation)菜单中，选择“设置”(Settings)。
- 在“允许”(Allow)框中，选择“任何值”。
- 单击“确定”保存更改。
Data > Edit Links > Break Link来删除它们。
如何从Excel中的条件格式规则(Conditional Formatting Rules)中删除外部链接(External Links)
另一个可能的修复方法是从条件格式规则中删除任何外部链接。条件格式设置规则可以使用外部引用作为其源数据或其条件。例如，您可能有一个根据另一个工作簿或工作表中的值更改单元格颜色的规则。
要从条件格式规则中删除外部链接，请按照下列步骤操作：
- 选择使用带有外部引用的条件格式规则的单元格或单元格区域。
- 接下来，按Home > Conditional Formatting > Manage Rules。
- 在“条件格式规则管理器”(Conditional Formatting Rules Manager)菜单中，按“编辑”(Edit)。
- 在“编辑格式规则”(Edit Formatting Rule)框中，将公式(Formula)或源(Source)更改为工作簿中的值或范围。
- 单击“确定”(Click OK)并关闭框，然后对任何其他单元格范围重复此操作。
从条件格式规则中删除外部链接后，按Data > Edit Links > Break Link以完全删除链接。
如何从Excel中(Excel)手动删除外部链接(Manually Remove External Links)
仍有问题吗？从Excel(Excel)文件中手动删除外部链接的一种快速方法是将Excel文件临时转换为 zip 文件。您可以通过在文件资源管理器(File Explorer)中手动重命名文件扩展名来执行此操作，但前提是文件未加密，以便仍可以查看
Excel数据，但不能编辑。(Excel)
更改扩展名后，您可以提取内容并从 zip 文件中删除隐藏文件夹（称为 externalLinks）。此文件夹存储有关工作簿中的外部引用的信息。完成后，您可以压缩文件并恢复原始Excel文件类型。
开始之前，请确保先复制Excel文件。您不想对原始文件执行此操作，以防文件损坏。
- 为此，您需要首先将文件资源管理器(File Explorer)中复制的文件从 file.xls 或 file.xlsx 重命名为 file.zip。完成后，将 zip 文件的内容解压到新文件夹中。
- 打开新文件夹，删除xl文件夹中的externalLinks文件夹。如果这在文件资源管理器(File Explorer)中不起作用，请尝试使用7-Zip等替代方案(using an alternative)。
- 接下来，选择所有其他文件和文件夹，右键单击它们，然后从上下文菜单中选择压缩到 Zip 文件夹。(Compress)
- 使用原始 .xls 或 .xlsx 扩展名重命名 zip 文件。
重命名 zip 文件后，在Excel中打开它并检查它是否仍然有效。您应该能够通过打开“编辑链接”(Edit Links)菜单来检查链接是否已被删除 - 如果没有，您此时应该能够将其删除。
管理您的 Excel 数据
使用上述步骤，您可以快速尝试找到断开Microsoft Excel工作簿中的外部链接的方法。您还可以尝试将 Excel 工作簿中的数据合并(merging data from your Excel workbooks)到缺少外部链接的新文件中。或者，您可以使用不同的Excel(Excel)文件类型保存文件，例如XLS中的(XLS)XLS X （反之亦然）。
正在努力寻找麻烦的数据吗？您始终可以使用 Excel 中的搜索功能(use the search functionality in Excel)来帮助您。
Can’t Break Links in Microsoft Excel? Try These 6 Fixes
If you’re working in Excel, yoυ mіght have encountered a situation where you can’t break links to external rеferences. This can be frustrating, especially if you want to update your data sources or share your workbook without sharing thе 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:
- From the ribbon, press Formulas > Name Manager.
- In the Name Manager menu box, select any existing external references.
- 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:
- Select the chart or chart element that uses an external reference.
- Press Chart Design > Select Data.
- In the Select Data Source menu, select Edit under Legend Entries (Series) or Horizontal (Category) Axis Labels.
- 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.
- 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:
- Select the cell or range of cells that use a data validation rule with external references.
- Press Data > Data Validation.
- In the Data Validation menu, select Settings.
- In the Allow box, select Any value.
- 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:
- Select the cell or cell range that uses conditional formatting rules with external references.
- Next, press Home > Conditional Formatting > Manage Rules.
- In the Conditional Formatting Rules Manager menu, press Edit.
- In the Edit Formatting Rule box, change the Formula or Source to a value or range within your workbook.
- 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.
- 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.
- 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.
- Next, select all of the other files and folders, right-click them, then select Compress to Zip folder from the context menu.
- 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.
