How to copy and paste exact formula in Excel

Copying and pasting an exact formula in Excel means copying a cell or range containing a formula and pasting it to another location, such that the formula is replicated exactly as it was originally written, including any relative or absolute cell references. This allows you to quickly and accurately apply the same calculation to different sets of data or reuse complex formulas without having to rewrite them every time.

Our dataset comprises a list of items purchased from a grocery store, including the name of the product, the quantity purchased, and the price per unit. We use a basic multiplication formula to calculate the total cost of each item and a summation formula to calculate the net total of the entire purchase. To maintain consistency and accuracy in our calculations, we have three methods for copying the exact formulas 

Method 1:  Copy the Exact formula of a Single Cell

Step 1 – Double Click on the Cell

• Double-click on the cell containing the formula to be copied and the formula will be shown on the cell

Step 2 – Add the Dollar symbol

• In the formula add the Dollar symbol($) in the form before every row and column header. This can be done manually or it can be done by pressing F4 key while keeping the cursor at each cell address.

Step 3 – Copy the Formula

• After placing the Dollar symbol select the formula
• And press the CTRL+C keys to copy the formula

Step 4 – Select the cell

• Click on the cell where you want to paste the formula

Step 5 – Paste the Formula

• After selecting the Cell paste the formula by pressing the CTRL+V keys

Step 6 – Press the Enter key

• After pasting the formula, press the Enter key to get the required result

Method 2: Keep the copied Formulas similar using special symbol

Step 1 – Select the Range of Cells

• Select the range of cells whose formulas are to be copied

Step 2 – Click on the Find and Select option

• Click on the Find and Select option in the Editing group of the Home tab and a dropdown menu will appear

Step 3 – Click on the Replace option

• From the drop-down menu, click on the Replace option and a dialog box will appear

Step 4 – Select the Symbols in the Dialog box

• Select the (=) in the box next to the Find What option
• And select (#) in the Replace with option 

Step 5 – Click on the Replace All option

• After selecting the symbols, click on the Replace All option at the left bottom of the dialog box and the symbols be changed.
• This will convert all the formulas to simple expressions which can be copied and pasted easily to any other range.

Step 6 – Copy the Range

• After changing the symbols, select the range and press the CTRL+C keys to copy the range

Step 7 – Select the Cell

• Click on  the Cell where you want to paste the formula to select it

Step 8 – Paste the Range

• After selecting the cell, press the CTRL+V keys to paste the copied range

Step 9 – Click on the Find and Select option

• Click on the Find and Select option in the Editing group of the Home tab and a dropdown menu will appear

Step 10 – Click on the Replace option

• From the drop-down menu, click on the Replace option and a dialog box will appear

Step 11 – Replace the Symbols in the Dialog box

• Write the (#) symbol next to the Find What option.
• Write (=) in the Replace with option. 

Step 12 – Click on the Replace All option 

• After selecting the symbols, click on the Replace All option at the left bottom of the dialog box to get the required result

Step 13 – Select the Range

• Select the previous range that you copied.

Step 14 – Change the Symbol

• Locate the Find and Replace dialog box again.
• Write the (#) in the Find What option
• Write (=) in the Replace with option 
• Then click on the Replace All option

Method 3: Copy the range of Formulas using the Show Formula option

Step 1 – Select the Range of Cells

• Select the range of cells whose formulas are to be copied

Step 2 – Click on the Show Formulas option 

• After selecting the range of cells, click on the Show Formulas option in the Formula Auditing group of the Formulas tab 
• And the formulas will appear in the selected range

Step 3 – Copy the range of Formulas

• To copy the formulas select the range of formula
• And press the CTRL+C 

Step 4 – Paste the Range on the Notepad

• After copying the range open the Notepad
• Then paste the range on the notepad by pressing the CTRL+V.

Step 5 – Select and copy the Range

• After typing the range on the Notepad, press CTRL+A to select all the formulas.
• Copy the whole text by pressing the CTRL+C keys. This will copy the formulas as text and the same cell references will be copied.

Step 6 – Select the Cell

• After copying the formulas from Notepad, click on the cell where you want to paste them

Step 7 – Paste the Range

• After selecting the cell, press the CTRL+V keys to paste the copied formulas. 
• When we paste the formulas from notepad then Excel doesn’t change the cell references and paste the formulas as they were written in the notepad as shown below.

Step 8 – Click on the Show Formulas option 

• Select the range of pasted formula 
•  And click on the Show Formulas option in the Formula Auditing group of the Formulas tab to get the required results.