How to Break Links in Excel

Need to convert formulas to their current values? Use Break Links in Excel. To do this, open the Workbook links menu, click the ellipsis, and click “Break links.” Below, I’ll show how to complete this with step-by-step instructions.

What are Excel break links? Microsoft Excel has a great link feature, allowing users to add links to other spreadsheets in the main workbook. Creating a workbook link allows you to get specific data from the spreadsheet and import it. However, if the linked file is corrupted or lost, Excel will show unwanted errors. That’s when I use the Excel break links feature.

In this article, I’ll go in-depth about the Excel break links feature, what it does, why, and how you can use them in Microsoft Excel.

What Are Excel Break Links?

The Excel break links feature allows users to disconnect an external spreadsheet from the current spreadsheet. This is slightly different than removing hyperlinks in Google Sheets. In fact, there’s a specific menu just for it.

The break links feature in Excel can help you remove unwanted dependencies and control if your workbook can extract data from an external source. This process will often delete the formula creating the link and replace it with the values.

How To Find Links in Microsoft Excel

Before I explain ways to use Excel break links, I’ll briefly review how to identify outbound links in an Excel workbook. Note that minor changes will occur in the interface according to your Excel version. For this guide, I used Microsoft Office Professional 2021.

1. Open the Excel spreadsheet and where you want to check for break links.

2. Click on “Data” in the top ribbon.
   • This will switch to a new toolbar.

3. Click on the “Workbook Links” option in the “Queries & Connections” tab section.

4. A new “Workbook Links” sidebar will open on the right side of the screen. Here, you can see the spreadsheets connected to the current workbook.
   • To make sure the list is fully updated, click on the “Refresh all” button.

Ways to Break Links in Microsoft Excel

There are multiple ways to break links in an Excel workbook. Let me guide you through some of them below. Note that I also have a resource on adding hyperlinks.

Break Links From Dialog Box

This is one of the quickest methods to break links in Excel. When the spreadsheet is opened, Excel will let you know that the workbook contains links, and you can choose to update the links if you trust the source file.

Here is how to break links using a dialog box in Excel:

1. Open the Excel workbook that contains the links.

2. If the file contains links, an alert warning box will show up, asking if you wish to update the links.
   • Click the “Update” button, and the workbook will check for the links.

3. If Excel finds a broken link, an “Unable to Refresh” error ribbon will appear above the formula bar.
   • Here, click on “Manage Workbook Links.” This will open the familiar “Workbook Links” sidebar.

4. Click on the “Ellipsis” icon and then the “Break links” button.

Break Links Using Manage Workbook Links Tool

This method allows you to see all links in a workbook and manage or break them. I suggest using this method if you’re unsure of the location of the cell that contains the link reference or if you wish to break multiple links.

Here is how to do this:

1. Open the spreadsheet where you wish to break links in Excel.

2. Click on the Data tab in the top ribbon and then Workbook Linksin the Queries & Connections tab section.

3. This will open the Workbook Links sidebar.
   • Then, click on the Ellipsis icon beside the spreadsheet’s name to open a small dropdown menu.
4. Here, click on the Break links button.

To break all the links in the workbook, head over to the Workbook Links sidebar and click on the Break all button.

Break Links Using Paste Special

If the break links option doesn’t work out for you, another way to break the link is by using the “Paste Special” option.

Essentially, I will copy and paste the data in the same location while keeping the values and formatting. Here is how to do this:

1. Open the Excel spreadsheet where you want to break links.
   •  Click and drag to select the cell range containing the links you wish to remove.

2. With the cells selected, right-click on the cells and click on “Copy” or press the Windows shortcut, Ctrl + C (or Cmd + C on Mac).
3. With the cells selected, click on the cell where you want to paste the values.
   • I recommend clicking on the top left cell in the selected range to ensure the values are pasted in the same location.

4. Now, right-click on the cell and click on “Paste Special” in the dropdown menu.
   • This will open a smaller “Paste Special” window.

5. Click the “Values and number formats” option in this window’s “Paste” section.
6. Click the “OK” button to save the data without formulas.

How To Stop Excel Break Links Errors

There may be some instances where you don’t want to break the links in your Excel workbook, but you also don’t want the break links error to appear whenever you open the spreadsheet.

In this section, I’ll show you some of the ways to stop the error from showing up.

Disable Automatic Updates

Using this method, you can stop Microsoft Excel from asking you to update the links whenever you open the workbook. Here is how to do this:

1. Open any workbook in Microsoft Excel.

2. Click on “File” in the main ribbon bar, which will take you to the main Excel startup screen.

3. Here, click the “Options” button towards the bottom left side of the window.
   • This will open a smaller “Excel Options” window.

4. Click on “Advanced” in the list of options towards the left side and scroll down to find the “General” settings.
5. Uncheck the “Ask to update automatic links” option.
6. Click the “OK” button to save the changes.

