Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Copy Formula in Excel to Change One Cell Reference Only

Excel is extremely powerful for large calculations and formulas. When working with formulas, sometimes, we need to copy the formula to change only one cell reference in Excel. In this article, I will discuss how to copy a formula in Excel to change only one cell reference.


Download Sample Workbook

You can download the following workbook for practicing while reading this article.


Absolute Reference: Use and Significance of $ Sign in Excel

This dollar sign ($) in an Excel cell reference changes just one particular thing. It directs Excel on how to interpret the reference when the formula is moved or copied to other cells. In one word, using the $ sign before the row and column coordinates performs an absolute cell reference that won’t change. Without the $ sign, the reference is relative and it will change.


3 Examples to Copy Formula in Excel to Change One Cell Reference Only

Say, you have a dataset of 5 products’ names and buying prices. You have to calculate the selling prices of the products for the following profit margins. So, you need to insert a formula and copy them afterward for the other products and other profit margins. You can accomplish this by using the fill handle feature to copy the formula in Excel by changing only one cell reference.

Sample Dataset to Copy Formula in Excel by Changing only One Cell Reference


Example 1: Copying Formula Vertically

Say, you need to calculate the selling price for products with a 5% profit margin. So, if you insert a formula once, and copy the formula vertically with only changing the product cell’s reference, you can get your desired result. Follow the steps below to accomplish this.

📌 Steps:

  • First and foremost, click on cell D6 and insert the following formula.
=C6*(1+$D$5)
  • Subsequently, hit the Enter key.

Copy Formula Vertically by Changing only One Cell Reference

  • Here, you have referenced the profit margin cell as absolute. As a result, if you copy the formula only the C column cells’ references will be changed.
  • Now, place your cursor in the bottom right position of cell D6.
  • Following, drag the fill handle below to copy the formula vertically.

Drag Fill Handle Below

Thus, your formulas will be copied vertically and you will be able to get your result by copying the formula by changing only one cell reference. And, the outcome should look like this.

Copied Formula Vertically by Changing Only One Cell Reference


Example 2: Copying Formula Horizontally

Now, say, you have a dataset for similar products with 3 values of profit margins. Now, you need to calculate the selling price with every profit margin for individual products. You can do this by copying the required formula horizontally. Go through the steps below to achieve this.

📌 Steps:

  • At the very beginning, click on cell D6 and insert the following formula.
=$C$6*(1+D5)
  • Following, hit the Enter key.

Formula to Calculate Selling Price at 5% Profit Margin

  • As a result, you will get the selling price for Mango with a 5% profit margin.
  • In this formula, you have used the absolute reference for C column cells. So, if you copy the formula, the C column cell won’t change.
  • Afterward, place your cursor in the bottom right position of cell D6.
  • Consequently, a black fill handle will appear.
  • Following, drag it rightward to copy the formula for the other profit margins.

Drag Fill Handle Rightward to Copy Formula Horizontally by Changing Only One Cell Reference

  • As a result, you will get the selling price for Mango with all profit margins.
  • Similarly, for Orange, insert the following formula on cell D7 and press Enter key.
=$C$7*(1+D5)

Formula to Calculate Selling Price at 5% Profit Margin

  • Repeat the previous procedures for the other cells rightward and downward.

Copy Formula Horizontally by Changing Only One Cell Reference

As a result, you will get your desired results by copying the required formula at every row horizontally by changing only one cell reference. For instance, the result should look like this.

Copied Formula Horizontally by Changing Only One Cell Reference


Example 3: Copying Formula Across Rows & Columns

Another thing, sometimes, you might need to copy a formula quickly across rows and columns by changing only one cell reference at a time.  Follow the steps below to do this.

📌 Steps:

  • Initially, select cells D6:F10.
  • Afterward, insert the following formula in the formula bar.
=$C6*(1+D$5)
  • Subsequently, press Ctrl + Enter.

Enter Formula Across Rows and Columns by Changing One Cell Reference at a Time

As a result, you will get your required results. Here, in the formula, we have used mixed references. So, when going vertically, only row references will change and when going horizontally, only column references will change. For example, the output should look like this.

Copied Formula Across Rows and Columns


💬 Quick Notes

You can use the F4 key to toggle the cell references type.

  • If you press F4 once, the cell will be absolute.
  • If you press F4 twice, the cell will be absolute in terms of rows and relative in terms of columns.
  • If you press F4 thrice, the cell will be column-wise absolute and relatively referenced row-wise.
  • If you press F4 four times, the cell will be fully relative again.

Conclusion

So, in this article, I have shown you 3 practical uses of copying a formula by changing only one cell reference in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. And, you are very welcome to comment here if you have any further questions or recommendations regarding this article.

Visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo