Excel – Prevent cell numbers from incrementing in a formula in Excel

excel

I have a formula in Excel that needs to be run on several rows of a column based on the numbers in that row divided by one constant. When I copy that formula and apply it to every cell in the range, all of the cell numbers increment with the row, including the constant. So:

B1=127
C4='=IF(B4<>"",B4/B1,"")'

If I copy cell C4 and paste it down column C, the formula becomes

=IF(B5<>"",B5/B2,"")
=IF(B6<>"",B6/B3,"")
etc.

when what I need it to be is 

=IF(B5<>"",B5/B1,"")
=IF(B6<>"",B6/B1,"")
etc.

Is there a simple way to prevent the expression from incrementing?

Best Answer

There is something called 'locked reference' in excel which you can use for this, and you use $ symbols to lock a range. For your example, you would use:

=IF(B4<>"",B4/B$1,"")

This locks the 1 in B1 so that when you copy it to rows below, 1 will remain the same.

If you use $B$1, the range will not change when you copy it down a row or across a column.

Related Topic