Relative formula not updating when copying
If you want to prevent Excel from changing the cell references when copying cells, you need to use absolute references.To create an absolute reference, insert a dollar sign ($) before both parts of the cell reference in the formula you want to freeze, as illustrated in the following image.Full feature free trial 60-day, no credit card required!Get It Now Is there an easier way to copy formula without changing its cell references this quickly and comfortably?
As you are typing a formula and finish a cell reference, press F4 to toggle between the 4 combinations of reference types.Actually, Kutools for Excel provide such utilities of Convert Formula to Text and Convert Text to Formula. Please copy them and paste into your destination range. And then you can restore the text strings to formula with selecting the text strings and clicking Kutools The formulas changes after copying as a result of relative references.And you can convert formulas to text and copy them to other places, and then restore these text to formula easily. Select the formula cells you will copy, and click Kutools Convert Formula to Text. Therefore, we can apply Kutools for Excel’s Convert Refers utility to change the cell references to absolute to prevent from changing after copying in Excel. Select the formula cells you will copy, and click Kutools Convert Refers. In the opening Convert Formula References dialog box, please check the To absolute option and click the Ok button. Copy the formulas and paste into your destination range.In actual, Kutools for Excel can help you copy formulas without changing its cell references quickly. Select the formula cells you will copy, and click Kutools Exact copy. In the first Exact Formula Copy dialog box, please click OK.
And in the second Exact Formula Copy dialog box, please specify the first cell of destination range, and click the OK button.See screenshot: Kutools for Excel - Includes more than 300 handy tools for Excel.