Show All

Hide All

When you move a formula, the cell references within the formula do not change. When you copy a formula, the cell references may change based on the type of reference used.

1. Select the cell that contains the formula.
2. Verify that the cell references used in the formula will produce the result you want. Switch to the type of reference you need. To move a formula, use an absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

   How?

   1. Select the cell that contains the formula.
   2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , select the reference you want to change.
   3. Press F4 to toggle through the combinations. The "Changes To" column reflects how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.

   

   Formula being copied

   Reference (Description)	Changes to
   $A$1 (absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) column and absolute row)	$A$1
   A$1 (relative (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) column and absolute row)	C$1
   $A1 (absolute column and relative row)	$A3
   A1 (relative column and relative row)	C3

3. On the Edit menu, click Copy.
4. Select the cell you want to copy it to.
5. To copy the formula and any formatting, on the Edit menu, click Paste.
6. To copy the formula only, on the Edit menu, click Paste Special, and then click Formulas.

• How do I copy formula but only increment certain ranges?
• What's the best way to add a row and copy formulas and formats?
• Excel: hold a reference to a single cell when copying formulas?
• How to fix a formula, so when a row is inserted it doesn't change?
• How do I copy a formula with the cell increasing by increments?
• How do I copy a formula from one worksheet to another?