Hi guys
In this video I’m going to show you what to do when you open an Excel file and you
see the following message: This workbook contains links to one or more external sources that
could be unsafe.
Should you click Update or Don’t Update, and how can you stop the warning from appearing?
Well I’m going to show you what the warning actually means, and I’ve got a couple of
great solutions to help you fix it.
I’ve had a look at some of the other advice out there, and I can see that people are either
showing you how to silence the warning completely which is a terrible idea for security, or
they’re just showing you how to delete parts of your data, which might end up being a bad
idea too.
So I’m going to give you a few options here and because it’s a slightly longer video
I’ve broken it up into chapters and you can click to the chapter you want and skip
around and find the right part.
This video has chapters so you can skip to any part you like.
And I’m going to show you how to do this on both a Windows computer and on the Mac.
So, let’s get started.
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.
I’m just going to say Don’t Update for now.
I have an example here, a list of expenses.
These numbers are just made up for this video, by the way.
These first 2 figures are just numbers, but look at this figure for home expenses, it’s
pulling this in from a separate spreadsheet, and the same for business expenses.
The way these spreadsheets work, I open up Home expenses first and get these numbers
right, and then I open Business expenses and get these right.
And then I open up the main Expenses workbook and it pulls all the numbers together into
one place.
So if you’ve made a spreadsheet do this, if you made the external data source, it’s
perfectly safe.
You can click Update, 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.
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 this dropdown and choose Search in Workbook.
And press Enter 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.
I’m going to show you now 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.
And we do this the same way on both Windows and Mac.
You need to:
Click on the Data tab.
Then click on Edit Links.
And you’ll see a line here for each linked file.
Select one of these and then click Startup Prompt.
Select ‘Don’t display the alert and update links’.
Then OK.
And then you should do this for each linked file you have, although I’ve found that
once you’ve changed one it just changes the rest for you.
And then click Close.
And now when you open this file, it will update the data from the linked files, but only in
this document.
You’re still protected when you open other Excel files.
So let’s test that.
The figure for Home is 865.
I’m going to close the workbook, and I can click Save, and then open Home Expenses and
change one of these figures.
And then close and save that.
And then I’m going to open the Expenses workbook again, and notice how there’s no
warning now, but it has pulled in an updated figure.
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 Advanced.
Scroll all the way down to the General section.
And you want: 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.
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 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 Function Option F11.
And 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.
So that’s everything you need to know about this warning.
I hope it’s put your mind at rest and I hope it’s answered any questions you had.
Let me know in the comments below how you got on, and if the video helped you please
give it a like.
And if you want to see more of my stuff in future hit the subscribe button.
Thanks for watching and I’ll see you next time.