Locked lesson.

Upgrade

About this lesson

Learn about creating and updating external links, and about the potential dangers of external links in Excel.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

External Links - Master.xlsx
10 KB External Links - Source.xlsx
10 KB

Quick reference

Topic

Creating, updating and the dangers of external links in Excel.

When to use

When you have little alternative but to link to data in another workbook.

Instructions

Creating

  • Open the “Source” workbook and the “Target” workbook
  • Go to the Source workbook, and select the cell where you’d like the value from Target workbook to show up
  • Type = then, using the mouse, navigate to the Target workbook, click the cell and press Enter
  • You can now save and close Target workbook and the link will exist in Source workbook

Updating

  • When you open the Source workbook, you should be prompted to update the data
  • If you need to update at other times, go to Data > Edit Links
  • Select the links you wish to update and click Update Values

Breaking links

  • To convert links to hard coded values, go to Data > Edit Links
  • Select the links you wish to convert and click Break Link

Dangers

  • Links are set to certain file paths. If the file you’re linking to moves, then your links will break
  • If you insert a new row or column in the target workbook, and the source workbook is not open, the source workbooks formulas will NOT update, and your links will point to the wrong places. (In order to modify the target workbook, the source workbook must also be open to avoid this issue.)
  • Your ability to email the file to others is impaired. Unless you send them the target file and they put it in the exact same path on their machine, the links will be broken
  • If you decide to make a backup copy of your entire solution, you may find your links pointing to the original data copy unless you are very careful. (The proper method to do this is open both the source and target workbooks, save the target under a new name, then save the source under its new name.)
Login to download
  • 00:04 In this video we're going to look at some of the problems that can be caused when we
  • 00:08 try to link external workbooks together.
  • 00:10 Sometimes they can work quite well but
  • 00:12 other times we can end up having some challenges.
  • 00:15 So,we want to watch that.
  • 00:16 Now we've got two workbooks open here.
  • 00:18 We have a master and we have a source workbook over here.
  • 00:22 And inside the master workbook we have a house pricing matrix.
  • 00:26 And over on the right-hand side we have the costing matrix for a specific house.
  • 00:30 And what we want to do is link these two together to get the total house cost.
  • 00:34 So, we can do this, this is actually relatively simple.
  • 00:36 We take equals and we can go and
  • 00:38 select the total house cost from the other work book.
  • 00:41 And you'll notice it puts source, sheet1, b8, no problem.
  • 00:45 When we hit Enter, It links very nicely.
  • 00:48 And as we go in now, if we were to turn around and
  • 00:50 say you know the framing cost of this one went to $75,000.
  • 00:54 Everything works, it updates nicely here, it updates nicely on this side as well.
  • 01:00 So, everything looks great, but here's what ends up happening in some of
  • 01:04 the stuff that can go a little bit sideways on us.
  • 01:07 I'm going to save the master workbook and I'm going to close it.
  • 01:15 Now, at this point I'm going to insert a new row inside the Source workbook and
  • 01:21 now we'll open the master back up.
  • 01:26 And we'll move the master and look what's happened to the formula for
  • 01:28 the master now.
  • 01:30 It's actually pointing to the finishing cost of 80,000,
  • 01:33 not the total house cost anymore.
  • 01:35 So what actually ended up happening here?
  • 01:37 Well the challenge is, that we made a change to the external link source,
  • 01:42 while the master workbook was closed.
  • 01:45 It doesn't understand that it needs to update the formula
  • 01:49 in this formula bar to point to a different place.
  • 01:53 And yet, now that the workbook is actually open at the same time as the source,
  • 01:59 when we go and delete this row.
  • 02:01 You'll notice that it does stay put, it's now pointing back.
  • 02:05 Although, it says B8 when I come and check over here,
  • 02:07 it'll say it's pointing to B7 and indeed it is.
  • 02:10 So, in order to fix this we would actually have to go back and repoint the formula.
  • 02:14 So, this is one of those things that can end up catching you out.
  • 02:17 The big thing here is, if you are using external links to another work book,
  • 02:21 always make sure that both the Master and the Source are open.
  • 02:24 Before you start making modifications in your Source workbook.
  • 02:28 That's a really, really big issue.
  • 02:30 Now, let me show you something else here.
  • 02:32 I'm going to save this again, because now it's all fixed.
  • 02:35 We're gonna say File, then we'll go down and close this.
  • 02:39 We're also going to go to File and we will close the Source workbook as well.
  • 02:44 And yet we'll save our changes.
  • 02:47 Now, what I'm going to do is,
  • 02:48 I'm gonna move the location of the Source workbook into a broken folder.
  • 02:55 And we'll open up out master again.
  • 02:57 And it says, Hey, this contains links to one or more sources,
  • 03:00 if you trust them would you like to update them?
  • 03:02 We'll say, Sure, update it and it says, wait a minute, we can't find the link.
  • 03:08 If you'd like to find it, you can click at at links or
  • 03:11 you can continue without updating values.
  • 03:13 So we'll say, continue.
  • 03:15 The challenge here is that If we open this up a little bit here now,
  • 03:19 we can see that it's trying to find GoSkills Source.
  • 03:23 But it can't because that workbook's not there anymore, so it's left this alone.
  • 03:26 Now, if I want to lock this in right away and
  • 03:30 make sure that it's not not gonna change and we're not gonna get prompted.
  • 03:33 I could go back to the data tab and I could go to edit links.
  • 03:38 I could select this link and choose break link and
  • 03:40 at that point it's going to convert all these to values.
  • 03:42 And I won't get bothered by that any more.
  • 03:45 Or, if I want to actually locate the link, I could do that too.
  • 03:49 What I could do is, I could click on this and I could say change source.
  • 03:54 And at that point it says, where would you actually like to find this?
  • 03:57 So, we can drill into locate the file that is sitting in C,
  • 04:04 Go skills, broken, there it is.
  • 04:07 And we'll say okay and
  • 04:09 now when we hit close, everything will update correctly again for us.
  • 04:14 So, be really careful when you are using external links for work books.
  • 04:18 One of the strategies that I like to recommend is make sure that if you
  • 04:21 are going to be linking to a specific sheet in a workbook.
  • 04:25 Set up a sheet in that target workbook, that is never going to change.
  • 04:29 So, its only purpose is to hold the values that are being linked
  • 04:32 out to somewhere else.
  • 04:33 That's going to end up saving you from having one of these things where somebody
  • 04:36 inserts rows in that workbook.
  • 04:38 But while it can be a handy technique, I'd have to say use it as caution.
  • 04:41 We try to avoid it as much as we can.
  • 04:43 However there's definitely instances where you do need it.
  • 04:45 Just guide yourself accordingly as you go through and try not to monkey around with
  • 04:50 the data page, if you know it's feeding other files.

Lesson notes are only available for subscribers.

Gift this course