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?
This question is related to
excel
TL:DR
row lock = A$5
column lock = $A5
Both = $A$5
Below are examples of how to use the Excel lock reference $
when creating your formulas
To prevent increments when moving from one row to another put the $ after the column letter and before the row number. e.g. A$5
To prevent increments when moving from one column to another put the $ before the row number. e.g. $A5
To prevent increments when moving from one column to another or from one row to another put the $ before the row number and before the column letter. e.g. $A$5
Using the lock reference will also prevent increments when dragging cells over to duplicate calculations.
In Excel 2013 and resent versions, you can use F2 and F4 to speed things up when you want to toggle the lock.
About the keys:
F4 - Toggles the cell reference lock (the $ signs).
Example scenario with 'A4'.
How To:
In Excel, select a cell with a formula and hit F2 to enter formula edit mode. You can also perform these next steps directly in the Formula bar. (Issue with F2 ? Double check that 'F Lock' is on)
Notes:
Highlight "B1" and press F4. This will lock the cell.
Now you can drag it around and it will not change. The principle is simple. It adds a dollar sign before both coordinates. A dollar sign in front of a coordinate will lock it when you copy the formula around. You can have partially locked coordinates and fully locked coordinates.
Source: Stackoverflow.com