How to Copy Excel Formulas into Other Cells
Most of the time you need to move or copy Excel formulas to different cells in your workbooks. But you will quickly realize that your formulas are getting messed up without any apparent reason,. In this post I’m going to go over basics about how to copy Excel formulas
Your formulas get messed up when moved or copied because Excel will update their cell references according to their new location.
For example: you have a formula in cell A1 that reads =B1+C1
- When moved to A2, it will become =B1+C1
- When copied to A2, it will become =B2+C2
Note: If your formula doesn’t include any cell reference (eg. =2+2), it will stay same no matter where it is copied.
You may or you may not want it to happen, or you may want it to partially happen. In this post you are going to learn how to manage it.
How to Move Excel Formulas:
If you have a formula like =SUM(B2:B6) you can move it as it is. You can do it by positioning your mouse at any border of the cell that contains the formula and click when you see the “four headed arrow” sign. This sign means “move”. While clicked you can move your formula to any other cell without it’s cell references changing.
Another way to move formulas without changing their cell references is by going into edit mode in this cell (either by double-click or single-click + F2). While in the edit mode, select the formula and copy it (Ctrl+C) and paste it anywhere you like. Cell references will stay same.
How to Copy Excel Formulas:
Here comes the tricky part. Like I stated above, when you copy Excel formulas from one cell to another Excel will automatically adjust their cell references based on formulas new cell location. This may be convenient or inconvenient. If it is convenient for you, you don’t need to do anything. But if it is not, here is how to deal with it:
Use of Relative References ($ sign) in formulas:
$ sign locks cell references in formulas. It works like this:
- $A$1 locks both column and row of the cell reference. It will stay as $A$1 when copied.
- $A1 locks column of the cell reference. It will change as $A$2 when copied.
- A$1 locks row of the cell reference. It will change as B$1 when copied.
There is a very useful shortcut key for putting $ in a cell reference. If you click on a cell reference in a formula then press F4, it will put $ in front of both column and row of that cell reference. You can alternate between possible locking combinations by repeatedly pressing F4.
Here are some examples for further clarification. I’am going to drag the formula down and to right to observe changes in cell references under different settings.
When you drag down a formula (auto fill) cell references will be adjusted.
If you lock row of a cell in a formula by putting a $ sign in front of it, it (row) will not adjust.
When you drag a formula sideways (auto fill) cell references will be adjusted.
If you lock columns of a cell in a formula by putting a $ sign in front of it, it (column) will not adjust.
If you put $ sign in front of both column and row of a cell reference in a formula, both column and row of that cell reference will stay same no matter where you copy it to.
This way you can copy Excel formulas without having any problems at all.