Microsoft Office Tutorials and References
In Depth Information
Creating an Absolute or Mixed Formula Reference
Follow these steps to type a formula that uses
mixed cell addressing:
1. Click the cell in which you want the result of
the formula to appear. In the example shown
in Figure 2-14, you would click cell C13.
2. Type an equals (=) sign.
3. Click the cell you want to reference in the
formula, or type its address. For example,
click cell C5. To make the cell address a
mixed reference, you can type the dollar sign
in front of either the column letter or the
row number when typing the cell address,
or you can type the regular cell address and
press F4 two or three times to cycle through
various versions of the address: regular cell
address, absolute address, mixed cell
reference with an absolute row, or mixed cell
reference with an absolute column. Since
you don’t want to make cell C5 absolute in
this example, continue to Step 4.
Use a mixed reference to copy a formula
throughout a range.
The formula in cell C13 calculates Thom’s
contribution to the total Nikon sales in July. After
entering this one formula, you can copy it to the range
C13:E17 to compute other averages you want.
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.
Here’s how it works: the formula in cell C13 is
=C5/C$10. The cell address C$10 is a mixed
address; the row number 10 is absolute, but
the column letter C is not. When you copy this
formula to cell C14 (one row down), it becomes
=C6/C$10. This formula takes the Nikon sales
amount for Kiesha and divides it by the total
sales for Nikon cameras and accessories in cell
C10 to compute her contribution to the total
Nikon sales. When you copy the formula to cell
D13, it becomes =D5/D$10 (the column part of
the second cell reference changes but not the
row). This formula takes the Olympus sales
amount for Thom and divides it by the total
sales for Olympus cameras/accessories in cell
D10 to determine Thom’s contribution to that
total. Thus, you can enter one formula in cell
C13, and because it uses a mixed reference,
easily copy that formula throughout a range to
instantly create the formulas you need.
5. Click or type the address of the next cell you
want to reference in the formula. For example,
type the cell address C$10, or click cell C10
and press F4 twice to change the formula to
6. Repeat Steps 4 and 5 as needed to complete
Press Enter to complete the formula. The
result appears in the cell.
8. Copy the formula to other cells, such as to
the range C13:E17. Any mixed cell addresses
used in the formula are only partially
adjusted. For example, the formula in cell C13 is
=C5/C$10, and when it’s copied to cell E17,
it is changed to =E9/E$10.