Excel 2013 & 2016: Copying Formulas, Mixed Cell References

Updated 2016.09.07

Objectives:

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

Mixed Cell References

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.