How to Copy and Paste Formulas Without Changing Cell References in Excel

Let’s say we are dealing with a dataset in which different employees and their monthly salaries are shown. The first name “Sam” is the project manager. We assumed that after a certain period, the salary of the employees will be increased by 10% of the salary of “Sam” (not the percentage of their own salary). We need to find that increment and want to copy a specific formula that is used to determine the increment of “Sam” and paste this formula to find the increment of the other employees.

Method 1 – Copy and Paste a Single Formula

The increment for “Sam” is 10% of the previous salary which is calculated by the following formula.

=C5*10%

C5 = Previous salary for Sam

Steps:

Double-click the cell with the formula so that the cell goes into edit mode.
Select the formula from the formula bar and copy it by pressing Ctrl + C.

Select another cell where you want to paste the formula and press Ctrl + V.
Your formula will be pasted just like it was applied for the previous cell without changing the cell references.

Repeat this process for all the cells where you want to copy and paste a single formula.

Note: This process is one kind of manual approach as you have to paste the formula to every cell individually one by one.

Method 2 – Copy and Paste a Range of Formulas

Case 2.1 – Using Absolute or Mixed Cell References

For the dataset, let’s apply the absolute cell reference first to calculate the 10% increment for the other employees which is the same as the one for Sam.

Steps:

Double-click on the cell containing the formula.
Click on the cell and press F4 or add dollar signs ($) before the row and column of the cell references to make them absolute.

=$C$5*10%

Drag the Fill Handle tool to autofill the formula below.

Excel will copy the formula to the other cells. If the cell wouldn’t be locked, it would change the reference during dragging. But now, the formula will be the same and the reference won’t be changed anymore.

You can also apply the mixed reference. To find the current salary of Sam after a 10% increment, the following formula was applied:

=C5+$C5*10%

Drag the formula below. Notice that we have locked only the column here ($C5), so the row number will change while dragging or copying.

Case 2.2 – Applying the Find and Replace Command

Steps:

We want to copy the formulas of Column D and paste them into Column E.

Press Ctrl + H to open the Find and Replace dialog box.
In the Replace tab, type “=” in the Find What field and put “\” in the Replace with field.
Hit Replace All.

Copy this modified version of the formula and paste it where you need it (i.e. Column E).

Apply the Find and Replace command again and replace the “\” with “=”

Your formula will be copied without changing the cell references.
Delete the unnecessary column (i.e. D).

Method 3 – Applying a Shortcut to Copy the Formula

The following image contains one formula.

If you want to copy exactly this formula without changing the cell reference just down to the next cell, select that cell and press (CTRL+’). This will copy the formula without changing cell reference and leave the new cell in editing mode.

Note: Don’t confuse (CTRL+`) with (CTRL+’)

Method 4 – Copy and Paste to Another Sheet

The worksheet contains some data just like the image below. Column D of this sheet contains formulas.

Copy the dataset by pressing Ctrl + C.

Select a cell in another sheet where you want to paste and press Ctrl + V. You will see the formula just like the previous sheet without any cell references.

Method 5 – Copy and Paste to Another Workbook

Let’s say, we have a dataset containing formulas in a workbook (i.e. Book1).

We want to copy and paste these formulas into another workbook while the main workbook is opened.

Apply the following formula in the workbook where you want to copy and paste the formula from the main workbook (i.e. Book1).

=[Book1.xlsx]Sheet1!C5*10%

So here we’re using the reference of another workbook. We have to put [Book1.xlsx]Sheet1! before typing C5 as this C5 cell is present in Sheet 1 of Book1.

If you want to add a reference to another workbook that is closed, you have to mention the source file path before typing the reference of another workbook. In this case, if Book1 is not open, the function would read something like this:

=('C:\Users\88019\Desktop\[Book1.xlsx]Sheet1'!C5*10%)