Business Productivity Tools
Excel – Copying Formulas
© Mike Splane 2006
When
you enter a formula into a cell you see the formula in the “formula bar” above
the worksheet. If the formula also appears in the cell, you are in “mirror” view.
If the formula does not appear in the cell, you are in “flashcard” view. Switch
between the two views by simultaneously pressing CTRL and ~ on the keyboard. Do
this exercise in mirror view.
Excel
automatically alters cell references when a formula is copied into a new cell. The
goal of this exercise is to learn how this process works.
Each
cell reference has two parts, a row number and a column letter. A cell
reference with two dollar signs, called an Absolute Cell Reference, won’t
change at all when the formula is copied: =$C$4. A cell reference with one
dollar sign, called a Mixed Cell reference, stops the symbol following the
dollar sign from changing: If the formula is written as =C$4 only the C can
change, if written =$C4 only the 4 can change. If the formula contains no
dollar signs, called a Relative cell Reference, then both parts of the cell
reference can chance: =C4.
Instructions:
Assume
each formula from the first column of this table was entered into cell F6 (start
with =B2, then =B2 + C2, etc).
The
cell references will change when each formula in cell F6 is copied into cells
F7, G6, and G7.
Record
your predictions for what the new formula will look like in the rows where it
says My Prediction.
Do
this for each formula. Now, test your predictions.
Enter
each formula into cell F6 and then copy the formula into cells F7, G6, and G7
and record the results.
Continue
until you have tested all 8 formulas.
Compare
your prediction to what actually happened when the formula was copied.
Try
to figure out the rules for how cell references change when formulas are
copied.
|
Predict what will happen if the formula in Cell F6 is copied into: |
A different row? Try Cell F7 |
A different column? Try Cell G6 |
A different row and column? Try Cell G7 |
|
|
= B2 |
My Prediction |
= B2 |
|
|
|
What Excel Did |
= B3 |
|
|
|
|
= B2 + C2 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|
|
= B2 + B3 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|
|
= $B2 +$B3 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|
|
= B$2 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|
|
= $B2 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|
|
= B$2 + C$2 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|
|
= $B$2 |
My Prediction |
|
|
|
|
What Excel Did |
|
|
|
|