Relative formula not updating when copying
If you have formulas in other cells that were referencing back to the original location of the cells you moved, the reference formulas will automatically update to the new location of the cells you moved. (Assuming this works in Excel, but I've only done it in Google Sheets).In this lesson we discuss cell references, how to copy or move a formula, and format cells.
Remember from Lesson 2 where we had to split a full name into first and last name? Write the formula =RIGHT(A3, LEN(A3) – FIND(“,”, A3) – 1) or copy the text to cell C3.
For example, you could anchor the row reference then move a cell down two rows and across four columns and the row reference stays the same. Let’s refer to that earlier example – suppose in cell A1 we have a formula that simply says =A2.
That means Excel output in cell A1 whatever is inputted into cell A2.
Now drag (hover over the edge of the selection until the mouse cursor changes into a hand; now you can drag) the selected cells to the new position.
Go back to the original top-left position of the copied cells and use Ctrl V (paste).