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.
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.
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.
To execute that-
- Select cell D5 first.
- Write down the following formula.
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
Double Clicking Fill Handle Icon
Double-click the ‘+’ icon to evaluate the increased price of fruits.
- 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.
Using Various Paste Options
There are different paste options and you can use these to copy a formula in Excel with changing cell references.
- 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.
Utilizing Show Formulas Feature
To apply this method, the increased price must be calculated using any of the above-mentioned methods.
- 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.
Copying Function from Formula Bar
Formulas can be copied directly from the Formula Bar and can be used in any cell.
- First, calculate the total current price of the products by using the SUM function.
The formula in the selected cell.
- 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.
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.
- 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.
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.
- 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.
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,
- 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
- 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.
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.
- 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.
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.
- 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.
- 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
- Next, instead of pressing only Enter press the CTRL+Enter keys and the new and increased prices will be shown.
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.
- 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.
📝 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.
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.
- 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