Permanently Remove “Update Links” Alert in Real Estate Excel Models

There are few Excel experiences more frustrating than getting that “this workbook contains links to one or more external sources” alert and not being able to find/break the external links causing it. In this tutorial, I teach you five steps to fix the problem.

In the video that follows the written instructions, I fix the issue using a real situation from v1.10 of Michael’s Basic Hotel Acquisition Model in our Library of Real Estate Excel models.

Want to learn to build institutional-quality real estate financial models yourself and from scratch? Consider joining our real estate financial modeling training program: The A.CRE Accelerator.

Why Does This Occur? Excel Workbooks with External Links

If you’ve ever opened Excel files shared with you by a co-worker, broker, or source online, you’ve likely come across the “this workbook contains links to one or more external sources that could be unsafe” alert. This is because the Workbook you’re attempting to open has a link to a different Workbook that is located either on your computer, on a local server, on another user’s computer, or on a remote server.

Most often, these links are benign and were unintentionally added to the Workbook. This usually occurs when a user borrows logic from a separate workbook in creating the workbook you’re now opening, and in doing so inadvertently leaves one or more links back to the first workbook. So you may open a Workbook from a broker, who in modeling a particular deal linked to data on a separate workbook without understanding how to avoid creating external links.

Check out our ‘Best Practices in Real Estate Financial Modeling‘ post for how to avoid accidentally creating external links

Other times, the link is created intentionally, but without the intent to harm. For instance, an organization may store data on a central Excel workbook that other workbooks then communicate with via links. To give a real estate example, a REPE firm might have forward yield curve data stored on a separate Excel file. Each time the firm’s proprietary real estate model is opened, it automatically grabs the yield curve data from the separate Excel file containing that data.

In rare cases, the external link is created intentionally, but with malicious intent. The external links in these files may pull information or code to your workbook that leave malware, spyware, or worse on your computer. While I’ve never come across this situation in my 18+ year real estate career, having opened thousands of Workbooks shared with me by others, it does happen. Thus to warn you of this possibility, Excel gives the option to ‘Update‘ or ‘Don’t Update‘ any external links within a Workbook each time you open the file.

When to Remove (i.e. Break) External Links

So when should you remove, or in Excel jargon “break“, these external links? In my experience, you should always remove external links unless you’ve been expressly notified that the Workbook includes essential external links.

In real estate, this usually means your company (or you) intentionally added external links to enhance the functionality of a model. Otherwise, the external link was likely included unintentionally and can be removed without affecting the functionality of the model.

How to Remove (i.e. Break) External Links?

So how do you break external links, and thus remove the annoying “this workbook contains links” warning? Use the following steps to remove external links within the model and permanently fix this issue:

I. Select ‘Don’t Update’

This is important. While the vast majority of these external links are not harmful, just to be safe it’s best not to update the link; meaning, Excel won’t actually attempt to pull data from that external link.

So when the “This workbook contains links” warning box appears, make sure to select ‘Don’t Update’.

II. Remove External Links in Cell Formulas

The most likely place where unintended external links can be found are in formulas within actual cells. These are not necessarily the easiest to find, but they are the easiest to remove. That’s because Excel has a built-in feature for automatically breaking these external links.

To break a link within a formula in a cell:

1. Go to the ‘Data’ ribbon
2. In the ‘Queries & Connections’ section, select ‘Edit Links’
3. The Edit Links dialog box will appear with a list of external links
4. Select each of the links and click the ‘Break Link’ button for each

If an external link to a source is contained within a cell formula, the link will disappear from the list. If doing this step removes all links from the list, then you’re done!

But most likely, you already knew this step and that’s why you found this blog post. Doing the above didn’t fix the issue, and so let’s move on to step III.

III. Remove Graphs/Charts with External Links

Occasionally, the creators of a model will copy a graph or chart from one model and paste it into another. They do this, without realizing that the links within the graph/chart point back to the original graph/chart. As a result, these graphs/charts are worthless and should be removed.

To check to see if a graph/chart contains external links:

