Do you need to copy a formula or function in Excel with or without changing cell references? There are lots of ideas and techniques to do it without encountering any hassle.

In this article, we will demonstrate how to copy formulaÂ in Excel with changing cellÂ references.Â Here youâ€™ll find a list of methods through which youâ€™ll be able to copy any sort of formulas or functions with changing cell references.

Here, we will explain **14** suitable methods to copy a formula in Excel with changing cell references. Itâ€™s not a major context at all but when youâ€™ll have to copy a formula to use elsewhere in the spreadsheet you need to change the input data for another cell, right?

Basically, cell reference will do this trick automatically, and if you need to lock this cell reference so that you donâ€™t want to change the input values by column or rows then youâ€™ll find the process in this article too.

**Watch Video â€“ Copy a Formula in Excel with Changing Cell References**

**Table of Contents**hide

## 1. Dragging Fill Handle Icon to Copy a Formula in Excel with Changing Cell References

Suppose a company has decided to increase **10%** salary of its **five** specific employees and here you see the chart with employeesâ€™ names along with their current salaries.

Now, youâ€™ve to find out what will be their new salaries after a **10%** increase.

Here, due to a 10% increase in the salary of each employee, you have to **multiply each oneâ€™s current salary by 1.1** to find out the new salary.

- To do this, firstly select
**cell D5.** - Now tap
**â€˜=â€™**, then select the**cell C5**and multiply with**1.1.**

What youâ€™ll be typing can be shown in the **Formula Box** which has been boxed with red at the top.Â

- Now, press
**ENTER**key and youâ€™ll see the new salary for**Sam**in**cell D5.**

- After that, to check the new salaries of all other employees, first of all, point your mouse cursor to the
**bottom right corner**of**cell D5.**Here, youâ€™ll notice a**â€˜+â€™**sign there. Which is called the**Fill Handle**Â icon. - Then, click it with your mouse, and without releasing the button drag it down to
**cell D9**and then release it there.

Thus, youâ€™ll get the amounts of new salaries of all employees. This method is called **â€˜Fill Downâ€™** as youâ€™re filling other cells by dragging with the reference of the** 1st** cell.

**Read More: How to Copy Exact Formula in Excel**

## 2. Double Clicking Fill Handle Icon to Copy a Formula with Changing Cell References

As well as you can evaluate the increased salaries of all employees by **double-clicking** the **â€˜+â€™** sign.

- Firstly, you have to do the calculation for the
**1st cell (D5)**only like before. - Secondly,
**double-click**on the**â€˜+â€™**icon.

As a result, you will see all employeesâ€™ salaries at once.

**Read More:** **How to Copy SUM Formula in Excel**

## 3. Creating an Excel Table to Copy a Formula with Changing Cell References

This is another very good option to copy aÂ formulaÂ with changing cell references.

- Firstly, select the whole section shown below.
- Secondly, from the
**Insert**tab >> choose the**Table**option.

Subsequently, a dialog box of **Create Table** will appear.

- Next, select the data for your table.
*Which will be auto-selected.* - Here, make sure that you mark the â€œ
**My table has headers****â€œ**. - Then, press
**OK**.

At this time, the table will appear with headers.

- Now, go to the
**cell D5**, put the**â€˜=â€™**symbol, select**C5,**and multiply it with**1.1**like before.

- Lastly, press
**ENTER**key and you will get the results as follows.

## 4. Copying Formula to Non-Adjacent Cells in Excel

For example, sometimes there can be gapsÂ among the rows or even columns in a chart.

Now, if you use the **â€˜Fill Downâ€™** method here **D6 and D9 cells** will show** 0** as results or even can display error messages as no calculation will be executed there.

- Thus, in this case, you have to
**copy****cell D5**first by right-clicking the mouse on it.

- Then, youâ€™ll enter
**CTRL**and without releasing it, continue to select the**D7**,**D8**,**D10**, and**D11**cells.

- After that,
**right-click**the mouse again and choose the**1st option**of**Paste****Options**.

As a result, youâ€™ll get your desired results at the right places easily.

**Read More: How to Copy Formula Down with Shortcut in Excel**

## 5. Using Single Formula for Multiple Columns at Once in Excel

Sometimes you have to perform column or row calculations with similar data but with different multipliers. Here, letâ€™s find out what will be the salaries of the employees with both **10% **and** 20% **increases.

So basically, youâ€™re going to use one commonÂ formula,Â but you have to use it for two different columns. Actually, in **row 11**, there are two multipliers under the perspective columns to make calculations easier.

- Firstly, youâ€™ll select the
**array D5:E9**with a mouse.*Here, if you are using an older version of***Excel**than**2013**then press**F2**to enable editing at**D5. Furthermore, we used the****Microsoft 365 version**here.

- Now, only type the formula to multiply
**cell C5**with**D11**(1st multiplier) but donâ€™t execute the function right now.

Actually, you have to lock column** C** as well as **row 11** by using the Dollar** ($)** sign before them inside the Formula Box to ensure the proper calculations under mixed cell references. Here, weâ€™ll guide you through this â€˜Mixed Cell Referencesâ€™ a few methods later.

