Microsoft Office Tutorials and References

In Depth Information

**Using Mixed References**

Figure 3-7

Results of formulas with absolute references

absolute reference

to cell D5

monthly income

from Janu
ary to May

◗

4.
Click each cell in the range D19:G20 and verify that the formulas =$D$5 and

=$D$6 were copied into the appropriate cells.

Using Mixed References

A formula can also include cell references that are mixed. A mixed reference contains

both relative and absolute references. For example, a mixed reference for cell A2 can

be either $A2 or A$2. In the mixed reference $A2, the column reference to column A is

absolute and the reference to row 2 is relative. In the mixed reference A$2, the column

reference is relative and the row reference is absolute. As you can see, a mixed reference

“locks” one part of the cell reference while the other part can change. When you copy

and paste a formula with a mixed reference to a new location, the absolute portion of

the cell reference remains ﬁ xed and the relative portion shifts.

Figure 3-8 shows an example of using mixed references to complete a

multiplication table. The ﬁ rst cell in the table, cell B3, contains the formula =$A3*B$2, which

multiplies the ﬁ rst column entry (A3) by the ﬁ rst row entry (B2), returning the value 1.

When this formula is copied to another cell, the absolute portions of the cell references

remain unchanged and the relative portions of the references change. For example, if

the formula is copied to cell E6, the ﬁ rst mixed cell reference changes to $A6 because

the column reference is absolute and the row reference is relative, and the second cell

reference changes to E$2 because the row reference is absolute and the column

reference is relative. The result is that cell E6 contains the formula =$A6*E$2 and returns the

value 16. Other cells in the multiplication table are similarly modiﬁ ed so that each entry

returns the multiplication of the row and column headings.