Menu
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?
530
The number won't increase automatically if we hold down the Ctrl key while dragging the AutoFill handle in Excel. In our case, please select the Cell A2; hold down the Ctrl key which will change the cursor to a double-cross ; and then drag the AutoFill handle down to the cells as you need.
If you do not want cell references to change when you copy a formula, then make those cell references absolute cell references. Place a "$" before the column letter if you want that to always stay the same. Place a "$" before a row number if you want that to always stay the same.
1. Select the cell with the formula you want to make it constant. 2. In the Formula Bar, put the cursor in the cell which you want to make it constant, then press the F4 key.
For locking the cell reference of a single formula cell, the F4 key can help you easily. Select the formula cell, click on one of the cell reference in the Formula Bar, and press the F4 key. Then the selected cell reference is locked.
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.
175
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