Now, you just have to keep in mind that youâ€™re using this **â€˜$â€™** symbol to lock the multiplier row (**11**) for their respective columns, and in the same way, youâ€™re locking Current Salary (Column** C**) to ensure the perspective calculations of increased salaries in** two** different cases for each employee.Â

After youâ€™re done with these processes.

- Lastly, press the
**CTRL+ENTER**keys instead of pressing only**ENTER**and youâ€™ll be able to see all the new and increased salaries in both cases of all employees.

**Read More: How to Copy Formula in Excel Without Dragging**

## 6. Using Fill Handle Icon Differently to Copy a Formula with Changing Cell References

Letâ€™s look at a **Series** now.Â Basically, itâ€™s a series thatâ€™ll contain multipliers of **5**. How will you get all the next values in the column with a single click?

- Firstly, select cells including
**B5 to B8**, keep the mouse pointer to the right bottom corner of the**B8 cell,**and then**Fill down**the series.

As a result, youâ€™ll get all the required values for the multipliers for **5**. Basically, Excel is assigned to **Fill Series** as default.

Now, letâ€™s check other **Copy** options. Here, if you choose **Copy Cells**, the **1st 4** cells youâ€™ve selected before will be copied downward again and again.Â

And if you opt to choose** Fill Formatting** **Only**, here only the cell pattern or background will be copied, not the values.

Furthermore, if you select **Fill Without Formatting**, youâ€™ll get the whole series, but the background of the cell patterns will not be copied here.

Last of all, this is probably the most interesting part. Suppose you want to get only first names from a list of full names.

- Now, in the column of
**First Name**, type it once for the**1st**one only. - Then, drag the
**Fill Handle**icon.

- After that, select
**Flash Fill.**

And all the first names will be displayed in the column right away.

**Read More: How to Copy Formula Down Without Incrementing in Excel**

## 7. Applying Different Paste Options to Copy Formula in Excel

Like** Copy** options, almost similar or more options youâ€™ll find in **Paste** options too. Here, you can use the **Paste** options to copy a formula in Excel with changing cell references

- If you want to copy
**column D to F,**then select the column range you want to copy, and it includes formulas too. But you have to lock**column C**otherwise during pasting at**column F,**values from**column C**wonâ€™t appear here, rather the empty cells in**column F**will want to be multiplied with**1.1**and the error messages will be shown.

- Now,
**right-click**the mouse on**cell F5**and youâ€™ll find a variety of**Paste**options.

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

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

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

Moreover, there are some other **Paste** options that you can find through **Paste Special** tab.

Here, you can select a wide variety of options to paste the values or formulas or both depending upon your required criteria.

**Similar Readings**

**Creating and Copying Formula Using Relative References in Excel****How to Copy Formula in Excel to Change One Cell Reference Only****How to Copy a Formula in Excel Without Changing Cell References**

## 8. Copying Function(s) from Formula Bar in Excel

You can even copy the formula from **Formula Bar** directly and use it in any cell you want.

- Here, at first, calculate the total current salaries of
**5**employees by using**the SUM function**.

- Now, focus on the
**Formula Bar**and**Cut**this formula instead of copying.*Here, if you copy this formula you have to lock the cell reference to paste elsewhere.*

- After that, paste it on
**cell E7**.

As a result, youâ€™ll get the calculated value with the formula there.

But since you cut the formula so the value from the **cell C11** will have vanished, and youâ€™ll have to paste the same function again on the **cell C11** and the calculated value will be back.

**Read More: [Fixed] Excel Not Copying Formulas, Only Values**

## 9. If You Donâ€™t Want to Change Cell References to Copy a Formula in Excel

This is a tricky part, but youâ€™ll find it interesting, I hope. Eventually, Iâ€™ve already given a bit of an idea on this topic when discussing the** 5th** method where you had to copy **one single** formula to use for **two** different columns.

Now, letâ€™s take a look in detail now. Like before, youâ€™ll have to find out the increased salaries both with **10%** and **20%** increments.Â

- Firstly, select
**Array D5:E9**. - Secondly, multiply
**C5**with**D11**in the**D5**cell.

- Now, press the
**CTRL+ENTER**keys.

Subsequently, youâ€™ll see the calculation has been made only for Sam, but others have been denied.

Here, the reason is you havenâ€™t locked the cell references. So, how and when will you lock them?!

Before knowing that, letâ€™s have a glance at **cell D6. **Now, go to the** Formula Bar** and youâ€™ll see the calculation has been executed based on the multiplication between Robertâ€™s current salary**(C6)** and an empty cell** (D12)!**

Basically, this is why the result has been displayed as** $0**.

So, youâ€™ve already got that here cell references are moving from cell to cell, and you have to lock these cell references now based on the required criteria.

- Now, letâ€™s select the whole array
**(D5:E9)**again. - Then, go to the
**Formula Bar.**Here, put aÂ Dollar**($)**sign before**C**, this will lock**column C,**and put it again before**11**, itâ€™ll lock the**11th row**containing the multipliers which you donâ€™t want to circulate over the whole Excel sheet. - Subsequently, press
**CTRL+ENTER**& youâ€™ll have your expected calculations.

