Excel is extremely powerful for large calculations and formulas. When working with formulas, sometimes, we need to copy 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.
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 coordinate performs an absolute cell reference that won’t change. Without the $ sign, the reference is relative and it will change.
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.
1. Copying Excel Formula Vertically by Changing Only One Cell Reference
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.
- 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.
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.
Read More: How to Copy Exact Formula in Excel
2. Copying Excel Formula Horizontally and Changing One Cell Reference Only
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.
- 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.
- 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)
- Repeat the previous procedures for the other cells rightward and downward.
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.
3. Copying Formula Across Excel Rows & Columns Changing Only One Cell Reference
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.
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.
Read More: Copy a Formula in Excel with Changing Cell References
💬 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.
Download Sample Workbook
You can download the following workbook for practicing while reading this article.
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. Thank you!
Related Articles
- Creating and Copying Formula Using Relative References in Excel
- How to Copy a Formula in Excel Without Changing Cell References
- How to Copy Formula to Entire Column in Excel
- How to Copy SUM Formula in Excel
- How to Copy Formula Down with Shortcut in Excel
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
