How to Copy Exact Formula in Excel (13 Methods)

Copy and Paste is one of the commonly performed activities while working with Microsoft Excel. We can copy any text, formula, or format in Excel. Here in this article, we will show how to copy the exact formula in Excel with 13 methods using proper illustrations.

We can copy a formula in Excel with changing Cell References or fixed cell references. We will discuss both cases through the methods below.

The above dataset will be used for this tutorial.

1. Applying Double Click to Copy Exact Formula in Excel

Just double-click the mouse to copy the formula from the above cell.

Step 1:

• First, we put a formula in Cell E5 to get the sum of Cells C5 and D5.

Step 2:

• Now, press ENTER to get the result.

Step 3:

• Now, move the cursor to the right bottom corner of Cell E5. A Plus sign (+) is showing. Double click here.

Now, look at the dataset.

The formula is copied to the rest of the cells. It will copy the formula through the column before getting any empty cell references.

Related Content: How to Copy Formula Down with Shortcut in Excel

2. Copying Exact Formula in Excel by Dragging

We can copy any formula by dragging. Dragging has the advantage of copying any formula in any direction left, right, up, or down.

We have a formula for Cell F5. We will copy this formula into 4 directions.

Step 1:

• Go to the right bottom corner of Cell F5.
• A Plus sign (+) will appear. Press and drag to the right side.

Now, we can see that the formula is copied towards the adjacent right cell.

Step 2:

• We can copy the formula downwards. Similarly, press the plus sign and drag it downwards.

We can see that formula is copied downwards. Similarly, we can copy the formula towards the left or upper side.

Read More: How to Copy SUM Formula in Excel

3. Using Excel Fill Feature to Copy Exact Formula

We can copy the formula using the Excel Fill tool.

We have a formula on Cell F5. Using the Fill tool, we will copy the formula of Cell F5 in four different directions.

Step 1:

• Move to Cell G5 first.
• From the Home tab, go to the Editing group.
• Choose Fill tool.
• Select direction from the list. Here, we choose Right as our selected cell is on the right side of the formula cell.

Now, look at the dataset.

The formula is copied to the right side.

Step 2:

• Similarly, click on Cell F6 to copy the formula downwards.
• Select Down from the Fill drop-down.

Read More: Creating and Copying Formula Using Relative References in Excel

4. Copying Same Formula Using Simple Copy-paste

The simplest method to copy the formula is just to press CTRL+C. In this section, we will use this method.

Step 1:

• First, go to Cell F5.
• Press the F2 button to edit the cell.
• Move the cursor to the end of the formula and press the CTRL+SHIFT+ Left Arrow to select the whole formula.
• Now, click on CTRL+C to copy the formula.

Step 2:

• Now, choose any cells from the dataset. We choose Cell F7.
• Press CTRL+V.

Look at the dataset. The mentioned formula is copied to the desired cell. Here, the formula is copied exactly like the original formula. Cell references are not changed here. If we copy the cell by editing the cell, the cell references will change.

Read More: How to Copy Formula in Excel to Change One Cell Reference Only

5. Using CTRL Hotkey to Copy Formula to Right and Down Sides

CTRL button is known as the modifier key. We can use this option to copy the formula in two directions: Right and Downside. The formula is copied only in the adjacent cells.

Step 1:

• First, go to Cell F6.
• Then, press CTRL+D to copy the formula downwards.

Notice the dataset. The formula is copied to the cell below.

Step 2:

• Go to Cell G5 to copy the formula towards the right side.
• Then, press CTRL+R.

Now, the formula is copied to the right side cell. We can not copy the formula to the adjacent up and left side using this modifier tool.

Read More: How to Copy a Formula in Excel Without Changing Cell References

6. Copying Exact Excel Formula Using CTRL+X Keys

We can use the CTRL+X option to copy a formula. By applying this method, we can copy the exact formula, cell references will remain unchanged.

Step 1:

• Go to Cell F5.
• Press CTRL+X.

The formula is copied now. We will paste the formula on Cell F8.

Step 2:

• Enter Cell F8 and press CTRL+V.

Our formula is copied exactly, no changes happened here.

Read More: How to Copy a Formula Across Multiple Rows in Excel (5 Ways)

