How to paste exact formulas in Microsoft Excel

By SpreadCheaters
Page last updated: 10/06/2023 | Next review date: 10/06/2025
close

Author bio


SpreadCheaters
View More north_east

Pasting exact formulas in Excel means copying and pasting formulas from one cell or range of cells to another cell or range of cells without changing any of the cell references or calculations.

In this tutorial, we will learn how to paste exact formulas into Microsoft Excel. In Excel, there are many ways in which a user can paste exact formulas. One approach is to cut the formula from the parent cell and paste it into the destination, Doing this pastes the exact formula. We can also use other methods which include converting the cell references to absolute references and then utilizing copy and paste method or using Autofill.

Suppose we have a dataset that calculates Total Sales using a formula. If we need to add a new column before the Total Sales column, we may want to copy and paste the formula to the new column. However, Excel might adjust the cell references when we copy and paste the formula, which can cause errors in the calculation.

Method 1: Utilizing the Cut and Paste Method

Step 1 – Cut the Formula

  • Choose the cell with the formula to be pasted.
  • Press the CTRL+X keys to cut the formula.

Step 2 – Paste the Formula

  • Paste the formula in the destination cell by selecting the cell and pressing CTRL+V.

Step 3 – Paste the Formula from Each Cell

  • Paste the formulas from each cell into the new column utilizing the method mentioned above.
  • Now we can add the new column before the Total Sales column.

Method 2: Copying the Formula Excluding the Equals Sign

Step 1 – Double Click on the Cell and Copy the Formula Excluding the Equals Sign

  • Double-click on the cell containing the formula.
  • Select the formula excluding the equals sign.
  • Copy the selection by pressing CTRL+C.

Step 2 – Place an Equals Sign in the Destination Cell

  • Place an Equals sign in the destination cell.

Step 3 – Paste the Formula

  • Paste the formula right after the equals sign.
  • For this press CTRL+V keys.

Step 4 – Paste the Formula from Each Cell

  • Paste the formulas from each cell into the new column utilizing the method mentioned above and delete the previous column.
  • Now we can add the new column before the Total Sales column.

Method 3: Pasting Exact Formula to Adjacent Cells

Step 1 – Double Click to Edit the Formula

  • Double-click on the cell containing the formula.

Step 2 – Select the Formula and Press F4 Key

  • Select the complete formula using the cursor or by pressing CTRL+A.
  • Press the F4 key to convert the cell references to absolute cell references.

Step 3 – Convert Cell References in Each Formula to Absolute References

  •  Convert cell references in each formula to absolute references.

Step 4 – Now use Autofill to Paste Each Formula into the New Column

  • Now use Autofill to paste each formula to adjacent cells.
  • After you can delete the previous column and add the new column.