Break Links in Excel – All of Them (Even When Excel Doesn’t)

When you copy cells or worksheets from another Excel workbook, links to other worksheets in many cases still persists. Excel offers a function to break links but this function only works with links within formulas. There are many other types of links as links within conditional formatting rules or data validation rules. The bad news: Those links can’t be cut easily. The good news: there are still ways to break these links.

Break ‘normal’ workbook links within formulas

Usually, “normal” workbook links within formulas can be cut easily with the ‘Edit Links’ function included in Excel (the numbers are corresponding with the picture on the right hand side):

1. Go to the Data ribbon.
2. If the “Edit Links” button is not greyed out it means that there is at least one active link to another data source (usually another workbook). Click on that button.
3. Select all the data links you’d like to kill.
4. Click on Break Link.

Please be careful: all cells referring to other workbooks within formulas will be changed to values. The underlying formulas will be removed.

If you want to avoid that the formulas are removed you might want to try another (more manual) approach: Using the replace function in Excel to replace the links with nothing:

1. Find a cell relating to another workbook within a formula.
2. Copy the link, shown with the square brackets inside the formula.
3. Make sure that the exact same sheet as the source sheet also exists in your current workbook.
4. Press Ctrl + h for opening the replace dialogue box.
5. Paste the copied link and leave the Replace field blank.
6. Click on Find Next.

Break links from named ranges

You can name cells in Excel. Instead of the cell reference as “A1” just the cell name will be shown. Breaking such links is easy:

1. On the Formulas ribbon go to Name Manager and you can see all the names in your workbook.
2. Please check in the reference column whether a cell name refers to another workbook. Just delete the entry if you want to cut that link.

Break Data Validation links

If you have data validation rules in your workbook – such as dropdown lists within cells – it’s possible that they relate to other workbooks. Unless you know exactly which cells have such rules you unfortunately have to search them.

Once you found cells having data validation rules referring to other workbooks follow these steps:

1. Select the cells having data validation rules referring to other workbooks.
2. Go to the Data ribbon.
3. Next, click on Data Validation.
4. The most common is the type List. If the source refers to other workbooks you should remove the path and link them to a place within your workbook. Alternatively remove the data validation rule completely by setting the “Allowed” type to “Any Value”.

Break links of Conditional Formatting rules

Conditional Formatting rules can relate to other workbook as well. Especially when copying worksheets to other workbooks such links can be created. Finding them must be done for each worksheet separately:

1. Click on Conditional Formatting in the center of the Home ribbon.
2. Click on Manage Rules.
3. In the drop down list on the top of the newly opened window select ‘This Sheet’. Now all the conditional formatting rules of the current worksheet will be shown.
4. The easiest way is deleting the rules referring to other workbooks. Otherwise you have to change them manually and link them to your current workbook.

Break links of Pivot Tables

If the data source of Pivot Tables is in another workbook you can break this link too. Therefore, follow these steps:

1. Find out if the data source of your Pivot Table is located on another workbook as described in this article.
2. If the Pivot Table links to another workbook you have two options:
   1. Set another data source within your current workbook.
   2. Remove the Pivot functionality and copy and paste the complete Pivot Table as values.

Break all links with Professor Excel Tools

Because cutting links in Excel can be very troublesome and takes a lot of time, we’ve included a break link manager in our Excel add-in ‘Professor Excel Tools’. All the above mentioned steps are provided.

For breaking all the workbook links follow these steps:

1. Go to the Professor Excel ribbon. and click on the ‘Break Link Manager’ within the ‘Workbook Tools’ group (the button with crossed out link on it). Professor Excel Tools now counts how many times each link type can be found within your Excel table.
2. Select all the link types you’d like to break and click on start.
3. Now, Professor Excel will break all the links. This procedure can take some time, especially if you have a lot of data in your workbook. The current status is shown in the status bar on the bottom of the screen.

Try it for free and see if it works for you.