I am in love with Excel and I’m not ashamed to say it. That may prove that I’m a geek, but I just love collecting and analysing data. So when in a latest project I had to copy many formulas just as they were to a different part of the sheet, I know it would be a problem. Excel tries to be smart and change the reference cells in the formulas you copy, and 99% of the time it is exactly what you need. But not this time.

It is not a new problem. I’ve had to face it many times before, but it was on a smaller scale. This time what I needed spanned formulas over 5 worksheets and about a quarter of them were the same formula used over and over. This meant that about 500 cells needed to be copied from one worksheet to the other 4 and they were not in the same place. This was going to be a nightmare.

Now, Paste special… option is all very nice and good, and I’m using it many times and it helps greately, but it does not have that specific method to allow me to copy the formula just as it is. I’m sure many people would need that method of copy/paste but it’s not in there. Searching for an answer I found a trick that works best. Just turn the formulas into a text format. Then copy over the exact formula text and presto! You are done!

If you are using an Excel version that can select multiple non-adjoining cells, then you can select the original cells and the ones you pasted and use Replace on both to revert them to working formulas again.