Microsoft Office Tutorials and References
In Depth Information
Copying Formulas from Cell to Cell
Referring to cells in different worksheets
Excel gives you the opportunity to use data from different worksheets in a
formula. If one worksheet lists sales figures from January and the next lists
sales figures from February, you can construct a “grand total” formula in
either worksheet to tabulate sales in the two-month period. A reference to a
cell on a different worksheet is called a 3D reference.
Construct the formula as you normally would, but when you want to refer
to a cell or cell range in a different worksheet, click a worksheet tab to move
to the other worksheet and select the cell or range of cells there. Without
returning to the original worksheet, complete your formula in the Formula
bar and press Enter. Excel returns you to the original worksheet, where you
can see the results of your formula.
The only odd thing about constructing formulas across worksheets is the
cell references. As a glance at the Formula bar tells you, cell addresses in
cross-worksheet formulas list the sheet name and an exclamation point (!) as
well as the cell address itself. For example, this formula in Sheet 1 adds the
number in cell A4 to the numbers in cells D5 and E5 in Sheet 2:
=A4+Sheet2!D5+Sheet2!E5
This formula in Sheet 2 multiplies the number in cell E18 by the number in
cell C15 in Worksheet 1:
=E18*Sheet1!C15
This formula in Sheet 2 finds the average of the numbers in the cell range
C7:F7 in Sheet 1:
=AVERAGE(Sheet1!C7:F7)
Copying Formulas from Cell to Cell
Often in worksheets, you use the same formula across a row or down a
column, but different cell references are used. For example, in the worksheet
shown in Figure 3-9, column F totals the rainfall figures in rows 7 through 11.
To enter formulas for totaling the rainfall figures in column F, you could
laboriously enter formulas in cells F7, F8, F9, F10, and F11. But a faster way
is to enter the formula once in cell F7 and then copy the formula in F7 down
the column to cells F8, F9, F10, and F11.
Search JabSto ::




Custom Search