Are you having difficulties copying a formula with changing cell references in Excel? Do you want to learn How to copy a formula in excel with changing cell references? We offer you some techniques and share ideas to ease your task.

This article will show how to copy a formula in Excel with changing cell references. The solutions listed below will enable you to copy formulas or functions while changing the cell references.

## 📁Download Excel File

Download this Excel file we used to create this article, and you can practice it yourself and can copy a formula in excel with changing cell references.

## Learn to Copy a Formula in Excel with Changing Cell References with These 10 Suitable Examples

The 10 useful methods to copy a formula in Excel with changing cell references are explained here.

**Example 1**

### Using Fill Handle Icon

Suppose a shop has decided to increase the price of five specific fruits, and what you’re viewing here is the chart with the names of those fruits and their current prices. Now, you want to calculate the new prices after a 5% increase.

Only by using the fill handle icon in two different ways, it’s possible to find all data for five rows together.

**Example 1.1**

### Dragging Fill Handle Icon

One way to determine a **5%** increase in the prices of the fruits is to** multiply each current price by 1.05**.

**STEPS**⬇️⬇️

To execute that-

- Select
**cell D5**first. - Write down the following formula.

`=C5*1.05`

The **formula box** with a red rectangular on top will show you what you’ve typed.

- Now, by pressing the
**Enter**key, you can see the new price of Apples in cell**D5**.

- Again, to determine the new prices of all other fruits, point your mouse cursor to the bottom right corner of cell
**D5**, and you’ll see a ‘**+**‘ sign. This icon is what we call the Fill Handle. - After that, click that with your mouse and drag it down, not releasing that button until cell
**D9**. After reaching cell**D9**, release it.

- Then you” ll get the increased prices of all the fruits. As we’re filling other cells by dragging with the reference of the 1st cell, this method is known as
**‘Fill Down’.**

**📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging**

**Example 1.2**

### Double Clicking Fill Handle Icon

Double-click the** ‘+’** icon to evaluate the increased price of fruits.

**STEPS**⬇️⬇️

- First, do the calculation for the first cell
**D5**only like the previous way. - After that, double-click the
**‘+’**sign.

- You’ll be seeing all the increased prices of fruits at once.

**Example 2**

### Using Various Paste Options

There are different **paste **options and you can use these to **copy **a formula in **Excel** with changing cell references.

*⬇️⬇️*

**STEPS**⬇️⬇️

- For copying
**columns D**to**F**, select the column that you want to copy and it will include the formula too. But you must lock**column C,**otherwise, during pasting at**column F**, values from**column C**won’t appear and an error message will be shown.

- Then,
**right-click**the mouse on cell**F5**and you’ll find a variety of**Paste**options.

The 1st one includes the formulas you used for** column D**.

**Condition 1.** Again, if you choose the **Values (123)** option then only values will be copied from **column D,** not the formulas or functions.

**Condition 2. **Furthermore, if you click the **Paste Link**, then calculated values will be shown in **column F**, this option will actually copy both values and functions assigned for **column D**.

**Condition 3. **Also, there are other **Paste** options that can be found through the **Paste Special. **

- From here, you can select a variety of options depending on your requirements.

**📕 Read More: 3 Quick Ways to Copy Formula and Paste as Text in Excel**

**Example 3**

### Utilizing Show Formulas Feature

To apply this method, the increased price must be calculated using any of the above-mentioned methods.

**STEPS**⬇️⬇️

- At first, you will find a
**Formula**tab in the ribbon; under this formula tab, click on**Show****formulas**.

- If you notice
**Column D**, you will see the functions executed in each cell.

- Then,
**copy**these formulas using the**CTRL+C**keys or right-clicking the mouse and selecting the copy option. - After that, make a new chart under
**column F**. **Paste**the formulas at**F5**by selecting the**values(V)**option.

- Following this process, you’ll get all the desired values.
- If you choose other
**Paste**opinions, you’ll be able to see formulas under**Column F**rather than values. Again, it is because we kept on the**Show Formulas**button. Also, you can use these formulas anywhere in the Excel sheet to copy a calculated value.

**Example 4**

### Copying Function from Formula Bar

Formulas can be copied directly from the** Formula Bar** and can be used in any cell.

**STEPS**⬇️⬇️

- First, calculate the total current price of the products by using
**the SUM function**.

The formula in the **selected cell**.

`=SUM(D5:D9)`

- From the
**Formula Bar**,**Cut**this formula instead of copying, because if you copy this formula you have to lock the cell reference.

- Then,
**Paste**it on cell**F7,**and you’ll get the calculated value with the formula there.

- As you’ve cut the formula so the value from cell
**C11**will vanish and you’ll have to paste the function again on cell**C11**and thus the calculated values will be back.

**📕 Read More: Copy Formula and Change Only One Cell Reference in Excel**

**Example 5**

### Creating Notepad to Paste Formula Columns

This method is helpful while keeping track of the calculated data and not exposing the formulas when copying those values to another column.

To follow this method, you must expose the formulas again using the **Show Formulas **feature from the **Formula** tab.

**STEPS**⬇️⬇️

- First, copy the formula from
**column D**to a**Notepad**.

- Next, paste the formulas from the
**Notepad**in**column F**.

- After that, if you turn off the
**Show Formulas**tab, you’ll be able to see the values again.

**Example 6**

### Use of Find & Replace Command

This method is a very effective one. In **column D,** the formulas are to be exposed because of using the **show formula **feature.

