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

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


Solution 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

Solution 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.