Calculation Options

Another way to stop being notified about broken links in Excel is by enabling manual calculation.

Microsoft Excel automatically recalculates the formulas by default. Using this method, we can disable auto-calculation. Here is how to do this:

1. Open the Excel spreadsheet where you wish to disable auto calculations.

2. Click on the “Formulas” button in the main ribbon.

3. In the “Formulas” tab, click on the “Calculation Options” button found in the “Formula Auditing” section.
4. Click on “Manual” in the dropdown menu.

Reasons To Use the Excel Break Links Feature

If you like the current version of your data and don’t want to rely on formulas or changing data, you may want to use Break Links in Excel. That converts all formulas to what you see right now. Basically: Break Links rebuilds your spreadsheet into a simplified version using only current data.

But there are many reasons why you may want to use Excel break links. Some of the most common reasons include:

1. Version Controlled Spreadsheets

Breaking links between spreadsheets can help create different versions of spreadsheets by freezing data in the workbook.

For example, if I have a spreadsheet with sales data that is updated daily, I can create a new spreadsheet and link it with the main spreadsheet to get the data. I can then break the link to create a spreadsheet that contains the sales data for a specific month.

2. Increased Performance

The file size of a linked spreadsheet can become large, which means there can be a significant performance impact, especially on low-end devices. Breaking the links will stop frequent data updates, eliminating lag or jitters.

3. Avoiding Errors

There are a lot of problems that can be caused by creating spreadsheet links, especially when the linked file is deleted. Breaking the links will mitigate this risk.

4. Increased Data Security

Multiple connected workbooks can prevent unauthorized access to external data sources, which can help keep the data secure in other workbooks.

Frequently Asked Questions

Here are some of the questions I hear most often about the Break Links feature in Excel.

How Do You Convert Linked Cells to Values?

To convert linked cells to values:

1. Select the cell range with the values and copy it with the Ctrl + C keyboard shortcut.
2. With the cells selected, hold Ctrl and press Alt + V shortcut to access the paste special menu.
3. Choose the “Values and number formats” option when prompted.

Why Isn’t Excel Break Links Working?

The option to break links in Excel files may stop working for multiple reasons. These can include:

• Technical glitches
• Incompatibility with Excel versions
• File format mismatch

Note: The workbook will not be protected if you’re not signed in to Microsoft products on your device.

Why Am I Unable to Break Links in Excel?

Protected workbooks may stop you from making changes to them. Similarly, shared workbooks can have varying levels of permissions, some of which may stop you from making changes to them.

If you’re seeing phantom Excel links, they might just be in protected areas. In this case, you’ll need to change permissions. You can also manually find links with a defined name or use data validation to check for external links.

Why Excel Break Links Won’t Break

There are multiple reasons why the break links feature doesn’t work in Excel.

A protected and shared worksheet might modify the permissions, preventing you from being able to edit links. Incompatible Excel versions can also have problems with breaking links.

What Can Go Wrong With Excel Break Links?

Although the Excel break links are extremely useful, they can cause some issues, such as:

• Data Loss: Breaking links to other spreadsheets without copying data to the main spreadsheet may cause you to lose access to important data.
• Formula Errors: Breaking links can sometimes cause unwanted errors in a separate part of the spreadsheet, including broken references and formulas. This can result in incorrect calculated values, broken Excel charts, empty cell references, and data series.
• Inconsistency: If the spreadsheet relies on various external data sources and if you break external links, it can sometimes cause inconsistencies in the dataset. There may also be a need to update links to fresh data, which you won’t be able to do if the links are already broken.
• Broken Macros: If you’re using macros that rely on multiple cells or formulas, you may not want to use Break Links.

Is There a Way to Undo Excel Break Links?

If you want to undo break links in Excel immediately after using the feature, you can do so by using the Ctrl + Z keyboard shortcut to undo the action.

However, if you saved the changes after breaking links, there is nothing you can do to roll back the changes unless you have a backup of the workbook. If you’re using the cloud version of Excel, try to recover a previous spreadsheet version.

Conclusion

The Excel break links feature can offer a lot of flexibility, allowing you to link multiple spreadsheets to import the data. However, this can cause problems if the linked spreadsheet is lost or corrupted. Thankfully, the break links feature can remove unnecessary links from the Excel workbooks.

Did this tutorial answer all of your questions? Feel free to provide me with some feedback in the comments section.

Related:

• How To Pull Data From Another Sheet in Excel
• 3 Ways To Extract a URL From a Hyperlink in Google Sheets
• Can Google Sheets Reference Another Sheet?

References

1. Microsoft. “Break a link to an external reference in Excel.” Microsoft Support. https://support.microsoft.com/en-us/office/break-a-link-to-an-external-reference-in-excel-f1ca8b08-4f24-4af6-92e5-f4fdb1442748. Accessed February 6, 2024.