Prevent cell numbers from incrementing in a formula in Excel

Question

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 lt  gt    B4 B1        If I copy cell C4 and paste it down column C  the formula becomes   IF B5 lt  gt    B5 B2      IF B6 lt  gt    B6 B3     etc    when what I need it to be is    IF B5 lt  gt    B5 B1      IF B6 lt  gt    B6 B1     etc    Is there a simple way to prevent the expression from incrementing

User · Accepted 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 lt  gt    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

User · Answer

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

User · Answer

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    F2 - With a cell selected  it places the cell in formula edit mode  F4  - Toggles the cell reference lock  the   signs   Example scenario with  A4     Pressing F4 will convert  A4  into   A 4  Pressing F4 again converts   A 4  into  A 4  Pressing F4 again converts  A 4  into   A4  Pressing F4 again converts   A4  back to the original  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    If the formula has one cell reference    Hit F4  as needed and the single cell reference will toggle   If the forumla has more than one cell reference  hitting F4   without highlighting anything  will toggle the last cell reference in the formula  If the formula has more than one cell reference and you want to change them all     You can use your mouse to highlight the entire formula or you can use the following keyboard shortcuts  Hit End key  If needed  Cursor is at end by default  Hit Ctrl   Shift   Home keys to highlight the entire formula Hit F4 as needed  If the formula has more than one cell reference and you only want to edit specific ones    Highlight the specific values with your mouse or keyboard   Shift and arrow keys  and then hit F4 as needed      Notes    These notes are based on my observations while I was looking into this for one of my own projects   It only works on one cell formula at a time  Hitting F4  without selecting anything will update the locking on the last cell reference in the formula   Hitting F4  when you have mixed locking in the formula will convert everything to the same thing  Example two different cell references like   A4  and  A 4  will both become  A4   This is nice because it can prevent a lot of second guessing and cleanup  Ctrl A does not work in the formula editor but you can hit the End key and then Ctrl   Shift   Home to highlight the entire formula  Hitting Home and then Ctrl   Shift   End  OS and Hardware manufactures have many different keyboard bindings for the Function  F Lock  keys so F2  and F4 may do different things  As an example  some users may have to hold down you  F Lock  key on some laptops   DrStrangepork  commented about F4 actually closes Excel which can be true but it depends on what you last selected  Excel changes the behavior of F4 depending on the current state of Excel  If you have the cell selected and are in formula edit mode  F2   F4 will toggle cell reference locking as Alexandre had originally suggested  While playing with this  I ve had F4 do at least 5 different things  I view F4 in Excel as an all purpose function key that behaves something like this   As an Excel user  given my last action  automate or repeat logical next step for me

User · Answer

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

[excel] Prevent cell numbers from incrementing in a formula in Excel

Examples related to excel