Ok, so I know it is dorky of me to post this, but part of the purpose of this blog is to put up content that I find myself looking for frequently and hopefully make it all the more accessible. Here you go internets!
If you are like me and are an Xcel jockey, or at least feel like one, and need to frequently copy large swaths of cells without altering the references; here are the 4 easy steps.
1) highlight the area you want to copy
2) click Edit and select Replace
3) In the “Find What” field enter “=” and in the replace “#” or any another symbol that isnt in any of your formulas. Basically, this will stop the references from being references, i.e. =page2!l3 becomes #page2!L3 and you can move it around without excel automatically changing it.
4) copy and paste the formulas where you want them and then reverse the process – select the area, click Edit and select Replace, but this time enter “#” in the “Find What” and enter “=” in the replace. Voila!
