Microsoft Office Tutorials and References
In Depth Information
Gaining the Upper Hand on Formulas
3. Press Enter.
Cell C2 now returns the value 200.
4. If C2 is not the active cell, click it once.
5. Press Ctrl + C, or click the Copy button in the Clipboard category on
the Home Ribbon.
6. Click cell C3.
7. Press Ctrl + V, or click the Paste button in the Clipboard category on
the Home Ribbon.
8. If you see a strange moving line around cell C2, press the ESC key.
Cell C3 should be the active cell, but if it is not, just click it once. Look
at the Formula Bar. The contents of cell C3 are =B3 * 2, and not the
=B2 * 2 that you copied.
Did you see a moving line around a cell? That line’s called a marquee . It’s a
reminder that you are in the middle of a cut or copy operation, and the
marquee goes around the cut or copied data.
What happened? Excel, in its wisdom, assumed that if a formula in cell C2
references the cell B2 — one cell to the left — then the same formula put into
cell C3 is supposed to reference cell B3 — also one cell to the left.
When copying formulas in Excel, relative addressing is usually what you
want. That’s why it is the default behavior. Sometimes you do not want
relative addressing but rather absolute addressing. This is making a cell reference
fixed to an absolute cell address so that it does not change when the formula
is copied.
In an absolute cell reference, a dollar sign (\$) precedes both the column
letter and the row number. You can also have a mixed reference in which the
column is absolute and the row is relative or vice versa. To create a mixed
reference, you use the dollar sign in front of just the column letter or row
number. Here are some examples:
Reference Type
Formula
What Happens After Copying the Formula
Relative
=A1
Either, or both, the column letter A and the
row number 1 can change.
Absolute
=\$A\$1
The column letter A and the row number 1 do
not change.
Mixed
The column letter A does not change. The row
number 1 can change.
=\$A1
Mixed
The column letter A can change. The row
number 1 does not change.
=A\$1
Search JabSto ::

Custom Search