Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .