When you copy formulas from one cell to another, Excel automatically changes the cell reference as per the selected cell by default. This feature helps to perform calculations for a huge range of data. But all the time this feature will not be helpful. There may be a situation when you need to fix the cell reference for performing calculations on enormous data. In this case, copying the formulas without changing the cell references is a must. In this article, I will show you how to copy and paste formulas in Excel without changing cell references.
What Is Cell Reference in Excel?
There are 3 types of cell reference a user can apply in Excel.
- Relative
- Absolute
- Mixed
The columns in an Excel worksheet are denoted by alphabets ( A B C…), and the rows are denoted by numbers (1 2 3….). When you select a cell situated in the 3rd column and 2nd row, Excel will show the cell reference as C2. This type of cell reference is relative cell reference. If you copy a formula to another cell, you will see that the cell reference has been changed automatically with an adjustment of how many cells the referred cell has traveled.
If you do not want to change the reference, then you have to use the absolute or the mixed cell reference.
Absolute cell reference means locking the row and column so that when dragging or copying the formula, the reference doesn’t change along the row or column Entering a dollar sign ($) before the row or the column freezes that specific row or column. So, putting the dollar sign ($) sign before both row and column in the relative cell reference will make it absolute (i.e. $C$2).
On the other hand, mixed cell reference means freezing either the row or the column. As one part is relative and one part is absolute, so it is called mixed type reference.
Let’s say, we are dealing with a dataset in which different employees and their monthly salaries are shown.
Here, 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 we 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.
In this section, you will find 5 effective ways to copy and paste formulas in Excel without changing cell references. I will demonstrate them one by one here. Let’s check them now!
1. Copy and Paste a Single Formula
In the dataset we are considering, the increment for “Sam” is 10% of the previous salary which is calculated by the following formula.
=C5*10%
Here,
- C5 = Previous salary of Sam
For all the employees we are considering having the same increment just like “Sam”, we want to copy and paste the formula without changing the reference we used for “Sam”. We will demonstrate this method to copy a single formula to another cell. In order to do so, proceed with the following steps.
⏩ Steps:
- First of all, double-click the cell with the formula so that the cell goes into edit mode. Here, the cell would not show the result but rather would show the formula.
- Now, take your cursor to the formula bar> select the formula> copy it from here by pressing CTRL+C.
- Now, select another cell where you want to paste the formula and press CTRL+V.
- Hence, 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.
2. Copy and Paste a Range of Formulas
When there exists a range of formulas and you have to copy and paste all of them, then you may follow several ways on the basis of the situation you face. Here, we are discussing some real-life scenarios.
2.1. Using Absolute or Mixed Cell Reference
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 found for “Sam”.
Follow the steps below for this.
⏩ Steps:
- Firstly, double-click on the cell containing the formula that you want to copy so that the cell goes into the edit mode> go to the formula input bar.
- Now, click on the cell and press F4 and the cell reference will be absolute, or just simply add dollar sign ($) before the row and column of the cell.
=$C$5*10%
This will freeze the cell and the reference won’t be changed anymore while dragging or copying.
- Then, drag the Fill Handle tool to autofill the formula below.
- Hence, 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.
If we locked the row only, then the reference of the column would vary.
2.2. Applying Find and Replace Command
By applying the Find and Replace command, you can copy and paste formulas into the Excel worksheet. So, let’s start the process like the one below.
⏩ Steps:
- Here, we want to copy the formulas of Column D and paste them into Column E.
- So, press CTRL+H to open the Find and Replace dialog box. In the Replace group, type “=” in the Find What field and put “\” in the Replace with field.
- This will replace the “=” with “\” in the formulas.
- Now, copy this modified version of the formula and paste it where you need it.(i.e. Column E)
- Now, apply the Find and Replace command again and replace the “\” with “=”
- Hence, your formula will be copied without changing the cell references. Delete the unnecessary column (i.e. D).
Read More: How to Copy and Paste Multiple Cells in Excel
3. Applying a Shortcut to Copy Formula
You can apply Excel shortcuts for copying formulas in certain criteria. 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, then select that cell and press (CTRL+’). This will copy the formula without changing cell reference and leave the new cell in editing mode.
4. Copy and Paste to Another Sheet
The worksheet contains some data just like the image below. Column D of this sheet contains formulas.
If you want to copy this whole dataset comes the formula, then follow the steps below.
- First, copy the dataset by pressing CTRL+C.
- Then, 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.
Read More: How to Copy Multiple Cells to Another Sheet in Excel
5. Copy and Paste to Another Workbook
You can also copy and paste data formulas without changing the cell references from 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. Now, select 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. And that’s why we have to mention [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 then you have to mention the source file path before typing the reference of another workbook. Here, in this case, if Book1 is not open then the commands in the function bar will be-
=('C:\Users\88019\Desktop\[Book1.xlsx]Sheet1'!C5*10%)
You can copy the formula and apply it to your worksheet. Ensure that the location of your own extension file or the source path of your reference workbook is mentioned properly.
Download Practice Workbook
You can download the practice book from the link below.
Conclusion
In this article, I have shown you some methods of how to copy and paste formulas in Excel without changing cell references. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.
Related Articles
- How to Copy Only Highlighted Cells in Excel
- How to Copy and Paste in Excel and Keep Cell Size
- How to Copy Formatting in Excel
- How to Copy Formatting in Excel to Another Sheet
- How to Copy and Paste Without Changing the Format in Excel
- How to Copy and Paste in Excel without Formulas
- How to Copy Above Cell in Excel
