When you copy the formula and paste it to another cell the reference of cells do not gets changed?

Sometimes we may need to copy formulas in MS Excel where there is a need to change only one cell reference. Generally, most of the time, we need to change all cell references in our copied formula. Here I will show some methods how to copy any formula by changing only one cell reference.

This problem can be solved using the idea of cell reference concepts in excel. And this is applicable when we work with more than one column in excel, where the only one will be changed when copying the formula.

Read More: How to Copy a Formula in Excel with Changing Cell References

Download the Practice Workbook

Use and Significance of $ Sign in Excel

This dollar sign ($) in an Excel cell reference changes just one particular thing. It directs Excel 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.

Copying Formula in Excel only Change One Cell Reference

The example shows the whole process. Suppose I have some Product list along with their Price. Now I need to calculate the price of each product using the different Commission rates. I will calculate the price with different commission rates in excel using a simple formula with changing one cell reference.

Step-1: Enter the formula in the first cell and press enter.

Step-2: Find the fill handle in the lower-right corner of the first cell.

Step-3: Click on the fill handle and drag over the cells up to the destination.

Step-4: The formula will be copied to the selected cells after releasing the mouse.

Step-5: Do the same thing for the other columns.

For the “D” Column:

For the “E” Column:

That’s how we can calculate the price after commission using this simple formula by changing one cell reference. The B column cell value is always changing as I did not put any ‘$’ in front of any row or column value to make it a relative cell reference. For D, C, E, I have used two ‘$’ signs, making it a fixed cell or absolute cell reference value.

For more Practice, here is the excel file for download.

Copy a Formula Down without Copying Formatting by Changing One Cell Reference

Here I will show how to copy any formula with one cell reference without changing the format. By default, when we copy any copy from any cell to another cell, the formula and the format of the cell will also be copied like background color, a symbol of the currency, decimal places, etc. That’s why there may be a need to copy formulas without formatting.

Let’s take the example using the above statement. Now let’s consider when we will calculate the price after commission. The first cell is colored, and we don’t want to copy the color of the first cell, just only the formula of the first cell.

Step-1: Enter the formula in the first cell

Step-2: Click on the fill handle and drag over the cells up to the destination.

Step-3: The formula will be copied to the selected cells after releasing the mouse.

Step-4: Click on the “Auto Fill Option”

Step-5: Select Fill Without Formatting.

Step-6: Formula will be copied without formatting.

Conclusion

These are the ways to copy any formula with one cell reference change in MS excel. Here only the methods are only for two columns. It is also applicable when the column number is more than that. Besides here it also showed the process of copying formulas with or without changing the format.

Further Readings

• Shortcut to Copy Formula Down in Excel (7 Ways)
• How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)
• How to Copy Down Excel Formula without Incrementing (3 Easy Ways)