How to Break Links in Excel

Fact Checked By Cindy Wong

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.

Excel Break Links GIF1

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.
Open the Data tab
  1. Click on โ€œDataโ€ in the top ribbon.
    • This will switch to a new toolbar.
Click Workbook Links
  1. Click on the โ€œWorkbook Linksโ€ย option in the โ€œQueries & Connectionsโ€ย tab section.
Click Refresh All
  1. 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.
Click Update in the warning
  1. 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.
Click the Manage Workbook links message
  1. 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.
Click break links
  1. Click on the โ€œEllipsisโ€ icon and then the โ€œBreak linksโ€ย button.

Break Links Using Manage Workbook Links Tool

Break Links Gif 2

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.
Navigate to data, then break links
  1. Click on the Data tabย in the top ribbon and then Workbook Linksin the Queries & Connectionsย tab section.
Click the ellipsis, then break linkds
  1. This will open the Workbook Linksย sidebar.
    • Then, click on the Ellipsis icon beside the spreadsheetโ€™s name to open a small dropdown menu.
  2. Here, click on the Break linksย button.
Click break all

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.
Click on Copy
  1. 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).
  2. 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.
Click paste special
  1. Now, right-click on the cell and click on โ€œPaste Specialโ€ย in the dropdown menu.
    • This will open a smaller โ€œPaste Specialโ€ย window.
Click on values and number formats
  1. Click the โ€œValues and number formatsโ€ option in this window’s โ€œPasteโ€ section.
  2. 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.
Open the file menu
  1. Click on โ€œFileโ€ย in the main ribbon bar, which will take you to the main Excel startup screen.
Click Options
  1. Here, click the โ€œOptionsโ€ย button towards the bottom left side of the window.
    • This will open a smaller โ€œExcel Optionsโ€ย window.
Click ask to update automatic links
  1. Click on โ€œAdvancedโ€ย in the list of options towards the left side and scroll down to find the โ€œGeneralโ€ settings.
  2. Uncheck the โ€œAsk to update automatic linksโ€ option.
  3. 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.
Open the formulas tab
  1. Click on the โ€œFormulasโ€ย button in the main ribbon.
Change the calculation mode
  1. In the โ€œFormulasโ€ tab, click on the โ€œCalculation Optionsโ€ย button found in the โ€œFormula Auditingโ€ย section.
  2. 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:

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.

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!