Pro Tip: use cut-paste to move cells and update any references to them.
Excel offers cut-paste and copy-paste as two methods for moving or duplicating cells within a spreadsheet. The two appraoches generally boil down to: do you want to keep the original or not? In Word, that’s the only consideration; cut will remove the original and copy will keep it. Excel adds one new, critical distinction. In Excel, cut will move the original but will also update any other cells which reference that original. Copy will keep the original but will also update any formulas in the copy based on the new location. This means you can get a completely different spreadsheet depending on which approach you take. It also means that the order in which you do things matters. You could cut-paste some cell and then copy-paste the copy back to where it started. Any cells that referred to the regional will now refer to the copy, but this will not be obvious until you check any formulas.
Understanding how to use cut-paste to your advantage is one of those mastery level techniques. A cut-paste is about the only easy way to update all referencing cells to point to a new location. This also means that if you make a mistake, you really have to search for all of the places that were impacted.
There are a couple of caveats to Excel updating references. The major one is that Excel will only update a reference if you have cut all of it. If a formula refers to column C:C and you cut cell C2 and paste it back donw at D2, the formula will still refer to C:C. In this way, the cut had no effect on referencing cells. You don’t have to cut the exact region, just something that includes referenced part.