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.
Contents
Break ‘normal’ workbook links within formulas
Break ‘normal’ workbook links in Excel
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):
- Go to the Data ribbon.
- 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.
- Select all the data links you’d like to kill.
- 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:
- Find a cell relating to another workbook within a formula.
- Copy the link, shown with the square brackets inside the formula.
- Make sure that the exact same sheet as the source sheet also exists in your current workbook.
- Press Ctrl + h for opening the replace dialogue box.
- Paste the copied link and leave the Replace field blank.
- 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:
- On the Formulas ribbon go to Name Manager and you can see all the names in your workbook.
- Please check in the reference column wether a cell name refers to another workbook. Just delete the entry if you want to cut that link.
Hold on a second. Was this information helpful so far?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
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:
- Select the cells having data validation rules referring to other workbooks.
- Go to the Data ribbon.
- Next, click on Data Validation.
- 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:
- Click on Conditional Formatting in the center of the Home ribbon.
- Click on Manage Rules.
- 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.
- 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:
- Find out if the data source of your Pivot Table is located on another workbook as described in this article.
- If the Pivot Table links to another workbook you have two options:
- Set another data source within your current workbook.
- Remove the Pivot functionality and copy and paste the complete Pivot Table as values.
Break links With Professor Excel Tools or the Break Link Tool
Break all links in your workbook with Professor Excel Tools
As 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:
- Go to the Professor Excel ribbon.
- 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.
- Select all the link types you’d like to break and click on start.
- 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 our free test version and see if it works for you.
Alternativly, we offer the break link functionality as a stand-alone add-in. Please take a look at our Break Link Tool.
Break all links in your workbook with Professor Excel Tools
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Comments 11
Terry
Thanks for this information. I had struggled for hours trying to find errant links and finally located them in Conditional Formatting. One of the clues was that when I tried to bread or update the links, they wouldn’t.
Scott
I’ve been searching for hours… I rarely fill out these comment fields on sites like this… but I’m compelled. This is literally the only tool I’ve found that was able to fix this problem within my project. Major props.
Caren
I found this useful. Thank you so much!
Shankar
best solution on the subject i found on the web. lives up to the name “Professor Excel”
cheers!!!!
farhad azim
delet all comments
ganners9
Brilliant!!! I’ve been searching for ‘zombie’ links but this is first article to suggest Conditional Formatting. Links now found and removed. Many thanks 🙂
Al
Be sure to unlock the sheet, the break link button will be grayed out otherwise.
N36
Thanks – very useful article! Finally managed to delete pesky zombie links that have been bugging me for a while.
Ayrton
Really helpful man! Thnks a lot
Pete
Great article. Name Manager and Conditional Formatting had the hidden links not required. Got quite bothered not knowing what kind of data was hidden. Have been struggling with those links again and again, with no help found for quite some time!
Jason
Conditional formats… who would have thought it !!! Thanks you finally helped solve my issues