Copy Formula in Excel by Changing Only One Cell Reference

Copying formula in Excel only change one cell reference

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

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.

1 Comment
  1. I came across this info today – needed it for an Excel spreadsheet. Good stuff, especially your para explaining use and significance of the $ sign. However, I found your explanations in a couple of instances confusing, as explained below.

    Copying Formula in Excel only Change One Cell Reference
    Step 1: “Enter the formula in the first cell…”
    Reader might interpret “first cell” to be B3. Say “C3” rather than “the first cell.”

    Step 3: “…drag over the cells up to the destination.”
    Arrow points up to where? Looks like the top left corner of D5. What is “destination? Tell reader exactly what is destination. Give specific cell reference(s).

    Step 5: “Do the same for the other columns.” Vague. Do you mean repeat from step 1? Step 2? You have the nice spreadsheet example graphic – use the cell references to again specify exactly where to “find the fill handle…” when doing the “D” column, etc.

    I only used the first section so have no comments regarding what follows.

    I suggest that you assume your readers are a little slow (I qualify to that) and require everything to be spelled out. Always give cell references, which cannot be misunderstood. Experienced users of Excel (and most computer skills) may forget how confusing it all was when they first started using computers, and so overlook explaining the very basic things that confuse beginners.

    I do not need a reply, but I hope you find this helpful.

Leave a reply

ExcelDemy
Logo