How to Copy a Formula in Excel with Changing Cell References 

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.

Using Fill Handle Icon


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.

Dragging Fill Handle Icon

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

Dragging Fill Handle Icon

  • 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.

Dragging Fill Handle Icon

  • 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.

Double Clicking Fill Handle Icon to copy a formula in excel with changing cell references

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

Double Clicking Fill Handle Icon


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.

Using Various Paste Options copy a formula in excel with changing cell references

  • 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.

Using Various Paste Options 

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.

Using Various Paste Options to copy a formula in excel with changing cell references

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

Using Various Paste Options to copy a formula in excel with changing cell references

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

Using Various Paste Options 📕 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.

using show formulas

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

Utilizing Show Formula Feature to copy a formula in excel with changing cell references

  • 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.

Utilizing Show Formula Feature to copy a formula in excel with changing cell references

  • 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.

Utilizing Show Formula Feature to copy a formula in excel with changing cell references


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)

Copying Function From Formula Bar

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

Copying Function From Formula Bar

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

Copying Function From Formula Bar

Copying Function From Formula Bar

  • 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.

Copying Function From Formula Bar

📕 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.

Creating Notepad to Paste Formula Columns for Later Use

  • Next, paste the formulas from the Notepad in column F.

Creating Notepad to Paste Formula Columns for Later Use

  • 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.

Find and replace command to copy with changing cell references

  • 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.

copy and changing cell references by find and replace

  • Click OK on that.

copy and change sell references

  • 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.

Find and replace command to copy with changing cell references

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

copying formula in excel with find and replace

  • Now press OK on the dialogue box.

copy and change sell references

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

Copying formulas with changing cell references in excel with find and replace

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

How to copy a formula in excel with changing cell references by find and replace


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 

How to copy a formula in excel with changing cell references using SUBTOTAL and SUM

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

How to copy a formula in excel with changing cell references using SUBTOTAL and SUM

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.

How to copy a formula in excel with changing cell references using SUBTOTAL and SUM

  • 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)

copy a formula in excel with changing cell references using sum and subtotal


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.

copy a formula in excel with changing cell references by creating a table

  • 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.

copy a formula in excel with changing cell references by creating a table

  • A table will appear with headers.

copy a formula in excel with changing cell references by creating a table

  • 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

copy a formula in excel with changing cell references by creating a table

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

Using table copy a formula in excel with changing cell reference

📕 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.

 copy a formula in excel with changing cell reference using multiplier

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

Applying One Formula for Multiple Cells copy a formula in excel with changing cell references

  • 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.

Using Keybord Shortcut

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

Using Keyboard Shortcut

📕 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

(Visited 151 times, 1 visits today)
Lamisa Musharrat

Lamisa Musharrat

Hey there! I am Lamisa Musharrat, a Marine Engineer and a Content Developer. I love to help people by writing articles for them. Hope you will enjoy my blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo