How copy formula without changing cell reference in Excel

The goal of this lesson is to teach the user how to control cell reference addresses in formulas when copying or moving them.

Depending on whether a formula is copied or moved in Excel, the addresses of its references may differ significantly. This is something to always keep in mind!



Examples Excel formula copy without relative reference

Quick Formula Copying

Let's explore the specifics of changing references in formulas using a ready example. Before copying the formula in Excel, create a simple table on a sheet, as shown in the figure:

Table with Formulas

Copy the values from column B (for example, using CTRL+C) and paste them into column D (CTRL+V). Then move the data from column B to E (for example, using CTRL+X).

Now switch to the formula view mode – CTRL+`(backtick). Notice how cell references behave when moving and copying.

Formula View Mode

When moved (in column E), the references remain unchanged. However, when copied (in column D), they automatically adjust.

Copying Formulas in Excel with Adjusted References

When copying, the addresses of relative references adapt to the new location. If a reference pointed to a cell one column to the left, it continues to do so, but the address naturally changes. So the formula: =A2*1.23 becomes =C2*1.23 after copying. When we move the same formula instead of copying it, the addresses of its references remain the same, even though they are relative. When moving, cell references behave like absolute references. This is something Excel users should always keep in mind.

Note. More attention will be devoted to relative and absolute references in the section on formulas. For now, note that by default, references are relative, and if the address contains the "$" symbol, it is an absolute reference.

Now let's complicate the task. Restore the table to its original state as shown in the first figure. Perform a series of consecutive actions:

  1. Copy "Sheet1," for example, using the mouse and CTRL. Hover the cursor over the tab of the first sheet. While holding down the left mouse button and the CTRL key on the keyboard, move the tab (a copy of the sheet) to a new location. Release the mouse first, then the keyboard. You now have a sheet with the same name, but with "Sheet1(2)" added.
    2. Copying a Sheet
  2. In the copy "Sheet1(2)," copy the value from B2 to D2, and move it in E2 (as in the previous task).
    3. Copying and Moving Formulas
  3. Now copy columns D:E from "Sheet1(2)" and paste them into columns D:E of "Sheet1."
    4. Changing Cell References in Formulas

As you can see, both cells D2 and E2 were copied and moved in the same way, but the references in their formulas behave differently. When copying the formulas, E2's value does not change. This is because Excel considers this a different way of assigning cell addresses in the formula. To further confirm this, restore the table on "Sheet1" to its original state as shown in the first figure.

This time, copy the formula from B2 to E2, and move the same formula to D2.

Error in Formula

The program will inform us that we have an "incorrect cell reference" error in E2. However, if we had copied the formula instead of moving it, there would have been no errors.

Note. Formulas can be quickly copied by dragging a cell with the mouse while holding the left mouse button after hovering the cursor over the border of the selected cell. If you perform this action with the CTRL key pressed, the formula will be copied.

Quick Formula Copying

download file Download

This lesson may seem complicated to understand, but it is relatively simple in practice. The key is to remember the behavior of formulas when copying them.