Preventing a spreadsheet formula from changing when copying
When a formula is created in a Microsoft Excel spreadsheet or another spreadsheet program it is created using a relative cell reference by default. When copying a formula that is using a relative cell reference the formula is going to become relative to where it is being pasted. For example, if you have a formula in cell B2 that was "=A2" and you copied the cell to B3 it would become "=A3" because it is relative to where it is being copied.
To prevent a formula from changing when it is copied, you must change the formula in part or in whole to use an absolute cell reference. If you created an absolute cell reference in cell C2 that was "=$A$2" and copied the cell to C3 (or any other cell) it would always remain absolute and never change.
Additional absolute cell reference examples
Absolute cell references can be used in all parts of a formula or can be used for a row or column that you want to remain absolute. To make a row or column absolute, it must begin with a dollar sign ($). Below are some additional Examples of how an absolute cell reference may be used in a formula.
Relative column and absolute row reference
=A$2
The above formula is using a relative column with an absolute row reference. If the cell formula was copied the row would always remain the same (2), but the column would be relative to where it is being copied.
Absolute column with a relative row reference
=$A2
The above formula is using an absolute column reference with a relative row reference. If the cell formula was to be copied the column would always remain the same (A) but the row would be relative to where it is being copied.
Absolute cell reference with relative cell reference
=SUM($A$2+B2)
The above formula will always use the value in A2 but add the next relative cell. If the cell formula was copied from C2 to C3, it would become
"=SUM($A$2+B3)."
Related Articles :)
How to copy and paste text and formulas in an Excel spreadsheet.
Help, examples, and information on Excel formulas
See our absolute cell reference and relative cell reference further information and related links.
Microsoft Excel help and support.
When a formula is created in a Microsoft Excel spreadsheet or another spreadsheet program it is created using a relative cell reference by default. When copying a formula that is using a relative cell reference the formula is going to become relative to where it is being pasted. For example, if you have a formula in cell B2 that was "=A2" and you copied the cell to B3 it would become "=A3" because it is relative to where it is being copied.
To prevent a formula from changing when it is copied, you must change the formula in part or in whole to use an absolute cell reference. If you created an absolute cell reference in cell C2 that was "=$A$2" and copied the cell to C3 (or any other cell) it would always remain absolute and never change.
Additional absolute cell reference examples
Absolute cell references can be used in all parts of a formula or can be used for a row or column that you want to remain absolute. To make a row or column absolute, it must begin with a dollar sign ($). Below are some additional Examples of how an absolute cell reference may be used in a formula.
Relative column and absolute row reference
=A$2
The above formula is using a relative column with an absolute row reference. If the cell formula was copied the row would always remain the same (2), but the column would be relative to where it is being copied.
Absolute column with a relative row reference
=$A2
The above formula is using an absolute column reference with a relative row reference. If the cell formula was to be copied the column would always remain the same (A) but the row would be relative to where it is being copied.
Absolute cell reference with relative cell reference
=SUM($A$2+B2)
The above formula will always use the value in A2 but add the next relative cell. If the cell formula was copied from C2 to C3, it would become
"=SUM($A$2+B3)."
Related Articles :)
How to copy and paste text and formulas in an Excel spreadsheet.
Help, examples, and information on Excel formulas
See our absolute cell reference and relative cell reference further information and related links.
Microsoft Excel help and support.
Posts
0 comments:
Post a Comment