See all How-To Articles

How to Copy and Paste Exact Formula in Excel & Google Sheets

In this tutorial, you will learn how to copy and paste exact formulas in Excel and Google Sheets.

 

copy exact formula 16 new

 

Copy and Paste Exact Formula – Find & Replace Feature

In the example below, you have an amount in Column D that is the product of values in Columns B and C. Now, say you want to copy the range (D2:D6) to another location, keeping the formulas, and without changing the cell references.

1. In cell D2, enter the formula:

and drag it to the end of the range (D6).

 

copy exact formula 7 new

 

2. To copy and paste the exact formula without changing the cell references to another place in your sheet, you need to convert the formulas to text and then copy them. To do that, select the range with formulas you want to copy. Then, in the Ribbon, go to Home > Editing > Find & Select > Replace.

 

copy exact formula 9

 

3. The Find and Replace dialog box will appear. In the Replace with: box, type any symbol that you want to use instead of = (equal sign). When done, just click on the Replace All button.

 

copy exact formula 10 new

 

4. As a result, all the equal signs in the range you selected are replaced with the chosen symbol. Since the content in each cell no longer starts with “=”, it will be in text form.
Now, select that range, right-click it, and from the drop-down menu, choose Copy (or use the CTRL + C shortcut).

 

copy exact formula 11

 

5. Then, select the place where you want to paste the range, right-click it, and under Paste Options click on the Paste icon (or you could use the CTRL + V shortcut).

 

copy exact formula 12

 

6. This pastes the range in Column F.

 

copy exact formula 17 new

7. To convert the copied and pasted ranges back to formulas, select both ranges and in the Ribbon, go to Home > Find & Replace > Replace.

 

copy exact formula 18 new

 

8. In the Find and Replace dialog box, (1) under Find what: enter the symbol you chose in Step 3, and (2) under Replace with: enter = (an equal sign). When done, (3) click the Replace All button.

 

copy exact formula 19 new

 

9. After that, an information window will appear to inform you that all the replacements were made. To finish, just click OK.

 

copy exact formula 20 new

 

As a result of Steps 1–9, you have the exact formula, copied from Column D and pasted to Column F, without changing cell references.

 

copy exact formula 15 new

 

You can also use a macro to copy and paste the exact formula in Excel.

Copy and Paste Exact Formula – Absolute References

Another way to copy and paste a formula without changing references is to use absolute cell references. If you want a formula to consistently refer to a particular cell, regardless of where you copy or move that formula in the worksheet, you should use absolute cell references, which do not change when copied.

1. First, click on the cell where you want to enter a formula and type = (an equal sign) to begin. Then, select the cell you want to make an absolute reference and press F4:

When done with the formula, press Enter.

 

copy exact formula 4

 

2. Then, copy the formula by dragging it down the column.

 

copy exact formula 5

 

As a result, the formula is copied down the column, and the reference to cell E2 does not change.

 

copy exact formula 6 new

 

Copy and Paste Exact Formula in Google Sheets

In Google Sheets, you can copy and paste the exact formula using absolute references and dragging by performing the same steps shown above for Excel.

To copy the exact formula with the Find and Replace feature, the steps are a bit different.

1. In the cell D3, enter the formula:

and drag the fill handle down the cells you want to fill with formulas (D2:D6).

copy exact formula 21 new

 

2. After that, (1) select the range and in the Toolbar, (2) click on Edit. From the drop-down menu, (3) choose to Find and replace.

 

copy exact formula 22 new

 

3. The Find and Replace dialog box will appear. (1) In the Find box, type = (an equal sign); and (2) in the Replace with box, type the symbol you want to replace “=” with. (3) Then indicate the specific range where you want to do the replacement, and (4) check the box next to the Also search within formulas. Finally, (5) click the Replace all button and when done, (6) click Done.

 

copy exact formula 24 new

 

4. As a result, all the = (equal signs) are replaced with the symbol you chose, and the range will no longer be read as formulas.

 

copy exact formula 25 new

 

5. Now, select that range (D2:D6), right-click it, and click on Copy (or use the CTRL + C shortcut).

 

copy exact formula 26 new

 

6. Select the place where you want to paste the range, right-click it and click on Paste (or use the CTRL + V shortcut).

 

copy exact formula 27 new

 

7. This pastes the (text) range in A8:A12.

 

copy exact formula 29 new

 

8. To convert both the copied and pasted ranges back to formulas, , (1) select them and in the Toolbar, (2) click on Edit and (3) choose to Find and replace.

 

copy exact formula 28 new

 

9. The Find and replace window will appear. (1) In the Find box, type the symbol you used in Step 3 (in this example, slash). (2) In the Replace with box, type = (an equal sign). (3) Then, specify the scope for the replacement (in this example, “This sheet”), and (4) check the box next to Also search within formulas. Finally, (5) click the Replace all button and when done, (6) click Done.

 

copy exact formula 30 new

 

As a result, you have the exact formula copied without changing cell references.

 

copy exact formula 31 new