Far-Flung Formulas: Working with Multi-Sheet Cell References in Microsoft Excel 2010

Now what about those multi-sheet cell references? As stated earlier, you can write formulas in which
cells in different worksheets contribute to that formula result. For example, if you allocate a separate
worksheet to each of three employees and enter their salary in the same cell on each sheet—say cell

A7—you can write a sum formula, anywhere, on any sheet, which totals the three salaries. (And in fact
the salaries don’t have to be entered in the same cell address on the respective sheets. They can be
situated in any cells.)

Doing a Multi-sheet Calculation
But let’s start with a simpler case—you want to add two numbers on different sheets:
1.   On Sheet 1 type 56 in cell D12. 
2.   On Sheet 2 enter 48 in cell B3. (Remember that the formula referencing these
two cells can be written on any sheet—even Sheet 3, but we’ll enter ther
formula on Sheet 1.)
3.   Click back on Sheet 1, onto cell A21. Once there, type the usual, and necessary,
= sign. 
4.   Click on cell D12, the cell in Sheet 1 containing 56. You’ll see:
=D12
Nothing new so far. Then:
5.   Type the + sign, simply because we’re about to add the contents of two cells. 
6.   Click the Sheet 2 tab, and click on cell B3. You’ll see (Figure 7–11):

Figure 7–11.  Writing a formula in Sheet1, including a cell in Sheet2
Note the budding expression in the formula bar: =D12+Sheet2!B3. By clicking on cell B3, the
formula supplements that cell reference with the Sheet2! prefix, and why? Because, remember—we’re
actually writing this formula in cell A21 on  Sheet 1, and so we need to indicate  which  cell B3 we’re now
referring to. After all, that cell could be in Sheet 1, Sheet 2, or Sheet 3—or any other sheet we might
have inserted into the workbook. As a result, Sheet2! is Excel’s way of notifying the formula that we
want to call upon the B3 in Sheet2. T he n  pr e ss En te r , an d we ’ r e  sn appe d back to She e t1, an d the

answer—104. And as with any Excel formula, its result will automatically recalculate, should either of
its two contributing values—the 56 and the 48—be changed. 
Now you may want to know why Sheet2! is attached to the second of the two cell references in the
formula, but nothing like it accompanies the first. That’s because the first cell reference–D12—appears
in the  same sheet as the one in which the formula was written, and Excel assumes, by default, that unless
the user indicates something to the contrary, all the cell references in a formula  and  the formula itself
emanate from the same sheet—and after all, isn’t that usually the case?
Now had we added these two numbers in a formula composed in Sheet3  instead—the sheet that
contains neither of the values we wanted to add—we would have clicked in a cell on Sheet3, typed =,
and then clicked on the two cells in Sheet1 and Sheet2 respectively. The formula would in this case
have looked like this:
=Sheet1!D12+Sheet2!B3
See why? In this case  neither cell shares its worksheet location with that of the formula itself, and
so Excel needs to specify the worksheets on which  both ce l l s a r e  posi ti on e d. 
So that’s the general approach to multi-sheet cell references in a formula—enter the = sign and
the mathematical operation (or function) you wish to perform, and then click on the sheet and the
cell(s) on that sheet you wish to incorporate into the expression. And if you need to reference a range
of cells from another worksheet, you can just type = in your current destination cell, click on the first
cell in the range on the sheet from which you wish to copy, then drag the appropriate range length, and
press Enter. All those cells from the source worksheet are now referenced here, because they’ll all be 
accompanied by the Sheet1, Sheet2, etc., identifier.
Now here’s a neat variation on that theme. Suppose you want to add a group of cells, all of which
ha v e  the  same address  in a collection of different worksheets—for example, values in the cell A3 in
Sheets 1, 2, and 3. Let’s try it:
•  In those three cells, enter 86, 72, and 4. 
•  In cell C19 on Sheet1, enter =SUM( . 
•  Then click on cell A3 in Sheet1, hold down the Shift key, and click on the Sheet3
tab. 
You should see this in the formula bar:
=SUM(‘Sheet1:Sheet3!’A3)
Press Enter, and your answer—162—flashes into the cell (note you don’t have to type the close
parentheses. Pressing Enter automatically supplies it). With this technique, Excel automatically
references the same cell in all the selected sheets—and by tapping the Shift key we’ve really grouped
all three sheets (this method works with any Excel function, not just SUM. In fact, once you press Shift
and click on the last of the sheets you want to reference, you can go ahead and drag any range you
want. That range, with precisely the same coordinates, will be selected on all the sheets, and all will be
calculated into the formula. Thus: 
=AVERAGE(‘Sheet1:Sheet3!’A6:B14)
will calculate the average of all the values in the A6:A14 ranges on the three sheets.
Note in addition that a named range on one worksheet can be directly referenced on any other
worksheet, without any concern for relative cell reference complications (note: this assumes the
range’s scope is the Workbook, which is the default in any case. See the Appendix on range names.)
Understand a key point here-that the cell coordinates of a named range don’t change—they’re treated
as absolute references (a point elaborated in the appendix) by default. Thus you can write:
=MAX(Scores)
on Sheet1, even if the range Scores is on Sheet2