Prevent an increase in the number of numbers in a formula in Excel

Question

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

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

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

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

when i need it

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

Is there an easy way to prevent an increase in expression?

Answer 1

There is something in excel called a “locked link” that you can use to do this, and use the $ characters to block the range. For your example, you will use:

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

This blocks 1 in B1 , so when copying to lines below 1 will remain the same.

If you use $B$1 , the range will not change when copying by row or column.

Answer 2

In versions of Excel 2013 and resent, you can use F2 and F4 to speed things up when you want to switch the lock.

About the keys:

• F2 - With the selected cell, it places the cell in the formula editing mode.

• F4 - Toggles the blocking of cell references ($ signs).

• Sample script with "A4".

  • Pressing F4 converts 'A4' to '$ A $ 4'
  • Pressing F4 again converts '$ A $ 4' to 'A $ 4'
  • Pressing F4 again converts "A $ 4" to "$ A4"
  • Pressing F4 again converts '$ A4' back to the original 'A4'

How to do:

• In Excel, select the cell with the formula and press F2 to enter the formula in edit mode. You can also follow these steps directly in the Formula Bar. (Problem with F2 ? Double check that the "F Lock" function is enabled)

  • If the formula has one cell reference;
    • Press F4 as necessary, and the link to one cell will switch.
  • If forumla has more than one cell link, pressing F4 (without highlighting anything) will switch the last cell link in the formula.
  • If the formula contains more than one cell reference, and you want to change all of them;
    • You can use the mouse to select the entire formula, or you can use the following keyboard shortcuts:
    • Press the End key (if necessary) The cursor ends by default)
    • Press Ctrl + Shift + Home to select the whole formula
    • Press F4 as needed
  • If the formula contains more than one cell reference and you want to edit only certain ones;
    • Highlight specific values ​​using the mouse or keyboard ( Shift and arrow keys), and then press F4 as needed.

Notes:

• These notes are based on my observations when I reviewed this for one of my own projects.
• It only works on one cell formula at a time.
• Pressing F4 without selecting anything will update the lock on the last cell link in the formula.
• Pressing F4 , when you mixed the lock in the formula, converts everything to the same thing. Example: two different cell references, such as "$ A4" and "A $ 4", will become "A4". This is good because it can interfere with a lot of guessing and cleaning.
• Ctrl + A does not work in the formula editor, but you can press the End key and then Ctrl + Shift + Home to select the whole formula. Beats Home , and then Ctrl + Shift + End .
• OS and hardware manufacturers have many different key bindings for Function (F Lock) keys, so F2 and F4 can do different things. For example, some users may need to hold the F Lock key on some laptops.
• "DrStrangepork" commented F4 actually closes Excel, which may be true, but depends on what you last selected. Excel changes the behavior of F4 depending on the current state of Excel. If you select a cell and in the formula editing mode ( F2 ), F4 will switch the blocking of links to cells, as Alexandre originally suggested. Playing with this, I had F4 do at least 5 different things. I see F4 in Excel as an entire function key that behaves somehow like this; "As an Excel user, given my last action, automate or repeat the logical next step for me."

Answer 3

Highlight "B1" and press F4. This will block the cell.

Now you can drag it and it will not change. The principle is simple. He adds a dollar sign in front of both coordinates. The dollar sign in front of the coordinate will block it when copying the formula. You can have partially locked coordinates and completely locked coordinates.