One thing you should bear in mind, when you click show formula, the format of your data-set will change. Here, in this case, the currency sign in 2nd column is gone, and alignment has also changed.

*⬇️⬇️*

**STEPS**⬇️⬇️

- First, select the range,
**D5:D9**array. - Go to the
**Home**tab, and from there go to the**Editing**menu. - Then, we’ll be choosing the
**Replace**option from the**Find & Replace**feature. As a result, a dialogue box named**Find and Replace**will appear.

- After that,
**replace**the “**=**” symbol with “**/**” or any other symbol according to your choice which is not been used yet. - Click the button
**Replace All,**and a new dialogue box will appear.

- Click
**OK**on that.

- Next, click the
**Close**to the**Find and Replace**dialogue box. - Due to this, the formulas in
**column D**will turn into text strings that can be copied anywhere you want. - Then, select the whole column F and open the
**Find & Replace**tab again, and the dialogue box will appear again.

- Now, reverse those symbols that you used earlier for
**column D**. - Again, choose the option
**Replace All**.

- Now press
**OK**on the dialogue box.

- Therefore, you’ve turned the strings into number functions with this process.

- Turn off the
**Show Formulas**, and you will be able to see the calculated values in**column F**.

**Example 7**

### Utilizing SUBTOTAL or AUTOSUM Function

To get rid of typing any function manually, you can use the **SUBTOTAL** and **SUM** /** AUTOSUM **functions, which you can find under the home tab. The **AUTOSUM **function may be displayed as **SUM** in some versions, but they are the same. So to use this feature,

*⬇️⬇️*

**STEPS**⬇️⬇️

- Firstly, select any cell.
- Secondly, follow this sequence :
**Home**tab >> select**Editing**>>from**AUTOSUM**or**SUM**>> select**SUM**

- We’ve done the total for
**Column C**with the help of the**SUM**feature.

The formula used in the selected cell

`=SUM(C5:C10)`

- Another similar function to mention is
**the SUBTOTAL****function**, which can be used by going through different parameters from the list, where**9**is for the**SUM**function.

- Therefore, we did the summation for
**Column C**with the help of this function called the**SUBTOTAL.**

The formula used in the selected cell.

`=SUBTOTAL(9,D5:D9)`

**Example 8**

**Creating Excel Table to Copy a Formula**

Another good option is to copy formulas by changing the cell references. But in this method, a table must be created first.

**STEPS**⬇️⬇️

- First, you have to select the
**whole section**. - Then, click the
**Insert**tab and select the**Table**option from there. - A dialogue box
**Create Table**will appear.

- The next step is to select the data for your table. Which will be auto-selected.
- Make sure that you mark the “
**My table has headers**”, and press**OK**.

- A table will appear with headers.

- Go to the cell
**D5**and put the symbol “**=**” , select**C5**and multiply this with**1.05**(to find the 5% increase)

The formula used in the selected cell

`=[@[Current Price per kg]]*1.05`

- Finally, press
**Enter**key to get the results as follows.

**📕 Read More: How to Copy Excel Sheet to Another Workbook with Formulas**

**Example 9**

**Applying One Formula for Multiple Cells**

This method can be used when you have to perform column or row calculations with similar data but with different multipliers. Here, if you want to find the prices of the products with both **5%** and **10%** increases, you will be applying this method.

So, when using one formula for two different columns, this method is a perfect fit. In** row 11 **there are two different multipliers under the prospective column to make your calculation easier.

*⬇️⬇️*

**STEPS**⬇️⬇️

- First, select the array
**D5:E9**by mouse, but if you are using any previous version of**Exce**l than**2013**then press**F2**to enable the editing at**D5.**Here we’ve used the**Microsoft 365**version.

- Then, to multiply cell
**C5**with**D11**, only type the formula, but don’t execute the function right now. - You have to
**lock column C**and**row 11**by using**($)**, the**Dollar**sign, just before the rest, inside the Formula box. But keep in mind that the “**$”**symbol is used to lock the multiplier row (11) for their respective columns, and using the same way you’re locking**column C**to ensure the calculations of increased prices in two different cases.

The formula used in the selected cell

`=$C5*D$11`

- Next, instead of pressing only
**Enter**press the**CTRL+Enter**keys and the new and increased prices will be shown.

**📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel**

**Example 10**

**Using CTRL+D & CTRL+R Keyboard Shortcut**

**CTRL+D** or **CTRL+R** can be used to fill the immediate next cell, but only if the amount of data calculation is minimal.

*⬇️⬇️*

*STEPS**⬇️⬇️*

- After the initial calculation is completed in
**D5**, go to**D6**and use**CTRL+D**for downward calculation.

- Then, go to
**D8**and use**CTRL+R**to move it to the right.

**📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel**

## 📝 Takeaways from This Article

From this article,

- The readers will be able to copy any formulas or functions along with changing the cell references in Excel.
- Readers will have an overview of the different features of Excel, that can be used while handling and analyzing tons of data effectively.

## Conclusion

So, in this article, we’ve covered** how to copy a formula with changing the cell references** in Excel. You’re welcome to let me know any kind of suggestion or opinion about this article in the comment box below. I’ll catch you up with your comments soon. Don’t forget to visit our **Excelden** page to get more Excel-related updates.

## Related Articles

**9 Examples to Copy Formula from Above Cell Using VBA in Excel****3 Ways to Copy Formula with Relative Reference Using Excel VBA****How to Copy Formula Down in Excel Without Incrementing**