Microsoft Office Tutorials and References

In Depth Information

**Using cell references in formulas**

calculations, you must change the reference to cell B2 to an absolute reference before you

copy the formula.

To change the reference style, click the formula bar, click the reference to cell B2, and then

press F4. The result is the following formula: =$B$2*B5.

When you copy this modified formula to cells C6:C8, Excel adjusts the second cell reference

within each formula but not the first. In Figure 12-5, cell C8 now contains the correct

formula: =$B$2*B8.

Figure 12-5
We created an absolute reference to cell B2 before copying the formula.

Copying mixed references
You can use mixed references in your formulas to anchor a

portion of a cell reference. (In a mixed reference, one portion is absolute, and the other is

relative.) When you copy a mixed reference, Excel anchors the absolute portion and adjusts

the relative portion to reflect the location of the cell to which you copy the formula.

To create a mixed reference, you can press the F4 key to cycle through the four

combinations of absolute and relative references—for example, from B2 to $B$2 to B$2 to $B2.

The loan payment table in Figure 12-6 uses mixed references (and an absolute reference).

You need to enter only one formula in cell C6 and then copy it down and across to ill the

table. Cell C6 contains the formula = –PMT($B6,$C$3,C$5) to calculate annual payments

on a loan. We copied this formula to all the cells in the range C6:F10 to calculate payments

using three additional loan amounts and four additional interest rates.