Microsoft Office Tutorials and References
In Depth Information
Partially Fixed References
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
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