So, whatâ€™s happening here?Â

As youâ€™ve locked **column C** and **row no. 11**, now under **column D**, all employeesâ€™ salaries will be multiplied by **1.1** from** D11** and similarly, under **column E**, salaries with **20%** increments will be obtained for the multiplier **1.2** from **E11**.

## 10. Using Show Formulas Feature to Copy a Formula with Changing Cell References

To follow this method here, you have to calculate the increased salaries first by filling down the cells like before.Â

- Firstly, under the
**Formulas**tab, click on**Show Formulas.Â**

As a result, under **column D**, youâ€™ll see the functions executed in each cell.

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

So, youâ€™ll get all the desired values at once.

If you opt to choose other **PasteÂ ****Options,** then you will see the formulas under** columnÂ ****F **rather than the values as we kept on the **Show Formulas** button. Furthermore, you can paste these formulas anywhere in the Excel sheet to copy the calculated values.

## 11. Applying â€˜Find & Replaceâ€™ Button to Copy a Formula in Excel

This is really a fruitful method, and you can find more fun here. Here, in** column D** initially, like the previous method, formulas are exposed.Â

- Firstly,Â select the column range
**D5:D9.** - Secondly,Â from the
**Home**tab >> go to**Editing**menu.Â - Thirdly, weâ€™re going to choose the
**Replaceâ€¦**option from the**Find & Replace**feature.

At this time, a new dialog box named **Find and Replace** will appear.

- Next, you have to
**replace**the**â€˜=â€™ symbol**with**â€˜/â€™**or any other symbol which has not been used already in the spreadsheet. - Then, tap
**Replace All.**

Subsequently, a new box of **Microsoft Excel** appears.

- Now, press
**OK**on it.

- After that, click on
**Close**to the**Find and Replace**dialog box.

Thus, in **column D**, the formulas will turn into text strings that you can copy anywhere you want.

- Now, letâ€™s go to
**column F**and paste**column D**Â there first.

- Then, select the whole
**column F**, and open the**Find & Replace**tab again.

Again, the dialog box named **Find and Replace** will appear.

- Similarly, reverse those symbols that youâ€™ve used before for
**column D**.

- Then, choose the option
**Replace All**again.

- Now, press
**OK**on the box of**Microsoft Excel**.

And youâ€™ll have turned the text strings into number functions with this process.

- Now, turn off the
**Show Formulas**.

Finally, you will see the calculated values in **column F**.

**Read More: How to Copy Formula and Paste as Text in Excel**

## 12. Using CTRL+D & CTRL+R for Immediate Next Cell to Fill

You can use **CTRL+D** or **CTRL+R** for the immediate next cell to fill one by one.

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

- Now, go to
**D8**and press**CTRL+R**to move to the right.

Basically, this method can be useful for a very small amount of data calculation.

## 13. Creating a Notepad and Paste Formula Column to Use Later

You can copy a formula with changing cell references using **Notepad**. To do so, at first, you have to expose the formulas again. Here, you can expose the formula using the** Show Formulas** feature as before.Â

- Now, copy the formulas from
**column D**to a**Notepad**.

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

- Turn off the
**Show Formulas**tab and youâ€™ll get the values again.

This method is quite effective when you have to keep track of the calculated data, but you cannot expose the formulas while copying those values to another column. So, you got how to** copy a formula** in Excel with changing cell references using **Notepad**.

## 14. Choosing â€˜AutoSumâ€™ or â€˜SUBTOTALâ€™ Function in Excel

If you donâ€™t want to type any function manually you can choose this **AutoSum** option under the **Home **tab where youâ€™ll get your desired functions for basic and simple calculations. Basically, with the help of this **AutoSum** feature, you can find a summation, average, count, maximum value, minimum value, and so on.

- To use theÂ
**Sum**feature from**AutoSum**, select any cell. - Then, go to the
**Home**tab >> select**EditingÂ**>> from**AutoSum**>> finally selectÂ**Sum**.

Here, we did the summation for **Column C** with the help of the **AutoSum** feature.

**The SUBTOTAL function** is another similar function you can use to find out the results by going through various**Â parameters** from the list like **9** denotes the **SUM** function.

Also, we did the summation for **Column C** with the help of the **SUBTOTAL** function.

**Download Excel Workbook**

Download the Excel Workbook that we used to prepare this article so that you can practice yourself.

## Conclusion

So, here we have described how to copy a formula in Excel with changing cell references. There are all the basic techniques you need to know and use in your daily Excel activities when youâ€™re assigned to analyze tons of data. Youâ€™re welcome to let me know any kind of suggestion or opinion in the comment box. Iâ€™ll catch you up with your valuable words!

## Related Articles

**How to Copy Formula to Another Sheet in Excel****How to Copy and Paste Formulas from One Workbook to Another in Excel****VBA to Copy Formula from Cell Above in Excel****Excel VBA to Copy Formula with Relative Reference****How to Copy a Formula across Multiple Rows in Excel****How to Copy Formula to Entire Column in Excel**