Excel 2019/365: Copying Formulas, Mixed Cell References

Updated 2019-08-09

·
Know when to use **mixed** cell references in a
formula that is to be copied.

**Relative cell references:** A relative cell reference is a reference that changes when it is
copied. C5 is a relative cell reference. If a formula with *C5* is copied down the page, it will change to C6 in the next cell,
C7 in the next, etc. If it is copied to the right, it will change to D5 in the
next cell, then E5, etc.

**Absolute cell references:** An absolute cell reference is a reference that does not change
when it is copied. An absolute cell reference is indicated by a dollar sign
before the column letter, and a dollar sign before the row number, like this:
$C$5. If a formula with $C$5 is copied, the $C$5 will never change.

**Mixed cell references: **A mixed cell reference is a cell reference that is partly relative
and partly absolute, like this: $C5. Or this: C$5. In $C5, the dollar sign
before the column letter means "never change the column letter".
However, if the formula is copied down, the row number will change (because the
row number is a relative reference). In C$5, the dollar sign before the row
number means "never change the row number". However, if the formula
is copied to the right, the C will change (because the column letter is a
relative reference).

Mixed cell references are
useful in cases where the number in a table is determined by a value from the
leftmost row *and* by a value from the
top column. A simple example is an addition table.

Create a new worksheet and
enter the following in row 1 and column A:

I have formatted the data
(centered, bold, shaded, with borders), but that isn't necessary.

Every entry in our
addition table will be created by selecting a number from column A, and by
selecting a number from row 1 and adding them together.

In B2, we want to add the
number in A2 and the number in B1, so our formula would be:

=A2+B1

However, if we copy this
down one cell, we get:

=A3+B2

which is not correct. The
A3 is correct, but the B2 is not. It should remain B1.

If we copy the formula in
B2 to the right one cell, we get:

=B2+C1

which is also not correct.
The C1 is correct, but the B2 is not. It should remain A2.

In every formula in the
worksheet, we want the following:

1) The first term must come from
column A (but the row number will change if we copy the formula down the page).

2) The second term must come
from row 1 (but the column number will change if we copy the formula to the
right).

So in the formula:

=A2+B1

we want the "A"
in A2 to be absolute, but want the 2 to be relative, so it should be $A2.

And we want the
"1" in B1 to be absolute, but we want the B to be relative, so it
should be B$1.

So our formula becomes:

=$A2+B$1

Enter this into B2. Copy
it down to row 11. The copy it across to column K. Your spreadsheet should look
like this:

You can double-click
anywhere in the range B2:K11 and you will always see the same thing: The blue
term will come from column A. And the red term will come from row 1.