Excel move and copy of formulas

You can move and copy formulas in the same way that you move and copy cells, and you don't have to repeat them here. Unlike moving and copying cell data, they affect the results for formulas that have cell addresses, meaning that Microsoft Excel automatically adjusts the references of all moved cells so that they still refer to the same cell in the new location. If we move the cell to a position that was previously referenced by another formula, those formulas produce the error value "#ref!" because the original cell has been replaced by a cell that has been moved over.

Effects of copied cells on relative reference locations

The effect of a replicated cell on a relative address reference and an absolute address reference is different. For relative address references, when we copy a cell, Microsoft Excel automatically adjusts all the relative address references and the associated portions of the mixed references that are in the newly pasted range of cells. For example, the formula "=sum (C1:C2)" is stored in the "C3" cell. We select the "C3" Cell, press the "Copy" button, then point the mouse pointer to cell "D3", and then press the "Paste" button, we will see the cell becomes "=sum (D1:D2)", As shown in Figure 7-8.

Effect of copied cells on absolute, mixed reference locations

If you want the reference position of the copied formula to remain unchanged, you should use an absolute address reference or a mixed address reference. For example, the formula "=-PMT" is stored in the "C5" cell (C$4/12, $B $ $B 5), select the C5 cell, press the Copy button, point to cell E5, and then press the Paste button to see that the cell becomes the =-PMT (e$4/ $B $ $B 5) ", as shown in Figure 7-9.