Microsoft Office Tutorials and References

In Depth Information

**Partially Fixed References**

Copying Formulas

With a partial fixed reference, Excel updates one component (say, the column part)

but not the other (the row) when you copy the formula. If this sounds complex (or a

little bizarre), consider a few examples:

• You have a loan rate in cell A1, and you want all loans on an entire worksheet to

use that rate in calculations. If you refer to the cell as $A$1, its column and row

always stay the same when you copy the formula to another cell.

• You have several rows of loan information. The first column of a row always

contains the loan rate for all loans on that row. In your formula cell, if you refer

to cell $A1, then when you copy the formula across columns and rows, the row

changes (2, 3, 4, etc.) but the column doesn’t (A2, A3, A4, etc.).

• You have a table of loan rates organized by the length of the loan (10-year, 15-

year, 20-year, etc.) along the top of a worksheet. Loans in each column are

calculated using the rate specified at the top of that column. If you refer to the rate

cell as A$1 in your first column’s formula, then the row stays constant (1), but

the column changes (B1, C1, D1, etc.) as you copy the formula across columns

and down rows.

Tip:
You can quickly change formula references into absolute or partially fixed references. Just put the

cell into edit mode (by double-clicking it or pressing F2). Then, move through the formula until you’ve

highlighted the appropriate cell reference. Now, press F4 to change the cell reference. Each time you press

F4, the reference changes. If the reference is A1, for instance, it becomes $A$1, then A$1, then $A1, and

then A1 again.

Up to speed

Creating an Exact Formula Copy

There’s another way to copy a formula that prevents Excel

from automatically changing the formula’s cell references.

The trick is to copy the formula itself rather than copy the

whole cell (which is what you do when performing a basic

copy-and-paste operation on a formula).

3. Select all the text in the cell. You can use the mouse,

or you can use the arrow keys (just hold down Shift as

you scroll from the beginning to the end of the cell).

4. Once you’ve selected the complete formula, press

Ctrl+C to copy it.

5. Press Esc to leave edit mode once you’re finished.

6. Move to the new cell, and then press Ctrl+V to paste it.

Keep in mind that when you use this approach, you create

an exact copy of the formula. That means this technique

doesn’t help in situations where some cell references need

to be absolute, and others need to be relative.

The process takes a few more steps, and it lets you paste

only one copy at a time, but it can still come in handy if

you don’t want Excel to use relative references. Here’s how

it works:

1. First, move to the cell that contains the formula you

want to copy.

2. Place this cell in edit mode by double-clicking it or

pressing F2.