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

I am trying to achieve the following scenario.

  1. value of G1 has formula set to (=F1)
  2. When I copy the formula to other cells in column G, I want the formula to saty same (=F1) instead of dynamically getting updated to next cell number in the F column like (=F2) etc.
share|improve this question

migrated from Jun 10 '11 at 12:35

This question came from our site for professional and enthusiast programmers.

up vote 6 down vote accepted

For something like this you can use the $-symbol.


The $-symbol will take the following character as constant. So with the code above it will always refer to F1, no matter where you copy it.

share|improve this answer
Why in German? ? – GrzegorzOledzki Jan 11 '11 at 11:48
I was still tired I guess... as soon as I answered I realized that I wanted to answer in English... I hope it's showing in English now? (I edited it) – evident Jan 11 '11 at 11:53

This isn't really for Stack Overflow but the answer is to use absolute references and so your formula would be =$F$2 and wherever you copy it, it would still refer to that cell.

Another way to do this would be with named cells, but absolute references is probably simpler.

share|improve this answer

(For completeness of the previous answers)

As the previous posters noted, the $ notation makes the reference absolute under cell copy.

Note that you can make the reference row coordinate and the column coordinate fixed independently, like $RC or R$C.

In order to streamline the $-ify reference process while writing the formula, use the F4 function key after selecting that cell.

Cycling through the modes using F4 on, say, A10 reference, will change it to: $A$10 -> A$10 -> $A10 -> A10.

share|improve this answer

You must log in to answer this question.