Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

When you copy formulas to another location, you can choose formula-specific paste options in the destination cells.

Here’s how you copy and paste a formula:

  1. Select the cell with the formula you want to copy.

  2. Press ⌘ + C.

  3. Click the cell where you want to paste the formula.

    If that cell is on another worksheet, go to that worksheet and click the cell you want.

  4. To quickly paste the formula with its formatting, press ⌘ + V. Or, you can click the arrow next to the Paste button:

    Arrow next to the Paste button

  5. Clicking the arrow gives you a list of options. Here are the most commonly used ones:

    • Formulas to paste just the formula and not the formatting of the original cell.

    • Formulas & Number Formatting — to paste just the formula, and the number formatting (for example: percent format, currency format, and so on).

    • Keep Source Formatting — to paste the formula, the number formatting, font, font size, border, and shading of the original cell.

    • Paste Values to exclude the formula and paste just the result.


    Other options available to you that might be useful:

    • No Borders — to paste the formula, the number formatting, font, font size, shading--but not the border of the original.

    • Keep Source Column Widths — to paste the formula, the number formatting, font, font size, shading, border, and the width of the original.

    • Transpose — Use this option when copying more than one cell. If you copy cells that are side-by-side in a row, then this option will paste them in a column. If the cells are in a column, they'll be pasted into side-by-side in a row. This option pastes the formula, the number formatting, font, font size, shading, border.
       

Verify and fix cell references in the new location

After copying a formula to a new location, it's important to check that its cell references are correct. The cell references may have changed according to the absolute or relative reference type that you use.

Formula being copied from A1, to two cells down and to the right
 

For example, if you copy a formula two cells down and to the right of cell A1, then the cells references you used will be updated like this:

This reference:

Changes to:

$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


If cell references in the formula don’t give you the result you want, try switching to different reference types.

  1. Select the cell with the formula.

  2. Select the reference you want to change in the formula bar: Formula bar

  3. Press F4 to switch between the absolute and relative reference combinations, and pick the one you want.

Move a formula to another location

Unlike copying a formula, when you move a formula to another location in the same or another worksheet, the cell references in the formula don’t change, regardless of what type of cell reference you used.

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

  2. Press ⌘ + X.

  3. Click the cell where you want to paste the formula.

    If that cell is on another worksheet, go to that worksheet and click the cell you want.

  4. To paste the formula with its formatting, on the Home tab, click Paste or press ⌘ + V.

    For other formula paste options, click the arrow below Paste. You have many options here, but the most commonly used ones are:

    • Keep Source Formatting to paste the formula, the number formatting, font, font size, border, and shading of the original cell.

    • Values & Source Formatting to paste the values and the formatting only. The formula will be excluded.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×