1. Select the graph/chart
2. Go to the ‘Design’ ribbon under the ‘Chart Tools’ heading
3. In the ‘Data’ section, select ‘Select Data’
4. There is both series data (left-hand list) and labels data (right-hand list). Select each one at a time, and click ‘Edit’
5. A ‘Edit Series’ dialog box appears with a box entitled ‘Series Values’
6. Check if the values point to either a file path (e.g. C://users/jbird/…) or a named range not included in the Workbook
7. If the series points to either of the above, you should delete the chart/graph

Follow the above steps with each and every graph/chart within the Workbook.

Note that Excel’s built-in ‘break external links’ feature actually replaces file path links in graphs/charts. The consequence of this is that the links are properly broken, but the chart/graph will no longer update based on changes made to the model. So if you want the insights from the chart/graph, you’ll have to recreate them using data within the actual Workbook.

IV. Remove Named Ranges or Named Cells Pointing to External Links

When Excel’s built-in break links feature doesn’t work, it’s most likely because there is a named range or cell that contains an external link. Excel can’t break these external links automatically, so you’ll have to find and remove them yourself.

To find and remove external links from named ranges and cells:

1. Go to the ‘Formulas’ ribbon
2. In the ‘Defined Names’ section, select ‘Name Manager’
3. Click the ‘Refers to’ heading to sort by name
4. Select each named range/cell that refers to a file path (e.g. C:\, D:\, F:\, etc)
5. Click ‘Delete’ to permanently remove that named range containing an external link
6. Hint: use SHIFT+CLICK to select and delete multiple items at once
7. Once all named ranges pointing to an external link have been deleted, click ‘Close’
8. Go to the ‘Data’ ribbon
9. In the ‘Queries & Connections’ section, select ‘Edit Links’ (if the ‘Edit Links’ button is grayed out, you’re done!)
10. Within the ‘Edit Links’ dialog box, check if links still appear in the list
11. If links are still in the list, select each of the links and click the ‘Break Link’ button for each

After doing 10 and 11 above, if all external links have been removed from the Workbook the list in the ‘Edit Links’ dialog box will be empty. If the ‘Break Link’ button still does not remove one or more of the links, proceed to the following step.

V. Remove Conditional Formatting Rules Using External Links

The next place to look for external links is within conditional formatting rules. This step is equally (if not more) involved than the previous, but there is a shortcut or two to make this easier:

1. Select the first worksheet (i.e. furthest left tab) in the Workbook
2. Click the gray triangle between the column A and row 1 label
3. Go to the ‘Home’ tab
4. In the ‘Styles’ section, select the ‘Conditional Formatting’ drop-down
5. In the ‘Conditional Formatting’ drop-down, click ‘Manage Rules’
6. A ‘Conditional Formatting Rules Manager’ dialog box appears
7. Within the dialog box, there’s a ‘Show formatting rules for:’ drop-down at top with a list of rules for the current workbook below
8. If rules exist for the current workbook, delete any rule where the ‘Formula:’ contains a reference to an external link file path or named range to an external link (e.g. =$C$17= “Discount”, $C$17>’c:\user\…’, etc)
9. Once all rules in that worksheet with references to external links have been deleted, proceed to the next worksheet using the ‘Show formatting rules for:’ drop-down menu within that same dialog box
10. Go through each worksheet, deleting any conditional formatting rules with external links
11. Click ‘Close’ to close the ‘Conditional Formatting Rules Manager’ dialog box
12. Go to the ‘Data’ ribbon
13. In the ‘Queries & Connections’ section, select ‘Edit Links’ (if the ‘Edit Links’ button is grayed out, you’re done!)
14. Within the ‘Edit Links’ dialog box, check if links still appear in the list
15. If links are still in the list, select each of the links and click the ‘Break Link’ button for each
16. Click ‘Close’ to close the ‘Edit Links’ dialog box
17. Save the Workbook

Upon clicking closing the ‘Edit Links’ dialog box, the ‘Data>Queries &Connections>Edit Links’ button should now be grayed out. That means you’ve successfully deleted all external links and the “this workbook contains links to one or more external sources” alert will no longer appear.

What to do if the above didn’t fix the issue?

If following the above steps didn’t fix the issue, in my experience one of two things is occurring. Either you missed a named range or conditional formatting rule containing an external link. This is quite common when you have dozens (or hundreds) of named ranges or conditional formatting rules. So return to steps IV and V again to confirm.

If you’re confident you’ve removed all named ranges and conditional formatting rules pointing to external links and if the workbook is a macro-enabled workbook (i.e. file path ends in .xlsm, xlsb, xlm, xlb, etc), then you may need to go through each macro to confirm that an external link isn’t being used.

Additionally, I’ve read in some forums that after all else fails, resaving the file as a different Excel file type (e.g. going from xls to xlsx or xlsx to xlsm) fixes the issue. I’ve never tested, or needed to test, this technique. But if all else fails, give it a try.

And if nothing else works, you will have to either live with clicking ‘Don’t Update’ upon opening the file each time or rebuild the model.

Video Tutorial – How to Permanently Remove “Update Links” Alert in Excel Files

In addition to the written instructions above, I’ve created a video tutorial for how to stop the update links alert in Excel. If you have questions about this technique, or if I’m missing something, please let me know!