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.