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.


Download Practice Workbook

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


13 Methods to Copy Exact Formula in Excel

We can copy any formula with the relative 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. Copy Formula Using Double Click

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.
=C5+D5

Copy Formula Using Double Click

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.

Copy Formula Using Double Click

Now, look at the dataset.

Copy Formula Using Double Click

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

Related Content: How to Copy Formula in Excel (6 Quick Methods)


2. Copy 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 on 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.

Copy Formula by Dragging in Excel

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.

Copy Formula by Dragging in Excel

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 Formula in Excel without Dragging (10 Ways)


3. Excel Fill Feature to Copy 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.

Excel Fill Feature to Copy Formula

Now, look at the dataset.

Excel Fill Feature to Copy Formula

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: Copy Formula in Excel by Changing Only One Cell Reference


4. Copy 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.

Copy Formula Using Simple Copy-paste

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 and Paste as Text in Excel (2 Ways)


5. Use 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 Down sideS. 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.

Use CTRL Hotkey to Copy Formula to Right and Down Sides

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.

Use CTRL Hotkey to Copy Formula to Right and Down Sides

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.

Related Content: How to Copy a Formula Down the Column in Excel(7 Methods)


6. Copy Formula Using CTRL+X

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.

Copy Formula Using 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: Shortcut to Copy Formula Down in Excel (7 Ways)


7. Copy Formula Using Absolute References

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 chooses Copy.

Copy Formula Using Absolute References

The formula is copied now.

Step 2:

  • Choose Paste from the Clipboard group.

Copy Formula Using Absolute References

Look at the dataset now.

The formula is copied exactly.

Read More: Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)


8. Use CTRL+ENTER to Copy Exact Formula in Multiple 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.

Use CTRL+Enter to Copy Exact Formula in Multiple Cells

Step 3:

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

Use CTRL+Enter to Copy Exact Formula in Multiple Cells

All the selected cells are filled with the copied formula.

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


9. Copy Exact 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.

Copy Exact Formula Using CTRL+’ in the Down 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. Use 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.

Use Mouse to Move Exact Formula in Excel

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. Excel Table to Copy Exact 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.

Excel Table to Copy Exact Formula

Step 2:

  • Now put the formula below on Cell E5.
=[@2019]+[@2020]

Excel Table to Copy Exact Formula

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. Use Find and Replace Tool to Copy Exact Excel 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.

Use Find and Replace Tool to Copy Exact Excel Formula

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.

Use Find and Replace Tool to Copy Exact Excel Formula

Step 3:

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

Use Find and Replace Tool to Copy Exact Excel Formula

Step 4:

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

Notice the dataset now.

Related Content: 3 Quick Ways to Copy Down Excel Formula without Incrementing


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.

Using Notepad to Copy Paste Formulas in Excel

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.

Using Notepad to Copy Paste Formulas in Excel

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.

Using Notepad to Copy Paste Formulas in Excel

Step 5:

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

Using Notepad to Copy Paste Formulas in Excel

Step 6:

  • Uncheck the Delimiters and press Next.

Using Notepad to Copy Paste Formulas in Excel

Step 7:

  • Now, choose Text and press Finish.

Using Notepad to Copy Paste Formulas in Excel

Look at the dataset now.

We copied the exact formula in the new cell.


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 have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo