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 explore how to link Excel worksheets together with three dimensional
links.
Creating a 3-D formula is another way to be able to create links to data in
other worksheets within the same workbook.
The key with this is that a 3-D formula requires that each worksheet contains
the exact same type of data,
such as a worksheet total in the same location for each worksheet in the 3-D
reference. Let's look at these examples for a moment.
So all of this product information,
these same labels appear in the exact same locations in each of these sheets.
So should I move to the other ones, South East, West,
not only the same products,
but also we're tracking this same type of data, in this case,
quarterly information for each of these locations.
So that is key in building a 3-D formula.
There won't be as many applications for it,
but it still might be a way for you to quickly be able to summarize information
by building a different kind of link.
Here's how you build a 3-D reference.
I'll move to our summary or master worksheet. Once again,
has the same structure as all of the others.
Then we'll move to the first cell where we want to build the formula.
This is going to be a sum. We'll begin it with an =sum,
open parentheses,
and then we'll move to and navigate to that first sheet.
We'll go to North.
I'll click on that reference cell that is in the North sheet and the cell here
B5. Now, the trick is, hold down SHIFT,
click on the last sheet of that range of worksheets.
Notice that they're all lit up or selected.
So now we're saying that we want to do a sum from North through West of the data
found in this cell B5.
I'll ENTER to complete this and then we'll look at the structure of this
formula. So it's simply is =SUM,
the range North through West exclamation to separate that worksheet from that c
ell and then close parentheses because our logic can be maintained across the
worksheets.
We actually can copy this so we don't have to build it one at a time. Well,
that's a really nice bonus. I'll simply use the AutoFill handle in this case,
drag it down and then drag it over.
And then finally here. Now this isn't always a luxury that we have,
but I'll highlight all of these values here and highlight the row below and to
the column to the right and now we'll simply hit the AutoSum button.
Excel is smart enough to be able to populate our SUM formulas into each of the
selected cells.
One of the cautions that we do have with this 3-D formula approach is that right
now the range is North through West. So should we move, for instance,
to the South sheet and drag it outside of that range?
Perhaps I'll move it to the left of North,
drop that in place.
We have still have a range of North through West,
but it excludes the South values at this point.
So that's one of the cautions that we have if you find a place for being able to
take advantage of 3-D formulas.
Now you've seen how to link Excel worksheets together with three dimensional
links. 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.