Breaking Links in Excel And Why You Should Avoid Linking to Other Workbooks
What is an External Link?
External links exist in Microsoft Excel when there are external references made, meaning that a cell in one workbook references a cell in another workbook. A cell in one workbook referencing other cells in the same workbook, even on different sheets, is not an external link. There are a few circumstances where external links are useful, however, most of the time they should be avoided if possible.
Why External Links are Useful
External links can be useful because they allow you to reduce the size of an excel file. For instance, if you have large data tables in one Excel file, externally linking two workbooks is more efficient than replicating the data in each file. This action allows for the files to run quicker and minimizes the duplication involved in financial modeling.
Why You Should Avoid External Links
Although externally linking workbooks can be beneficial, there are numerous occasions where they produce more problems than they eliminate. If both files engaged in the external link aren’t opened simultaneously, the model could display errors as the file containing the data isn’t active. This creates an issue for the distribution of the files as they must both be present for the end-user to run them correctly. Oftentimes when links are incorporated into an externally linked workbook, there may have been revisions or recalculations in the other workbooks, changing the values of the results. In some cases, links may refer to other notebook cells that no longer have values imputed, creating errors. In those cells, error messages will appear that look either like ‘#REF!’ or ‘#VALUE!’, as seen below.
How to Tell if You have External Links
Alerts will appear when first opening a workbook. It will state there are links to external sources and will then prompt to update those links.
If you choose to “Update” the links, then you can get the updated numbers at the risk of potential errors as mentioned previously. If you choose to “Don’t Update” the links, then the values will not be updated based on the latest version of the linked workbook.
How to Find External Links
There are a variety of different ways to find external links. One way to locate the links is to click on the data tab. Then select edit links and a dialogue box will appear showing the sources of links. Another way is using the keyboard shortcut “Alt+E+K”. To check charts and graphs you select the chart and go to the design tab. Choose select data and then check the ranges. If information is from different sources then you have external links. Next, you can check the names, ranges, and cells. Another option is by checking the conditional formatting rules. On the first sheet of the workbook, highlight the entire sheet by clicking the triangle in the top left corner of the ruler. Then, go to the home tab and select conditional formatting. Within conditional formatting, check the rules for the current worksheet as well as the ones proceeding. To do so, click the dropdown arrow to jump from page to page where it shows which sheet the formatting rules are for. While looking at the ranges and rules, look for ranges or paths that connect to an external workbook.
Breaking Links
Just like finding external links, breaking them can be done in many ways. Once you have found that information is from different sources you select a source and then break the link. If this works then the link should disappear from the dialogue box. If they remain in the box, and you have found external links in named ranges and cells then you go to the formulas tab and select name manager. It reveals where each name is referred to. If the cell has ‘C:\\’ in the front of the title it refers to data outside of the workbook. For those cells, select the names and then delete them. Once again, return to edit links on the data tab and proceed to try and break the link again.
Deleting Links in Macro-Enabled Workbooks
There may be a more complex issue in the workbook if the external links are unable to be broken after the mentioned steps. If it is a macro-enabled workbook, external links cannot be permanently deleted following these steps. To do so, go into the developer tab and then select VBA Code Developer. In the VBA Code Developer, external links will be found by going line-by-line through the workbook’s code.
Conclusion
Externally linking multiple workbooks poses more risk than reward. Users must consider the repercussions associated with multiple files running dependent on one another. At Top Shelf Models, all financial models avoid externally linking to alternative files as this enables our clients to efficiently model their projects. Schedule a model demo today through our website http://www.tsmfinancialmodels.com to discuss your firm’s financial modeling needs.
Before founding 3E in 2016, Managing Member Eric Bergin was Director at Rockpoint Group, where he was responsible for for the Finance Group, as well as acquisitions, asset management, and investor reporting activities.