Microsoft Office Tutorials and References
In Depth Information
Creating an Absolute or Mixed Formula Reference
cell C31 is an absolute cell address. As you can
see in Figure 2-13, when the formula is copied
from cell D22 to cell D23, the first cell address
in the formula is adjusted for the new row (C22
is changed to C23), but the second cell address is
not changed at all ($C$31 stays $C$31) resulting
in the formula =C23/$C$31.
Follow these steps to type a formula that uses
absolute cell addressing:
1. Click the cell in which you want the result of
the formula to appear. In the example shown
in Figure 2-13, you would click cell D22.
To prevent Excel from adjusting a cell address when a
formula is copied, make that cell address absolute.
2. Type an equals (=) sign.
3. Click the cell you want to reference in the
formula, or type its address. For example,
click cell C22. To make the cell address
absolute, you can type the dollar signs when
typing the cell address, or you can press F4
after typing the cell address to make that
address absolute. Since you don’t want to
make cell C22 absolute in this example,
continue to Step 4.
Absolute Cell References
Both simple and compound formulas can
contain absolute (or, as you learn in the
next section, mixed) cell references.
8. Copy the formula to other cells, such as cell
D23. Any absolute cell addresses used in the
formula are not adjusted. In Figure 2-13,
you can see that the formula is changed to
=C23/$C$31 when copied to cell D23 from
4. Type an operator such as + (addition),
– (subtraction), * (multiplication), or
/ (division) to indicate the type of calculation
you want Excel to perform. For example,
type / to indicate division.
5. Click or type the address of the next cell
you want to reference in the formula. For
example, type the cell address $C$31, or click
cell C31 and press F4 to change the formula
to =C22/$C$31 automatically.
Creating a Mixed Formula
Sometimes you do not want to refer to an
absolute cell address, but a mixed one, in which
only part of the cell address (the column letter
or the row number) is absolute. Typically, you
use mixed references when you want to copy a
single formula across a wide range. For example,
consider the sales worksheet shown in Figure 2-14.
6. Repeat Steps 4 and 5 as needed to complete
Press Enter to complete the formula. The
result appears in the cell.