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 focus on how to link several worksheets together within one Excel
workbook.
Do you have a workbook with related data such as separate worksheets for
individual months or different locations?
How do you create a summary of these detailed worksheets?
The most common type of linking in Excel is also the easiest. That is,
a reference to a value in a different worksheet within the same single workbook.
Here's an example. In this workbook,
we have individual worksheets for each of the states to be able to easily track
this information without having to navigate to each one of those worksheets.
We'll create a summary of that data.
Now linking is a lot easier than often we will hear.
In this case,
I want to see the total for Texas and I'll start as we would with any other
formula, and that is with an equal sign.
Next we'll navigate to Texas and then specifically to where that total exists.
Now we could add a lot more to this. That is,
this could be a calculation with multiple cells,
but if I simply wanted to see this total, all I need to do is hit ENTER.
That's it.
So now we have this total and it will update as this information changes.
Let's look at what we just created here in the cell.
The reference is equal to the worksheet,
so we see the tab name, the worksheet name, Texas,
exclamation, and then the cell where that data resides.
Now should we insert additional rows,
this will update just as any other formula would. Let's try this again.
Now I want to display the total for the Colorado data.
I could do it the same way as before,
but we'll put a little bit of a twist on this and that is start with once again
with equals and then SUM(.
Next, we'll navigate to Colorado. Even though we have a total,
there might be times when you don't or you want to perform a different
calculation. In this case,
I'll simply highlight the range of values that represent that total and then
complete with an ENTER.
So I still have a total that will update as the information updates.
Notice once again that this references is to the worksheet name,
so the worksheet name, exclamation, and then the range of values.
Let's repeat this for the next two states. We'll simply do an equal to,
and reference the total in that specific worksheet.
Now let's explore something that happened here with the New Mexico reference.
Notice those single quotes in Excel.
We'll see the single quotes around any reference where there are spaces in the
name. This is called a character string.
This is how we refer to it either manually or how Excel created it for us to
ensure that this all stays together.
That is one of the advantages that we have by using the pointing method.
That is where we simply click to and navigate to that location.
Excel helps us to build these formulas.
And so although we could have typed all of in directly,
it's a big time saver for us to have Excel help us out.
There's nothing wrong with this. In fact,
all the other entries could also be surrounded by those quotation marks,
but they would not be necessary unless that worksheet reference or name changes.
Now of course we can total this and I'll do so using a quick keyboard shortcut,
and that is ALT and the equals key.
You might know that as a keyboard shortcut that automatically builds our SUM
function for us.
And we don't have to make any updates to it because Excel captures the closest
range, which happens to be correct. And so I simply hit ENTER to accept that.
And that's all we need to do to create links within one workbook between the
different worksheets in that workbook.
Although we don't need it for this project,
let's look at one other thing that we could do here as well.
And that is we can create calculations between the different worksheets.
So just to give you that as a reference,
I'll move over here and I want to create a calculation that adds the values in
Texas and Colorado together. So to do that, I'll simply do an equals.
Now I know I have these values right here in the sheet,
but maybe I'm in a different location. I don't have that available to me.
So we'll start it once again with that pointing method. I'll go to Texas,
here's the total,
and now I'll add a plus and now navigate to Colorado,
select the total and hit ENTER.
So there we have that total or that calculation where we're adding those two
values together.
Even though it isn't required or needed for this particular workbook,
I wanted you to be able to see that there's a lot of flexibility about how we do
the calculations.
You're already likely building lots and lots of calculations and formulas.
The only difference here is that the source,
the information is simply stored somewhere else besides the current active
workbook. Now should our data update in any way,
so for instance, we insert new information.
I'll move here to the Texas sheet and I'll insert a new row
and we'll, let's just say that we have some new information here.
I'll copy this down and,
I've got some new data
and for expediency,
I'll simply ENTER this in and I'll copy from the previous cell above that.
A quick keyboard shortcut for that would be CTRL+D for duplicate.
And so now this has updated our total.
We have added some more information to this.
This also could happen if we changed the actual data within this,
but when we come back to the summary,
that information has also updated.
Now you've seen how to link multiple worksheets together within one Excel
workbook. 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.