How Copy formula row in Excel with absolute references

When copying relative formulas (those without a "$" sign), Excel often substitutes different values. But what if you want to maintain the original formula values? You can turn all formula references into absolute ones by adding "$" signs.



How to Copy Formulas Without Altering Them in Excel?

Simply place a dollar sign before the letters and numbers in your formulas to make them absolute references that won't change when copied.

For instance, in a table like this, a reference to markup ($B$1) won't change when copied to new rows because it's an absolute reference, not a relative one:

Absolute reference.

However, in large and complex tables with numerous formulas to copy and move, manually adding "$" signs to all references can be time-consuming. To work around this, you can use a clever method by temporarily deactivating your formulas.

  1. Press Ctrl+H (bringing up "Find & Replace").
    2. Replace.
  2. In the "Find" field, enter "=", and in the "Replace with" field, enter "#" Then click "Replace All."
    3. Example1.
  3. Now you can copy the necessary row without changing the reference addresses in the cell, as they have been temporarily deactivated.
    4. Example2.
  4. Press Ctrl+H again, but this time specify the reverse values: Find "#" and replace it with "=". The system will restore everything to its original state.
    5. Replace All.
Example Excel Formula.

download file Download

As a result, when copied to new rows, all formula references remain unchanged.