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.

Figure 2-14

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

=C5/C$10 automatically.

6.
Repeat Steps 4 and 5 as needed to complete

the formula.

7.

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.