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.

**Table of Contents**hide

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

**Similar Readings**

**How to Copy SUM Formula in Excel****How to Copy Formula Down with Shortcut in Excel****How to Copy Formula in Excel Without Dragging**

## 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

**How to Copy Formula Down Without Incrementing in Excel****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 Formula and Paste as Text in Excel****How to Copy Formula to Another Sheet in Excel****How to Copy and Paste Formulas from One Workbook to Another in Excel****[Fixed] Excel Not Copying Formulas, Only Values****VBA to Copy Formula from Cell Above in Excel****Excel VBA to Copy Formula with Relative Reference**