Prevent cell numbers from incrementing in a formula in Excel

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".

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.

Tags:

Excel

Recent Posts