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
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.