7. Using Absolute References to Copy Formula

We will use Absolute cell reference in the formula. And copy that formula using the ribbon option. Because of absolute cell references, the formula will remain unchanged.

Step 1:

• First, go to Cell F5 where the formula exists.
• From the ribbon Clipboard group choose Copy.

The formula is copied now.

Step 2:

• Choose Paste from the Clipboard group.

Look at the dataset now.

The formula is copied exactly.

8. Using CTRL+ENTER to Copy Exact Formula in Multiple Excel Cells

We can copy the same formula in multiple cells at the same time by pressing CTRL+ENTER.

Step 1:

• Go to Cell F5.
• Copy the formula from the formula bar using CTRL+C.

Step 2:

• Now, select multiple cells by pressing the CTRL button.
• After the selection of cells, press the F2 button.
• Cells are in an editable mood now. Paste the formula now, by pressing CTRL+V.

Step 3:

• Now, press CTRL+ENTER instead of the ENTER only.

All the selected cells are filled with the copied formula.

Read More: How to Copy Formula to Entire Column in Excel

9. Copying Similar Formula Using CTRL+' in the Down Cell

We copy the exact formula and make the cell editable using CTRL+'(Single quote). It can copy the formula only downwards.

Step 1:

• Go to Cell F6. Cell F5 contains a formula.
• Press CTRL+' on that cell.

Look at the dataset. Cell F6 now contains the formula from the previous cell and is in an editable mood.

Step 2:

• Now, press ENTER.

Here, the result is shown after the implementation of the formula.

10. Using Mouse to Move Exact Formula in Excel

We can simply move the formula using the mouse.

Step 1:

• Go to Cell F5.
• Place the mouse on any border of the cell. A four-sided arrow will appear.

Step 2:

• Press the left button of the mouse. Keep pressing the button. Move the cursor to your required position or cell.

Now, see that formula is copied exactly with no changes.

11. Using Excel Table to Copy Same Formula

Excel Table is a useful tool. We can copy formulas using this tool also.

Step 1:

• First, go to the Insert tab.
• Choose the Table Or we can press CTRL+T.
• Select the range for the table and click on OK.

Step 2:

• Now put the formula below on Cell E5.

Step 3:

• Finally, press the ENTER button.

The rest of the cells of the Total column are filled with data. So, the formula is copied successfully.

12. Applying Excel Find and Replace Tool to Copy Exact Formula

The Find & Replace method can easily copy Excel formulas exactly.

In this section, we have a formula on Cell F5 and we will copy this formula.

Step 1:

• Press CTRL+H to enter the Find and Replace dialog box.
• Put “= (Equal)” on Find what box and “# (Hash)” on Replace with box.
• Finally, press Replace All.

A pop-up is showing, indicating the number of replacements.

Step 2:

• Press OK on the pop-up and press Close on the Find and Replace dialog box.

Step 3:

• Now, copy and paste the formula from Cell F5 to F7 pressing CTRL+C and CTRL+V.

Step 4:

• Replace # by =, then follow Steps 1 and 2 again.

Notice the dataset now.

13. Using Notepad to Copy Paste Formulas in Excel

Using the notepad we will copy the exact formula in Excel.

We have a formula on Cell F5.

We will copy that formula into another cell.

Step 1:

• First, go to the Formulas Select Show Formulas from the Formula Auditing group.

Now, any formula that exists in the sheet will show.

Step 2:

• Go to the desktop main screen.
• Press the right button of the mouse and from the pop-up choose New.
• Select Text Document from the list.

Step 3:

• Now, copy the formula from the Excel sheet using CTRL+C and paste it into the Notepad file using CTRL+V.

Step 4:

• Copy the formula from the Notepad file.
• Select any cell in the sheet to paste the formula.
• Choose Paste from the Clipboard group.
• Click on Use Text Import Wizard from the list.

Step 5:

• A dialog box will appear. Select Delimited and then press Next.

Step 6:

• Uncheck the Delimiters and press Next.

Step 7:

• Now, choose Text and press Finish.

Look at the dataset now.

We copied the exact formula in the new cell.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described how to copy exact formulas in Excel. We added 13 methods to do this. I hope this will satisfy your needs. Please, give your suggestions in the comment box.

<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel