Should you click Update or Don’t Update? In this guide I show you the right way to deal with this type of warning. Everything here works on both the Windows and Mac versions of Excel – I’ll show you the Windows versions primarily, but I’ll show you how to do it on the Mac when that differs.
I’ve made a Youtube video that explains all the steps in this tutorial. Or, read on!
Table of Contents
Why is this happening?
So why is this happening? Well, usually Excel files are self-contained. All the data you need is in one file. But when you see this notice, the file you’re trying to open wants to pull data in from somewhere else – usually from another Excel spreadsheet.
Let’s take an example where we have three spreadsheets. The first two each contain a simple list of Home and Business expenses – all made up for the purposes of this demo by the way.
The third spreadsheet, Expenses.xlsx contains formulas that pull in data from the other two spreadsheets. Look at the formula that pulls in the figures for Home:
This spreadsheet makes it easy to see all my expenses figures in one place.
So if you’ve made a spreadsheet do this – if you made the external data source yourself – it’s perfectly safe. You can click Update when you open it, and it pulls the latest figures into the spreadsheet.
But if you didn’t make the file yourself, you should be really careful about opening it because there could be some code in there that could harm your computer. If you’re not sure where the data is being pulled in from, you should click Don’t Update.
Find the cause
Let’s take a file that’s giving us one of these warnings about external sources. How can we take a good look at this file to decide if we trust it?
Let’s open the file but as we’re being cautious, click Don’t Update.
Now let’s take a closer look at how we pull in another Excel file. You see the file name here, it’s enclosed in brackets. So we can do a search for an opening bracket.
To do a search, you need to press Control – F on a Windows computer, and if you have a Mac it’s Command – F. And type in left bracket.
It’s important that we search the whole workbook, not just one sheet.
So on Windows, you need to click Options, then where it says within, change this to Workbook.
And then click Find Next to find each result.
And on a Mac, click the dropdown in the search box, and choose Search in Workbook.
And press the Enter key to search. You can use the right arrow to find the next result. Then you can look at each external data source and just decide if you trust it. If you find any external data that’s not meant to be there, maybe it was put in by accident so you can just delete it.
Silence this warning for the current workbook
I’m now going to show you the best fix for this. We’re going to silence these warnings just on this one workbook.
In general you don’t want to switch warnings off, because you need to know if there’s a security risk. But we open this file all the time and we want to silence the warnings on it.
Time needed: 2 minutes.
How to silence this warning for the current workbook
- Open Edit Links
Click on the Data tab. Then Click on Edit Links.
You’ll see a line here for each linked file.
- Change the startup prompt
Select one of your linked files. Then click Startup Prompt.
Click on Don’t display the alert and update links.
Then click OK.
- Close the Edit Links box
Now close the Edit Links box. Now, when you open this file it will update its data from the linked files, but it won’t give you a warning.
Silence these warnings for all workbooks
An alternative fix is to silence this warning for all the Excel files you open. I don’t recommend this though, because normally if your computer’s trying to warn you something is unsafe, it’s a good idea to listen to it. But it’s your choice!
If you have a Windows computer you need to go to File then Options. Then click on Advanced. Scroll all the way down to the General section.
Look for Ask to update Automatic Links. Untick this then OK.
On a Mac it’s slightly different: click on the Excel menu at the top left of screen. Then click Preferences, and then Edit.
And look for Ask to update Automatic Links. Untick this, then close the window.
Once you’ve done this, each time you open a file like this it will update the external data without asking you.
Other things you should check
Now if you’re still getting the warning but you can’t find those references to external data sources, there are a couple of other places where you might find them, so let’s have a look.
The first is the Name Manager.
Click on Formulas, then click Name Manager. If there’s anything listed here, look under Refers To and if there are any external files, that’s where your data is being pulled in from. So you can check those files to see if they are safe.
And the second place: there could be a Macro, which has a reference to another file.
So we need to have a quick look in the VBA editor.
On Windows, you need to press Alt – F11.
And on a Mac, it’s either Option – F11, or Fn – Option – F11.
On the left you’ll see a file for the workbook and a file for each sheet. Open each of these and if there’s any code, have a look through it to see if there are any external files referred to. If these are blank, then you don’t have any Macros. You can close the VBA editor when you’ve looked in here.