Microsoft Office Tutorials and References
In Depth Information
Doing a Multi-sheet Calculation
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:
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
You should see this in the formula bar:
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:
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: