Copy Formula and Change Only One Cell Reference in Excel

In Excel, while working with formulas, we often have to copy or change formulas by making change in one cell reference. This article will enable you to understand how to copy a formula in Excel by making change in one cell reference and also relative and absolute reference.

📁 Download Excel File

Download the Excel file below.

What Is Absolute Cell Reference in Excel?

In Excel, an absolute cell reference is that cell along which the row and the column values remain constant while copying a formula from one cell to another. It is denoted by a $ sign. For example, for cell C2, its absolute reference can be done by writing $C$2 on the formula bar.

An absolute cell reference is essential in the sense that whenever we copy a formula in excel and paste it into another cell, the cell references change by default. If we want to keep it constant, we have to use an absolute cell reference.

📕 Read More: 3 Quick Ways to Copy Formula and Paste as Text in Excel

Learn to Copy a Formula in Excel by Changing Only One Cell Reference with These 3 Approaches

There are 3 ways that we can copy a formula in Excel by making change in one cell reference. As a matter of fact, we can understand it clearly if we can illustrate it with different examples. Suppose you have a dataset of an Apparel Business where the Owner of the business gives a commission to the Salesman upon the sale of a product. The selling prices of the products are given along with the different sales commission percentages of each product. Using this dataset, we will now explain how we can copy a formula in excel and change one cell reference with three separate examples.

Let us say that for the first example, we want to calculate the different sales commission amounts in dollars at a commission rate of 5%. When we insert a formula, say for the first product at 5% sales commission, we absolute reference the commission percentage cell and if we drag the fill handle, the cell references of product selling prices will change only, not the commission percentage cell. We can clearly understand it by following these steps:

• Firstly, let us select cell D6.
• Then we insert the following formula.

Notice that we used the dollar sign to absolute reference the cell D5. After inserting the formula, we press Enter.

• Now we press our cursor at the bottom right of cell D6 and drag it down using the Fill Handle as shown.

• We can see that the cell reference values of the selling price of products changed only on the formulas.

• Thus, in this way, the formula we can copy a formula to the dragged cells by changing one cell reference.
• The final table of results will look like the following image when all the column results are out.

📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging

Now say that you have to find the sales commission amount at different commission sale percentages for individual products. You can do this simply by applying the same formula and absolute referencing the individual products. You can follow the steps mentioned and copy the formula across rows:

• First let us select the cell D6 and insert the following formula

• Notice that we used a dollar sign to absolute reference the cell C6 for the Shirt product. The value of that cell won’t change even if we copy the formula to other cells. After inserting the formula, we press Enter.
• Now we press our cursor at the bottom right of cell D6 and a Fill Handle will appear. We drag the Fill Handle towards the right to copy the formula for getting the sales commission amount for other percentages.

• Therefore, we will get the sales commission amount for all the percentages for a particular product.

• For the next product which is a pant, we will use the following formula on cell D7.

• Notice that we are absolute referencing cell C7 Again we drag the fill handle towards the right to get the results for the pant item.

• By doing this for the individual products across different rows, we will get the sales commissions for different products. The final table will be as follows:

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel

When we need to copy a formula across rows and columns, we need to follow a mixed reference. To change one cell reference across rows and columns, we need to alter the formula slightly. These are the steps to obtain such a result with a dataset altogether:

• First, let us select the range of cells D6:F10 by selecting one of them and holding shift, and selecting the other.
• After that, we insert the following formula.

Notice the change in the following formula to the ones used previously for across row and column referencing.

• After that, instead of pressing Enter, we press Ctrl+Enter

• We will see that all the cells have been filled up. What happened with this formula is that when we are going across columns, the row references are changing and when we are going across the rows the column references are changing. This is called a mixed reference.

📕 Read More: 3 Ways to Copy Formula with Relative Reference Using Excel VBA 

Important Notes

There is a handy shortcut key for placing absolute references in the formulas and the shortcut is the F4 key. Therefore, we can press F4 key right after a cell is mentioned in a formula to absolute reference the cell.  The $ sign appears adjacent to the cell number when we press the F4 key. Pressing this key multiple times does different functions. These are as follows:

🖊️  Pressing F4 key once makes the cell absolute in both rows and columns

🖊️  If we press F4 key twice the cell will be absolute in rows but relative in columns

🖊️  When we press F4 key three times, it will make the cell absolute in columns but relative in terms of rows

🖊️  When we press the F4 button four times, the cell will become fully relative again.

📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌 Excel generally follow relative cell references when we copy any formulas from the cells.

📌 There are 3 ways to change one cell reference. We can do it across rows, across columns, or in a special way across both rows and columns by absolute referencing one cell

📌 Placing an absolute reference on a cell keeps it constant across the formulas copied across the rows and columns

Conclusion

This article will hopefully help you to understand absolute and relative references in Excel and enable you to understand how to copy a formula in Excel by changing one cell reference. If you have any further queries, you can comment on the article. Don’t forget to visit ExcelDen to get more ideas and articles related to Excel!

Related Articles

• How to Copy Excel Sheet to Another Workbook with Formulas
• 9 Examples to Copy Formula from Above Cell Using VBA in Excel
• How to Copy Formula Down in Excel Without Incrementing
• 4 Quick Ways to Copy Formula Down with Shortcut in Excel
• How to Copy a Formula in Excel with Changing Cell References