here I'll show you four tricks for
linking data between multiple workbooks
in Excel I'm gonna start off really
simple by linking individual cells and
then ranges then moving to formulas and
functions between workbooks then show
you how to consolidate and combine
multiple sets of data between workbooks
as well as linking those sets of data
and then at the end we'll step a little
bit into what's called power query which
is just a way to import the data in a
slightly more powerful way before we
start check the video description and
click the link to teach excel so you can
download the files for the tutorial and
follow along and make sure to subscribe
and accept notifications so you can see
all the new tutorials first things first
whenever you are linking data between
multiple workbooks you want to have both
of them open at the same time it'll make
your life so much easier now here I've
got two workbooks this is the workbook
that has the data that we want to get
I've got it separated onto multiple tabs
and I'm gonna show you different ways to
get the data so over here is the
workbook where we want to pull the data
into let's start off very simple let's
just get one cell of data here so let's
say that we want to get the total sales
amount from over here from this cell
right here all we have to do go to the
workbook where we want the data to
appear type an equal sign just like a
regular formula or function don't hit
anything else on the keyboard navigate
to the other workbook select the desired
cell you'll notice in the formula bar
that it fills in with a bunch of stuff
up here and I'll explain that in a
moment but it fills in with the formula
bar just like it would if you were
selecting a cell on another worksheet in
the same workbook so that's how you know
everything is working just fine when it
fills in some stuff up here once you've
selected the correct cell simply hit
enter it'll take you back to the first
workbook and we've got the number five
nine eight one 100 now if we double
click this or looked at the formula bar
you're gonna see a really long formula
so let's talk about this it's very
simple kind of it's just an addition to
our normal cell reference so let's start
at the far right here we have our normal
cell reference c14 by default it'll be
absolute references so with the dollar
signs then we have the worksheet that
it's from so cells and ranges now I've
named these worksheets the same between
the two workbooks so that you can more
easily see where the data comes from but
they don't have to be the same so this
cells and ranges right here doesn't
apply to this one it applies to the one
in the other workbook they could be the
worksheet names don't have to be the
same basically so this is the worksheet
name and over here the one extra
addition within the brackets the name of
the other workbook so you can see this
one is four tricks for linking workbooks
in Excel - one and the other one is - -
so it's workbook name worksheet cell
reference and that's how it's gonna look
for the rest of the tutorial even when I
show you different ways to link this
stuff now this is how to get an
individual cell but what if you wanted
to do an entire range let's go ahead and
delete this do it again equal sign go to
the other workbook select the cell that
you want to pull in let's just pull an a
one hit enter and now just like if it
was in the same workbook what we can do
is we can drag and move around the cell
to pull in data from other cells however
if I do it right now it's an absolute
reference so nothing will change the
formula if we look to the formula bar
right now it's the same for all of them
so what you want to do go ahead and
remove the dollar sign here and the
dollar sign here hit enter now it's
actually really cool copy it down and we
can even copy it across and you're going
to pull in all the data okay so there's
nothing in D let's go ahead delete that
and you have all the data from the other
worksheet and the other workbook it's so
easy and the moment that we change
something in the other workbook
let's say we want to change this region
from e to n hit enter go to the other
workbook
he has been changed to n even if we
change titles up here everything will
automatically update all we've done is
just a direct link between all of these
cells so you can see this was the
original one here a one down here it
says a two at the end and so on a three
now if we go to the right
this is b1 and then it'll say b2 b3 b4
and so on and we could even let's find
the sales number right down there so we
could go ahead and drag it down like
this or drag it for the entire worksheet
get the sales number and go ahead and
delete these guys now this isn't really
the best way to get data from another
worksheet but it's one way that you can
do it and it's pretty simple I'm pretty
straightforward and pretty difficult to
forget so now all of these values are
simple formulas however I do not
recommend that you go ahead and edit
this like you would a regular cell just
redo the formula itself with the other
workbook open now I showed you this is
what the formula looks like right okay
what happens if I close the other
workbook I'm gonna show you this now so
you don't get freaked out when you see
this in person let's zoom in okay here
it is name of the workbook now let's go
to the other one close it
sure let's save that now look at
looks kind of scary right and you may
think oh my gosh what happened all that
it did now that the other workbook is
closed
it needs a way to reference it correctly
so it has put the file path the
directory listing how you will get to
the other file on your computer it has
put that in front of the file name so
you see it still follows the same format
we have cell reference worksheet
reference workbook reference and now it
just has the location on your computer
that reference right here so don't let
that freak you out and definitely don't
change this by hand or it can cause a
lot of problems very easily because
Excel kind of tries to interpret all
this on its own so when you move the
files around your computer it'll
oftentimes update for you without a
problem
so it's better to rely on Excel sort of
figuring that out as opposed to you
changing it all by hand it's very easy
to mess up especially in a large scale
but now once I go ahead and open the
other one then we go back here you can
see that it has changed how it was
before so it just starts with the name
of the workbook instead of the directory
path in front of it so Excel is going to
automatically change that for you don't
let it confuse you now let's move to the
second thing which is going to be using
formulas and functions across workbooks
as you can imagine it follows the same
pattern that we just did here so I'm not
going to spend as much time covering
that this was pretty much for the
foundation of it let's go to the
formulas tab and over here formulas tabs
well now let's say we have some parts
and we want to use a vlookup to enter a
part number and the other workbook and
get the total so total stock from here
so very simple let's just tie part and
stock we enter our vlookup here equals
vlookup lookup value right here comma
table array now remember have both
workbooks open at the same time so once
we're on the table array argument simply
navigate to the other workbook and
continue entering the formula like you
normally would
select the table you can see it filling
in up here with name of the workbook
worksheet and reference for the cells
now don't go back to the other workbook
you've selected your table array it's
good we have our arguments here filling
it in the formula bar just hit a comma
and then we can fill in for the column
index which in this case will be 4 and
then comma range lookup exact match
false so finish entering your formula
here in the other workbook and then hit
enter it's gonna take you back to the
first one and it's gonna have the
formula filled in right here so you can
see for any reference to the other
workbook just like we showed you on the
first worksheet tab you've got first the
workbook name worksheet cell reference
so let's type in a part number and see
what happens
stock 117 let's test that out stock 117
let's try out a SC - 3 should be 57 good
practice to test out your work perfect
so you can see that is super duper easy
and all the other things I just told you
earlier in the tutorial also hold true
as far as the cell references so if I
close the other workbook this will have
the directory path in front of it once
you open it again it'll come back the
references automatically are going to be
absolute so I'll just pay attention if
you want to remove the dollar signs from
them you will have to go ahead and do
that by hand afterwards but otherwise
everything else is exactly the same it's
really really nice now let's get into
the fun stuff let's move on to the
consolidation tab so here what I want to
do is I want to take tables of similar
data and I want to combine them and I
want to have them all linked here so
let's say that I've got sales reports
for different divisions different months
and you want to combine it all so you
can see it in one sort of master report
so let's say over here what I've got
I've got two worksheet tabs just so you
can see how versatile it'll be and this
is our table of data we have sales this
could be from
say three different stores so parts and
then months and this is the sales data
here from one store here is it from
another store over here and the next tab
consolidate to let's say this is from a
third store now usually you wouldn't
have two of them on the same worksheet I
just want to illustrate that you can get
data from the same worksheet twice
without a problem so you'll see what I
mean in a moment what we want to do is
we want to sum all of this data so we
can see it in one worksheet in a
separate workbook first go to where you
want your data then go to the data tab
and over here in the data tool section
click consolidate now the first thing is
you want to tell this little box what
you want to do with the data so what
it's gonna do when it consolidates its
gonna do something with all the numbers
do you want to add the numbers together
usually that's what you're gonna do but
you can also have counts average max
mins products you can do all sorts of
things down here now the next thing that
you want to do is to enter the reference
so you want to enter the table reference
most examples of this use a worksheet
table reference that's in the same
workbook so all you do here is you click
in here click the arrow and then select
it from the same workbook or a different
worksheet or somewhere in there since we
want to get it from another workbook
actually it's not that different click
in here with the reference don't click
browse it's gonna make your life more
difficult click in here with a reference
make sure you have both workbooks open
click the arrow now navigate to the
other workbook you'll see this little
guy pop over with you select the table
and when you select the right one you
can hit enter or you can click the
little button right here and it will
automatically have entered the big huge
reference don't do anything up here just
click add now you have your first
reference now let's go up here again
click the blue button let's select the
second data table may see it doesn't
matter if it's on the same worksheet in
a different workbook or not it's all the
same let's hit enter this time
click Add now let's go up here again and
remember still this whole time we are in
the second workbook we go to consolidate
- let's click over here select it and
hit add now we have three references in
the separate workbook it's all listed
right here now if you accidentally
entered one that you don't want go ahead
and click delete so you can select it
and click delete but once you have all
the data tables selected go down to use
labels in so if we're using labels in we
want to click top row and left column
because we want the columns to contain
the same data for January February March
we want to get the January data with the
January data and then for the part data
we want each part to combine with the
same part in all the other tables
so basically top row left column you're
usually gonna select both now let's not
click this one just yet but obviously
that's how you create links to your
source data so now hit OK let's go back
to the first one and you can see we have
all of our data consolidated nicely and
neatly here in the other workbook so for
January ASC 1 we have 31 13 let's go
verify that one thousand eight one
thousand two thousand and eight plus one
thousand one hundred five perfect now
one thing to note is that all of the
data that was just consolidated is just
data there are no links so it's taken
everything over nicely and neatly but
nothing is going to update when it
updates in the other workbook just
static data so let's go ahead and fix
that we do the same thing that we did
before go to your empty worksheet data
consolidate the lovely thing about this
feature is it will save the last thing
that you did so we already have all of
our references saved in here we don't
have to go back and do that again use
labels in top row left column this time
create links to source data hit OK
and you'll see it looks quite a bit
different so we have the same data we
can make sure that works but this time
double-click and we have a sum but the
sum is on this worksheet so this is
pretty interesting what this is done
let's go to the first row and click the
plus sign is it has imported the data
using simple cell references like we
used in the very first example we have
the workbook worksheet and the cell
reference right here so it's imported
that from each data table 1 2 3 and then
it's used a sum to sum that data
together so it's basically just
automating what you yourself could do by
hand and then it's used this grouping or
outlining feature to hide the data so
that you don't see it by default so it
looks like you have a nice clean neat
little table but if you want to see
where you got the data from you can
click the plus and you can see the name
of the workbook and then the formulas
themselves right here
so if I click too it's gonna expand the
selections for everything and you can
see this is actually what it imported
most of the time you don't want to look
at all this stuff so you can go ahead
and click one to collapse all of it and
you've got your nice neat little table
sometimes I will note if you save your
workbooks and then you close them and
you reopen them Excel is gonna give you
a notification asking if you want to
enable links or refresh data and you
should allow that so long as you know
that your workbook came from you or from
someone you trust so it's safe because
sometimes the security settings will
turn off data connections so if you
don't see your data updating it's
because the connections have been killed
or just prevented due to some sort of
security concerns now by this point in
the tutorial you know how to link data
between multiple workbooks and multiple
worksheets how to use formulas and
functions on them how to make cell
references relative and copy it all
around and do all that fun stuff so what
I'm going to show you for the last trick
is a more automated way to pull in data
from another worksheet now for these
examples it's going to be like using
fighter jet - co2 the corner store but I
think it's important to just give you an
idea just to show you how you can do it
and then you can go on and learn more
about it so let's go over here to the
final worksheet a simple simple data set
and we want to pull it into the other
workbook so go to the data tab get data
from file from workbook then you
navigate to the workbook you find it on
your computer double-click and what it's
going to do is it's going to analyze the
other workbook so it's going to come up
with this window it's gonna show us all
the worksheets in the other workbook
let's go ahead and get it from
worksheets you'll notice that it shows
you all of the data within the worksheet
so if I click these other ones you'll
see all the data listed there
worksheets it works best if your data
set up is a simple data table so if you
don't have a bunch of extra titles and
all that jazz so we click worksheets and
now what you can do is you can click
load which is the super simple way to
just pull it into your workbook or the
more powerful way where you can do so
many cool things that are beyond this
tutorial is to hit edit when you do that
it's going to bring up the query editor
and that's going to allow you to do so
many things you can actually decide
which data you want to bring in you can
transform it you can change it you can
remove columns remove rows split the
data you can do pretty much all of the
data analysis that you would do in your
worksheet after you imported the data
you can do it here and then you can save
it as a query so that each time the data
is refreshed Excel will go and pull that
raw data will run all your functions
your formulas your data manipulation you
want automatically and present it in
Excel exactly how you want the results
to be so it's an amazing tool so I
highly recommend that you play around
with this and I will show you how to do
all sorts of fun things with this in
another tutorial it's way beyond this
tutorial but once you've got the data
that you want how you want it click
close and load it's the same as clicking
the load button on the previous screen
and it will take you to your worksheet
it'll open up a new one by default and
it will pull in the data notice that it
will pull the data in like a table so it
is a data table set up here so that
means when you click anywhere in here
you're going to have the traditional
table tools tab where you can do all
sorts stuff at the table but you're also
going to have the query tab we're just
going to allow you to do lots of fun
stuff and if you want to get back to the
window where you could edit and change
how the data comes into here simply
click Edit if you want to refresh the
data click refresh and then you may
notice that over here we have a queries
in connections window it's going to
allow you to do a lot of stuff so hover
over it go down here click Edit and it
will take you back to the query editor
now if you lose this window if you click
close and you're over here how the hell
do I find my queries
go to the data tab and then you can just
click queries and connections and it
will pop back up now all this data is
linked to the source data but since we
use this method access it we can do so
much more with the data then just basic
linking linking of the cells and ranges
so that's why you may want to use the
more powerful method of going to the
data tab get data from file from
workbook instead of just linking cells
and ranges and formulas however this
feature and the ability to import it
with the queries and do all that jazz it
wasn't available in earlier versions of
Excel so it's default in Excel 2016 and
later but it's going to be an add-in I
believe in Excel 2010 to 2013 and in
2007 it might be an add-on or you have
to use one of the other methods to
consolidate the data so this won't
always be available unless you have a
later version of Excel and those are the
four tricks for linking data between
multiple workbooks in Excel I hope you
liked the tutorial if it was helpful
don't forget to give it a thumbs up and
make sure to subscribe and accept
notifications so you can see all the new
tutorials