Some of the most useful and time-saving features in Excel are the options to
link data across worksheets. I'm Dawn Bjork, The Software Pro.
I'm also a Microsoft Certified Trainer and certified Excel expert.
In this online training,
we'll look at how you can link data across multiple Excel workbooks.
Just as you can link multiple worksheets within a workbook,
you can also link multiple workbooks.
You do this by creating a formula in one workbook that refers to a cell range or
a range name in another workbook.
References to other workbooks are called external references.
A workbook with the data is called a source workbook and the workbook with the
links is called the destination workbook.
You can maintain workbook links even if the workbook that is referred to is
renamed.
Why wouldn't you just put all the data into one workbook and create links that
way? Well, in some cases you can,
but the real value of linking becomes apparent when the source workbook is
perhaps being maintained or primarily updated by another person or a group.
You just simply may not have access to it all the time,
or it might be a master workbook that multiple people in your team have access
to and need to be able to work with.
Similar to creating links within one workbook,
creating a link across multiple workbooks is much easier than you might think.
Just as with any other formulas,
we start the formula in the cell where we want to see the result,
so in this case,
I want to link to a total that appears or is in another workbook with Colorado
data. Start with an equals here,
and then we'll navigate to that other workbook.
Now this can be through your task bar.
You could also go to View > Switch Windows and here this file's already open,
so I'll pick Colorado and simply point to where that total is.
This pointing method becomes a lot easier than if we actually have to type out
that entire formula. In this case,
all we're going to do is reference this total,
but it could also be a calculation or a formula to multiple cells that could be
in different workbooks. For right now, to finish this,
I'll simply hit ENTER.
Now let's look at the logic or the syntax of what this created.
The first part of this is the workbook name in square brackets.
Then the sheet name, which in this case is Sheet1.
Then an exclamation as a separator and then the cell reference for where that
total appears.
Let's repeat this for the next one,
so once again equals and then we'll navigate to where the other sheet is.
One of the things to keep in mind is that you don't have to have all these
workbooks open all the time. We'll see how we can manage that.
We're simply opening them all right now to be able to build these links.
Let's look at that syntax again.
There's only one thing that's a little different here and that is that we have
the single quotes around that reference that includes the workbook name and that
is simply what we call a character string. That is,
keep this all together because we happen to have a space and the name for New
Mexico and it's very common that workbooks would have spaces,
so not a problem doing that.
This is just a syntax or structure that Excel automatically builds when we use
that pointing method.
Now there's a different way that we can paste these links,
although it's not my preferred way.
I think it's easiest to use this pointing method because you're in the
destination workbook.
Let's look at how we can do it through a paste instead,
so in this case I'm going to switch over to that Texas sheet first.
This is the total I want to refer to first. I'll copy it.
I'll simply use CTRL+C.
You can use any technique you're most familiar with or that you prefer.
Then we'll navigate back to that original file.
That is where we're storing it,
which is the Sales Summary and we can use our Taskbar for that as well at this
point. Now we want to paste it, but we want to paste it as a link.
We could do this through the Home tab of the Ribbon. Instead,
I'll right-click here and from the shortcut menu here is the option to Paste
Link and I'll pick it.
Now we have created that link.
We've pasted in the link back to that Texas sheet.
Another way to be able to build our formulas here. Once again I'll do an =,
but this time we'll build a simple calculation, a sum.
Now I'll switch over to the Wyoming sheet and although I do have a total,
sometimes we might not or we might want to calculate specific cells within a
worksheet. I will highlight that range that I wish to sum,
hit ENTER to complete this.
I still have a reference back to that original.
The difference is that I have now a sum and then of course that range of values.
Are links across workbooks can be very simple or may have some complex
calculations. Right now,
all of our links back to the source workbooks are pretty simple. That is,
we just see the workbook name in that square brackets and that's because source
workbooks are currently open.
That external reference will include the path to the workbook,
if that source workbook is closed.
I'll move over to that workbook and now I'll close it and notice now that we
have this entire path to whatever location that might be.
It might be, you know, Onedrive, it might be SharePoint,
might be on your network,
but you'll see the entire path when that workbook isn't open.
We are able to know where to find that information. At this point,
I'm going to save this workbook and close it. Next,
I'll come back and open it. Now,
this might be two days later and might be a week later.
So just keeping that in mind and we'll open up that sales summary.
This is what I wanted you to see.
When we open up a workbook that has links to other workbooks,
expect to see this security warning. At this point,
those automatic links have been disabled.
This is default in Excel to prevent automatic updating,
which could launch programs or install malicious viruses and linked files at
least if these are things that are might come from an external source.
Now, in most work environments,
really your virus scanning programs and other security measures are pretty well
designed to protect against these dangerous files. As a precaution,
the default here is that you would need to enable the content each and every
time.
This is a setting that might be something that you can change,
but it does depend upon whether or not this is allowed in your organization.
A lot of organizations will prevent you from changing these options in Excel.
Right now at this point,
I'll enable the content and now any updates that we might have would come
forward as well because Excel is updating or referencing to those links whether
or not those workbooks happen to be open.
Let's look at some of the things that can happen when we have linked workbooks.
That is how we can make sure that we maintain those workbook links.
I have this Wyoming file and it may not even be something that I realize is
happening at the time. That is, that's often where we run into trouble. And,
I'm working on it,
it happens to be open as well as that destination workbook,
our sales summary. And at some point,
perhaps I want to use it for a different purpose,
going to do a File > Save As,
and I will
call this Wyoming 2020.
Now my intention is to use it for some other,
planning, projection, for budgeting, but now I have renamed that.
Although it might not be my intention,
I have also updated the links that take place within this workbook,
our master workbook. Those are some of the things that can get us into trouble.
Let's look at how we can fix that. In this case,
I'm going to go ahead and close this file.
I'll also save and close the sales summary.
It's fine if I have the other files open. I don't need to have those open.
I'll close this one is well here. I'm going to change some values, so,
make this much more significant.
Now my total is updated. I'll update the worksheet if I need to.
This change is taking place in the source workbook.
Let's see what happens when we open up that sales summary.
I'm going to add one more challenge,
one more element to this linking project.
What happens if at sometime along the way a workbook is either renamed or moved
that will break the link? How can we correct that?
For this example, I've renamed the Colorado file.
The original workbook, Colorado, no longer exist.
Let's see what happens when we reopen that summary file.
First we'll see the security warning and this is another way that we might see a
warning that simply says that there are external links and if you trust them go
ahead and update. Now,
what happened is that Excel was able to spot, "Hey,
I can't find that link in Colorado." So we would have the opportunity to go
ahead and fix that,
but I'm going to cancel out of here right now because what I want to have you
see here is how you can fix it in other ways. That is,
if Excel doesn't catch it, how can you fix it?
How can you even just see if you even have links within a workbook?
We find this under the Data tab and then we have Edit Links and if this is
grayed out, that means that this workbook does not have links.
Here you will see then a list of the links that exist within this workbook and
they could be links in many, many different locations.
It doesn't have to be something as simple as this particular reference.
One of the things that we can do just to make sure that we have integrity with
those links is to select all of these,
and so just clicking the first one, hold down SHIFT,
click the last one and we choose Update Values. Excel is saying,
I don't know what happened where that Colorado file went,
but I can't find it. So now,
we can go ahead and select that file.
If that was not discovered, we'd also have the opportunity to Change Source,
so that would be another option for us to be able to refer to and point it to
the correct file.
This also is an option for us to open the source.
Right now, although it says that the values are OK on the Texas file,
it's not currently open.
Perhaps I want to make changes to it so I could open the source for that as
well. And I'm going to,
switch back to the sales summary and once again go to Edit Links.
The other problem we ran into before is, without realizing it,
we're now pointing to a different copy of that Wyoming file and that might not
be the one that we want. So here again,
we can change the source reference,
that Wyoming file,
the original one that we actually want to point to because perhaps that copy is,
has a completely different intention, might have new data to it.
And we can then close.
Notice here too that the New Mexico data has updated based upon some of those
changes that we made earlier.
Let's go back to the Edit Links for one more thing and that is what happens when
you're done, when you want to finalize it? Maybe it's
the end of a quarter, end of a year,
and you want to capture what it looked like at that point in time.
You might not necessarily want to lose all of these links.
You just simply want to have a copy that you could archive,
could distribute, could share.
It's also important if you're sharing it externally that you remove the links
before you send it as an email attachment because an outside recipient isn't
going to be able to work with or see those links so they'll run into different
error messages and things that will make it more cumbersome for them to be able
to work with that data. Here's what we do. First of all,
we can save the original. Then we'll do a Save As and, in this case,
I'll simply make this my final. Next,
we'll go to Edit Links.
Now our intention this time is that we don't want to have any links.
We'll go ahead and select all of those worksheets and then choose Break Link.
You will be warned,
which is good and now go ahead and break the links.
When you close,
notice that in the Data ribbon Edit Links is no longer available because this is
just raw data.
No links back to those source workbooks,
but we are able to then finalize or capture the data at this point in time.
And that gives you some options for how you can link data across multiple Excel
workbooks. For more tips on being productive with Excel,
head to TheSoftwarePro.com/Excel.
If this online training was helpful,
please like and comment on this video and subscribe to this channel.
This is Dawn Bjork, The Software Pro. Thanks for watching.