Learn how to copy formula to multiple cells, down a column, to non-adjacent cells, copy formulas with absolute or mixed cell references, etc.
Copying formulas is one of the most common and easiest tasks that you do in a typical spreadsheet that relies mainly on formulas. It’s easy and time-saving to copy and paste a formula from one cell to another than retyping the entire formula all over again.
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 discuss different ways 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 we might prefer to copy and paste a formula from one cell to another to avoid retyping the entire formula all over again.
Let’s say we have this table:
There are actually a couple of methods to copy.
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’ selection in the ‘Clipboard’ section of the ‘Home’ tab in the ribbon.
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’ beneath these six icons to open the ‘Paste Special’ dialog box. Here, you can see the same options in addition to several others. Select ‘All’ or ‘Formulas’ under the Paste section and click ‘OK’.
Now the cell with pasted formula should have formulas just like the one in the copied cell but with the cell references changed to match the row numbers.
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.
Click the cell with the formula to select it 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 methods to paste the formula (like we did for the single cell).
Copy Formula to an Entire Column or Row
Excel provides a really quick way to copy a formula to a column or a row.
To copy a formula down a column, first, enter a formula in a cell. Then, select the cell with the formula, and hover the mouse cursor over a small square at the lower right-hand corner of the cell (It’s called Fill handle). As you hover, the cursor will change to a black plus sign (+). Hold that plus sign and drag the fill handle down the column over the cells where you want to copy the formula.
When you copy the formula to a range of cells, cell reference will automatically change based on a relative position of rows and columns and the formula will perform calculations based on the values in those cell references (See below).
In the same way, you can drag the formula into adjacent cells to the left, to the right, or upwards.
Another way to apply the formula to the entire column is by double-clicking the ‘plus sign’ instead of dragging it. When you double-click the plus sign, it copies the formula down as far as there is any data to the left.
Copy a Formula to a Range Without Copying Formatting
Copying a formula to a range of cells by dragging the fill handle not only copies the formula, but also the source cell formatting such as font color or background color, currency symbols, the decimal places, etc (as shown below).
To prevent copying the cell formatting, drag the fill handle as shown above. After that, click the ‘Auto Fill Options’ at the lower right-hand corner of the last cell, and in the drop-down menu, select ‘Fill Without Formatting’.
Copy an Excel Formula with Only Number Formatting
But if you want to copy and paste the formula with only the formula and the number formatting such as percentage format, currency format, etc, use Excel’s Copy & Paste feature.
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, select the ‘Formulas & Number Formatting’ icon 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 Cells
Copying formulas to multiple cells or a range of cells using the fill handle is the fastest method in Excel. But what if you want to copy a formula to non-contiguous or non-adjacent cells or beyond the end of the source cell? You can do this using the
Select the cell with the formula to select it and press
Ctrl+C to copy the formula. Then, select non-adjacent cells/ranges while pressing and holding the
Ctrl key. Press
Ctrl + V to paste the formula and press
Enter to complete.
Copying Formulas Without Changing Cell References in Excel
When you copy a formula to another cell in Excel, it automatically adjusts the cell references. Those references are called relative cell reference (without $) which uses the relative position of a cell address. 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.
If your formula contains relative cell references, Excel automatically adjusts them so that the formula refers to the adjusted rows and columns. If you use absolute references in a formula, then the exact formula gets copied.
When you add a dollar sign ($) in front of the column letter and row number (For example $A$1) that cell becomes an absolute cell. No matter where you copy this formula, 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
Sometimes you may want to copy the formula exactly, without changing the cell references along the way. If you need to copy or move an exact formula without changing 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.
Alternatively, select the cell with the formula, press
F2 (or double-click the cell) to enter the editing mode. Then, select the formula in the cell using the mouse and press
Ctrl + C to copy the formula in the cell as text.
Next, press the
Esc key to exit the formula bar. Then, select the destination cell and press
Ctrl + V to paste the formula.
This will paste the formula exactly, without changing the cell references.
Copy Formulas with Absolute or Mixed Cell References
If you want to move or copy Excel formulas so that no cell references are changed, the best way would be to change cell 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.
In some cases, you may need to use mixed cell references ($A1 or A$1) to lock either a row or column.
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.
As you can see in the screenshot below, the formula includes an absolute cell reference ($B$9) to fix the rent amount to cell B9, and a relative cell reference to cell B2 because you want this reference to adjust for each row. 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!
To fix this, just change a relative reference (B2) to a mixed reference $B2 (absolute column and relative row) by putting the dollar sign ($) in front of the column letter.
And now, if you copy or move the formulas from column C to column E, or any other column, the column reference won’t adjust because you anchored it by the dollar sign ($B2).
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 spreadsheet. The grave accent key (`) is the furthest key to the top left corner of your keyboard on the row with the number keys (below the
ESC key and next to the number 1 key).
Select all the cells with the formulas you want to copy and copy them by pressing
Ctrl + C, or
Ctrl + X to cut them. Then open Notepad or any other text editor and press
Ctrl + V to paste the formulas in the notepad.
Next, select the formulas and copy them from the notepad, and paste (
Ctrl + V) in the cells where you want the exact formulas copied.
After pasting the formulas, toggle 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 Excel formulas without changing their cell references, then you can use the Excel Find and Replace feature in the following way.
Select 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 some symbol or character that is not used in any 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.
Now, the text strings are converted back to the formulas and you will get this result: