By default, the references in Excel are Relative. This means when a formula is copied from one cell to other, the references in the formula will update according to the position of the cell in to which the formula is pasted.
We have the following formula in the cell C4 of a worksheet.
When the cell containing the formula is copied into the cell F7, the references in the formula updated. C2 and C3 became F5 and F6.
=C2*C3 became =F5*F6
Following are 3 methods using which we can Copy and Paste formulas without changing the cell references.
Table of Contents
Copy and Paste Manually
Suppose we need to copy and paste formulas from a few cells, it is better to do it manually.
To copy a formula,
select the cell containing formula > go to the Formula bar > copy the formula
Once we have copied the formula as it is, select the destination and paste it there.
This method will be handy when you have a bunch of formulas that needed to copied and pasted without changing references.
Here, our aim is to copy the formulas in the column F to column H without any change in the references.
Click on the Show Formulas button in the Formulas tab of the Excel ribbon.
By default, cells in an Excel worksheet will display the result of a formula in that cell.
Once the Show Formulas feature in Excel is activated, every cell will display the content of that cell. Cells having formula will display formulas instead of results.
Copy and Paste the required formulas into the Notepad.
Copy the formulas from the Notepad and paste them into the cells where you want it.
Ctrl + ` is the keyboard shortcut for Show Formulas
Using the Find and Replace feature
In the Excel, without the ‘=’ (Equal Sign) formulas are just text strings.
In this method, the idea is to
1. Replace the Equal sign of the formulas with a dummy character and convert them into text strings,
2. Copy and paste the text strings which where formulas earlier,
3. Replace the dummy character with the Equal sign and convert the text strings back to formulas.
Select the cells containing formulas. Go to Home tab of the Excel ribbon > Find and Select
Click on Replace to activate the Find and Replace dialog.
Type in = in the input box for Find what, # in the input box for Replace with and Click on Replace All.
The Equal signs before the formulas are all replaced and the formulas have become text strings. Copy this data and paste into the cells where formulas are required.
To convert the text strings back to formulas,
select the cells containing copied and pasted data > Activate the Find and Replace dialog > Replace # with =
And now we have formulas in the required cells.
Ctrl + H is the keyboard shortcut for the Find and Replace dialog.