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.