How to Copy a Formula in Excel

Copying formulas is one of the most common and easiest tasks that you do in a typical spreadsheet that relies mainly on formulas. Rather than typing the same formula over and over again in Excel, you can just easily copy and paste a formula from one cell to multiple cells.

After writing a formula in Excel, you can use the copy and paste commands to multiple cells, multiple non-adjacent cells, or entire columns. If you don’t do it right, you’ll end up with that awful # REF and /DIV0 errors. In this article, we’ll show you the different methods you can use to copy formulas in Excel.

How to Copy and Paste Formulas in Excel

Microsoft Excel provides various ways to copy formulas with relative cell references, absolute cell references, or mixed references.

Copy formula from one cell to another
Copy formula one cell to multiple cells
Copying formula to the entire column
Copying formula without formatting
Copy formulas to non-adjacent cells
Copy formulas without changing cell references

How to Copy a Formula from One Cell to Another in Excel

Sometimes you may want to copy a formula from one cell to another in excel to avoid retyping the entire formula all over again and save some time while doing that.

Let’s say we have this table:

There are few methods to copy formula from one cell to another.

First, select the cell with the formula and right-click, and in the context menu, select ‘Copy’ to copy the formula. Or you can use the ‘Copy’ option in the ‘Clipboard’ section of the ‘Home’ tab.

But you also copy formulas by simply pressing the keyboard shortcut Ctrl + C. This is a more efficient and time-saving method.

Then we go to the cell we want to paste it press the shortcut Ctrl + V to paste the formula. Or right-click on the cell you want to paste and click the options under ‘Paste Options’: either simple ‘Paste (P)’ option or paste as ‘Formula (F)’ option.

Alternatively, you can also click on ‘Paste Special’ below the six paste icons to open the ‘Paste Special’ dialog box. Here, you have several options including the six paste options from the context menu. Select ‘All’ or ‘Formulas’ under the Paste section and click ‘OK’.

Now the cell with pasted formula should have the same formulas (as the one in copied cell) but with the different cell references. The cell address is self adjusted by the excel to match row number of the pasted cell.

Copy Formula from One Cell to Multiple Cells

The same paste operation works just the same if we select multiple cells or a range of cells.

Select the cell with the formula and press Ctrl + C to copy the formula. Then, select all the cells where you want to paste the formula and press Ctrl + V to paste the formula or use one of the above paste methods to paste the formula (like we did for the single cell).

Copy Formula to an Entire Column or Row

In Excel, you can quickly copy a formula to an entire column or a row.

To copy a formula to a column or a row, first, enter a formula in a cell. Then, select the formula cell (D1), and hover your cursor over a small green square at the lower right corner of the cell. As you hover, the cursor will change to a black plus sign (+), which is called the Fill Handle. Click and hold that fill handle, and drag it in any direction you want (column or row) over the cells to copy the formula.

When you copy a formula to a range of cells, cell references of the formula will automatically adjust based on the relative location of rows and columns and the formula will perform calculations based on the values in those cell references (See below).

In the above example, when formula in D1 (=A1*B1)/2) copied to cell D2, the relative reference changes bases on it’s location (=A2*B2)/2) and so on.

In the same way, you can drag the formula into adjacent cells to the left, to the right, or upwards.

Another way to copy the formula to an entire column is by double-clicking the fill handle instead of dragging it. When you double-click the fill handle, it immediately applies the formula as far as there is any data to the adjacent cell.

Copy a Formula to a Range Without Copying Formatting

When you copy a formula to a range of cells with the fill handle, it copies the source cell’s formatting too, such as font color or background color, currency, percentage, time, etc (as shown below).

To prevent copying the cell formatting, drag the fill handle and click the ‘Auto Fill Options’ at the lower right-hand corner of the last cell. Then, in the drop-down menu, select ‘Fill Without Formatting’.

The result:

Copy an Excel Formula with Only Number Formatting

If you want to copy the formula with only the formula and the formatting such as percentage format, decimal points, etc.

Copy the formula and select all the cells to which you want to copy the formula. On the ‘Home’ tab, click the arrow below the ‘Paste’ button on the ribbon. Then, click the ‘Formulas & Number Formatting’ icon (the icon with % fx) from the drop-down to paste only the formula and the number formatting.

This option only copies formula and number formatting but ignores all other cell formattings like background color, font color, etc.

Copy a Formula to Non-Adjacent/Non-Contiguous Cells

If you want to copy a formula to non-adjacent cells or non-adjacent ranges, you can do that with the help of Ctrl key.

Select the cell with the formula and press Ctrl + C to copy it. Then, select non-adjacent cells/ranges while pressing and holding the Ctrl key. Then, press Ctrl + V to paste the formula and hit Enter to complete.

Copying Formulas Without Changing Cell References in Excel

When a formula is copied to another cell, Excel automatically changes the cell references to match its new location. These cell references use the relative location of a cell address, hence they are called relative cell reference (without $). For example, if you have the formula ‘=A1*B1’ in cell C1, and you copy this formula to cell C2, the formula will change to ‘=A2*B2’. All the methods we discussed above use relative references.

