If you use Excel,
you'll inevitably need to link sheets in a workbook.
The most common way to link cells from different sheets
is by using the equal sign.
To do this,
type the equal sign where you want to create your link,
go to the sheet that has the cell you want to link to,
and press Enter.
Because the sheet we are linking to
is in the same workbook,
the link created is a relative link.
You can tell this, because there are no dollar signs
in the link's cell formula.
This means that to link to the other values in Group 1,
I can drag this down
to link to the cells below the original cell
that we linked.
Then drag it to the right to include the cells to the right.
A faster way that you can create a link
is by using the Paste Link feature.
So go to the sheet with the cells
that you want to link to,
copy them, and let's go back to our Summary sheet.
Place your cursor in the location where you want your link,
right-click, and, in Paste Special,
you can either select the icon with the links
or click Paste Special and select Paste Link.
And you can see up here in the Formula Bar
how all of these values are actually linked
to those same cells in the Group 1 tab.
So something to note here is that the formatting
on our linked cells is not the same
as in the original values that we linked to.
So just know that you'll need to take that extra step
to format your linked cells.
You can also create links to named ranges.
So let's go over to the Group 1 tab,
and we'll select all of the data,
and then up here in the Name Box,
we'll just call it Group1.
Press Enter, and then each time
we select the Group1 named range,
all of this data will be selected.
So once you've done that,
you can go back to the Summary tab,
and type the equal sign and begin typing the named range.
So here's Group1.
We can select that,
press Enter to populate all of the contents
of that named range.
So something to note.
This type of linking works a little different
than the other methods.
With my active cell inside the named range
that we linked,
notice that you see a light blue border
around those linked cells.
To keep the cells in the named range together,
this type of linking actually creates a group
that can't be separated.
Notice if I try to delete any of them,
I'm not able to do so.
However, if I try to change any of these values,
I lose the entire linked range,
and I end up with an error.
You can also link to other cells by creating a hyperlink.
So this won't show you the values
from the cells that you're linking to,
but it will let you jump to that location.
To do this, just place your cursor
wherever you'd like to place your hyperlink,
and right-click and select Link,
or press Control + K from your keyboard.
And then here, let's go to a Place in This Document.
And from this section,
you can select either the sheet that you'd like to link to,
or if you have a defined name,
you can select that and jump straight to that section.
But for this example,
let's just select the Group 1 sheet.
So whenever you select a sheet to link to,
it's going to default to jumping to cell A1 of that sheet.
And you can see that here in the Cell Reference.
If you want to jump to a different location
in that sheet, you can update that cell location here.
But I'm gonna leave that as is for now.
And then up here in the Text to display,
you can either leave this as is,
or you can create a friendlier name for your link.
So we'll say Group1 Link.
And then press OK when you're done.
And now whenever you click on that link,
you'll automatically go to that location.
So what if instead you want your links
to total the values for the groups
or do some other calculation?
We can, again, link the cells
by using the equal sign,
and then adding a plus sign between each.
So we link to the first value in Group 1,
add a plus sign,
then go to Group 2,
and select the corresponding value
to create the calculation,
and then press Enter.
Let's expand this a little.
So then, like before,
we can drag this over
and down to populate the rest of the linked calculations.
So instead of using a bunch of plus signs,
you can also use a function
to calculate your linked values.
For example, let's type =SUM,
open parentheses,
in Group 1,
select the first cell, add a comma,
go to Group 2,
and select the corresponding cell there.
And you would keep doing this if you have other sheets
that you're combining,
and press Enter when done.
And then, like before,
we can just drag this over and down
to populate the rest of our calculations.
Another way you can sum linked values
is by creating a 3D formula.
So 3D formulas reference the same cell
or range of cells
across multiple sheets.
So if all the values you are summing
are in the same cell locations
in all the sheets you're referencing,
then you can use a 3D formula.
Take a look.
This time we'll type =SUM again,
we'll go to the Group 1 tab,
select the first cell,
and, while holding down the Shift key,
select each of the other tabs
that you want to include in the calculation.
So I only have one tab here,
but if you have more, select those.
And then press Enter when done.
And, again, we can copy over and down
to populate the rest of our calculation.
You can also calculate values across sheets
by consolidating them.
To consolidate, you'll need to make sure
that the cell ranges you're selecting
are laid out the same way.
They don't have to be in the exact same location
like they did for the 3D formula,
but they do need to have the same type of data
and layout.
Let's delete these.
And you'll find the Consolidate feature in the Data tab
under Data Tools section.
And, here, click Consolidate.
So the first thing you need to do
is select the type of calculation that you want.
For this example, we're using the Sum,
and then we need to select the cell ranges
that we're going to be consolidating.
Here in the Reference section,
I'm going to go to Group 1,
and select the values and the labels.
Come back here, click Add,
come to Group 2, and select the second group of data
to consolidate and click Add to that.
So next, if we want for Excel
to label the results for us,
we can select that here.
I know I want to use the headers along the top of my data,
and there was no labels in the left column,
so I'll leave that blank.
Next you need to select if you want
to create a link to the original data or not.
If not, click OK,
and you'll only see a result
of the calculation that you selected.
So the Consolidate feature saves your consolidation.
So you can go back to it,
and if we want to create links to the source data,
we can go back and do that.
Now, when you select to link the source the data,
your results will be automatically grouped.
And once you have your results,
you can then ungroup each
to see what cells the links are pointing to.
For more tutorials like this, click on these links.
Thanks!