Move or copy a formula

It's important to be aware of what can happen to cell references, whether they are absolute or relative, when you move a formula by cutting and pasting it, or copy a formula by copying and pasting it.

  • When you move a formula, the cell references within the formula do not change no matter what type of cell reference that you use.

  • When you copy a formula, the cell references may change based on the type of cell reference that you use.

What do you want to do?

Move a formula

Copy a formula

Move a formula

  1. Select the cell that contains the formula that you want to move.

  2. On the Home tab, in the Clipboard group, click Cut.

    You can also move formulas by dragging the border of the selected cell to the upper-left cell of the paste area. Any existing data is replaced.

  3. Do one of the following:

    • To paste the formula and any formatting, on the Home tab, in the Clipboard group, click Paste.

    • To paste the formula only, on the Home tab, in the Clipboard group, click Paste, click Paste Special, and then click Formulas.

Top of Page

Copy a formula

  1. Select the cell that contains the formula that you want to copy.

  2. On the Home tab, in the Clipboard group, click Copy.

  3. Do one of the following:

    • To paste the formula and any formatting, on the Home tab, in the Clipboard group, click Paste.

    • To paste the formula only, on the Home tab, in the Clipboard group, click Paste, click Paste Special, and then click Formulas.

      Note   You can paste only the formula results. On the Home tab, in the Clipboard group, click Paste, click Paste Special, and then click Values.

  4. Verify that the cell references in the formula produce the result that you want. If necessary, switch the type of reference by doing the following:

    1. Select the cell that contains the formula.

    2. In the formula bar Button image, select the reference that you want to change.

    3. Press F4 to switch between the combinations.

      The following table summarizes how a reference type updates if a formula that contains the reference is copied two cells down and two cells to the right.

For a formula being copied:

If the reference is:

It changes to:

Formula being copied

$A$1 (absolute column and absolute row)

$A$1

A$1 (relative column and absolute row)

C$1

$A1 (absolute column and relative row)

$A3

A1 (relative column and relative row)

C3

Note   You can also copy formulas into adjacent cells by using the fill handle Fill handle. After verifying that the cell references in the formula produce the result that you want in step 4, select the cell that contains the copied formula, and then drag the fill handle over the range that you want to fill.

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language