When you copy a formula with relative cell references, it automatically changes references so that the formula refers to the corresponding rows and columns. If you use absolute references in a formula, then the same formula gets copied without changing the cell references.

When you put a dollar sign ($) in front of the column letter and row number of a cell (For example $A$1), it turns the cell into an absolute cell. Now no matter where you copy the formula that contains the absolute cell reference, the formula will never. But if you have relative or mixed cell reference in a formula, use any of these following methods to copy without changing cell references.

Copy Formula with Absolute Cell Reference Using Copy-Paste Method

Occasionally, you may need to copy/apply the exact formula down the column, without changing the cell references. If you want to copy or move an exact formula with absolute reference, then do this:

First, select the cell with the formula you want to copy. Then, click on the formula bar, select the formula using the mouse, and press Ctrl + C to copy it. If you want to move the formula, press Ctrl + X to cut it. Next, hit the Esc key to leave the formula bar.

Alternatively, select the cell with the formula and hit F2 key or double-click the cell. This will put the selected cell into edit mode. Then, select the formula in the cell and hit Ctrl + C to copy the formula in the cell as text.

Then, select the destination cell and press Ctrl + V to paste the formula.

Now the exact formula gets copied into the destination cell without any cell reference changes.

Copy Formulas with Absolute or Mixed Cell References

If you’d like to move or copy exact formulas without changing cell references, you should change cell relative references to absolute references. For example, adding ($) sign to relative cell reference (B1) makes it an absolute reference ($B$1), so it remains static no matter where the formula is copied or moved.

But sometimes, you may need to use mixed cell references ($B1 or B$1) by adding a dollar ($) sign in front of the column letter or the row number to lock either a row or a column in place.

Let us explain with an example. Suppose you have this table that calculates the monthly savings by subtracting rent (B9) from earnings (in column B) every month.

In the example below, the formula uses an absolute cell reference ($B$9) to lock the rent amount to cell B9, and a relative cell reference to cell B2 because it needs to be adjusted for each row to match each month. B9 is made absolute cell reference ($B$9) because you want to subtract the same rent amount from each month’s earnings.

Let’s say you want to move the balances from column C to column E. If you copy the formula (by usual copy/paste method) from cell C2 (=B2-$B$9) will change to =D2-$B$9 when pasted in cell E2, making your calculations all wrong!

In that case, change the relative cell reference (B2) to a mixed cell reference ($B2) by adding the ‘$’ sign in front of the column letter of the formula entered in the cell C2.

And now, if you copy or move the formula from cell C2 to E2, or any other cell, and apply formula down the column, the column reference will remain the same while the row number will be adjusted for each cell.

Copy Paste Excel Formulas Without Changing References Using Notepad

You can see every formula in your Excel spreadsheet by using Show Formula options. To do that go to the ‘Formulas’ tab and select ‘Show Formulas’.

Alternatively, you can enter the formula view mode by pressing the Ctrl + ` shortcut, which displays every formula in your worksheet. You can find the grave accent key (`) at the top left corner of your keyboard on the row with the number keys (below the ESC key and before the number 1 key).

Select all the cells with the formulas you want to copy and press Ctrl + C to copy them, or Ctrl + X to cut them. Then open Notepad and press Ctrl + V to paste the formulas in the notepad.

Next, select the formula and copy(Ctrl + C) it from the notepad, and paste it(Ctrl + V) in the cell where you want the exact formula copied. You can copy and paste them one by one or all at once.

After pasting the formulas, turn off the formula view mode by pressing Ctrl + ` or again go to ‘Formulas’ –> ‘Show formulas’.

Copy the Exact Formulas Using Excel’s Find and Replace

If you want to copy a range of Exact formulas, you can also use Excel’s Find and Replace tool to do so.

Select all the cells that have the formulas that you want to copy. Then go to the ‘Home’ tab, click ‘Find & Select’ on the Editing group, and select the ‘Replace’ option, Or simply press Ctrl + H to open the Find & Replace dialog box.

In the Find and Replace dialog box, enter the equal sign (=) in the ‘Find what’ field. In the ‘Replace with’ field, enter a symbol or character that is not already part of your formulas, like #, or , etc. Then, click the ‘Replace All’ button.

You’ll get a prompt message box saying ‘We made 6 replacements’ (because we selected 6 cells with formulas). Then click ‘OK’ and ‘Close’ to close both dialogs. Doing this replaces all equal to (=) signs with hash (#) signs, and turns formulas into text strings. Now the cell references of the formulas won’t be changed when copied.

Now, you can select these cells, press Ctrl + C to copy them, and paste them into destination cells with Ctrl + V.

Finally, you need to change the (#) signs back to (=) signs. To do that, select both ranges (original and copied range) and press Ctrl + H to open the Find & Replace dialog box. This time, type the hash (#) sign in the ‘Find what’ field, and equal to (=) sign in the ‘Replace with’ field, and click the ‘Replace All’ button. Click ‘Close’ to close the dialog.