Microsoft Excel - Copy Pasting while maintaining reference to a single cell

Question

Let's say that I have a value in cell A5 that is being used to calculate a value in A3.

Example:

A3 = (A1 + A2) * A5

When I copy paste cells A1:A3 and paste onto B1:B3, I'd like the resulting formula to be this:

B3 = (B1 + B2) * A5

Notice that A5 is still there.

It is the default behavior of Excel to cause this to be B5 instead of A5.

Is there a way to force a reference a single cell, such as A5, to be maintained while copy pasting rows?

Thanks

Answer 1

You want to use Absolute References then instead of relative references. Yes that is the default behavior of Excel. In your example do $A$5 to always reference A5.

A3 = (A1 + A2) * $A$5
B3 = (B1 + B2) * $A$5

Answer 2

In the formula, instead of referencing A5, reference $A$5. You can also keep only the column, but with changing row numbers, through referencing $A5, or only keep the column, changing row, by referencing A$5.

Answer 3

I am using Excel 2007 and when I perform this paste (as a simple CTRL+Insert then SHIFT+Insert), the A5 is pasted as B5. It does NOT stay as A5 during the paste.