

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 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):
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:
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:
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
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:
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:
If the data source of Pivot Tables is in another workbook you can break this link too. Therefore, follow these steps:
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:
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.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
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