Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 9 down vote accepted

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
share|improve this answer
2  
To quickly turn an "A5" into a "$A$5" press "F4". This will help you scroll through fixed references: $A5 (Column fixed); A$5 (Row fixed); $A$5 (Column&Row fixed) –  RocketGoal Jul 16 '10 at 14:15
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.