How to Copy Formulas in Excel

By Steve McDonnell

When you want to apply the same formula to a series of data, such as computing batting averages for a little league team, Excel enables you to enter the formula for the first player and then copy it for the other players. There are a few ways to copy a formula depending on whether you want to copy to a single cell or to multiple cells, and depending on other cell properties you might want to copy, such as a number format.

Copying Formulas

To copy a formula, select the cell that contains the formula, right-click and choose "Copy." Select the destination cell and perform one of the following actions, depending on what you want to paste: right-click and select "Paste" to paste the formula and the cell formatting; right-click, choose "Paste Special" and "Formula" to paste the formula without the formatting; or right-click, choose "Paste Special" and "Values" to paste the resulting value of the formula instead of the equation.

Copying to Multiple Cells

To copy a formula to more than one destination cell, select the formula and press "Ctrl-C," or right-click and choose "Copy." Use your mouse to highlight the destination cells and press "Ctrl-V," or right-click and choose "Paste." There's an even easier way to copy a formula if the destination cells are adjacent to the cell with the formula. Grab the fill handle in the lower-right corner of the cell with the formula and drag it down or across the workbook to copy it to one or more adjacent cells.

Copying Between Worksheets

You can copy a formula from one worksheet to another using the same process. After you copy the formula, choose the destination worksheet, select the destination cells and paste the formula. Excel automatically prefixes references to cells in a different worksheet with the worksheet name and an exclamation point, for example, "Sheet1!A1."

Changing Cell References

Cell references in Excel are relative. This means that, if you have the formula "=A1+A2+A3" in cell A4 and you copy the formula to cell B4, Excel assumes you want the formula to be "=B1+B2+B3" and changes it for you automatically. When you copy a formula from column A to column C, all cell columns in the formula increase by two letters. When you copy a formula from row 1 to row 3, all cell rows in the formula increase by two. To prevent a column or row from changing, prefix it with a dollar sign. For example, copying "=$A3" won't change column A; copying "=A$3" won't change row 3; and copying "=$A$3" won't change either reference.


You can alternate between displaying the value of a cell or the formula in the cell by double-clicking the cell. If you're not using dollar signs but none of the cell references change, you're probably choosing "Cut" instead of "Copy." "Cut" moves a formula and doesn't modify the cell references. If you have formulas in Sheet2 that reference cells in Sheet1 and you delete Sheet1, the formulas in Sheet2 will display the "#REF!" error.