I am using the following Excel formula to get the relative row number for a calculation.


Cell C3 contains a constant value, so I always want to use that value, regardless of the row. When I apply the function to the rows, the rows increment correctly, but Excel is also incrementing the C column to C4, then C5, then C6. I want the formula to always use the value in C3, regardless of the row, how do I force it to do that?

  • Ok, figured this out on my own. To keep a cell reference constant in a formula, even after copying the formula around the spread sheet, simply use $C$3. – user646584 Jul 3 '11 at 4:43

Just use $C$3. The dollar signs tell Excel not to vary that row/column.

  • Why does Excel vary the row with $A9, but not with $C$3? Just seems a bit non-intuitive, they should have used a different symbol to differentiate it – user646584 Jul 3 '11 at 4:48
  • The dollar signs apply to rows and columns separately. $A9 varies the row but not the column; A$9 varies the column but not the row; A9 varies both and $A$9 varies neither. – jtbandes Jul 3 '11 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy