Would you like to copy your Excel formulas from one place to another without the cell references changing?
Excel default behavior is to adjust the cell references when you copy a cell and paste to another one. You can choose to fix the cell reference by using the dollar sign ($). A $ for the row number, fixes the row and a $ on the column number fixes the column.
You might however need to copy a set of formulas that have relative references from one location to another or from one file to another and keep the existing cell references.
It’s quite simple actually!
Follow these steps:
- Highlight the are you’d like to copy
- Go to Home / Find & Select / Replace (or press Ctrl + H)
- Search for = and replace with a text that’s not in your file – in this example I chose “notinfile” (note as mentioned in the comments in YouTube, you can also replace with ” =”, i.e. a space before the equal sign)
- Go back to Home / Find & Select / Replace (or press Ctrl + H) – search for your text – in my example “notinfile” and replace with =.
- That’s it!
Watch the steps in this video:
Feel free to Download the Workbook HERE.
Excel Dashboards that Inform & Impress