Copy formulas without the cells updating to their new location
I have had an ongoing problem with a worksheet I use to track data by date. Each month I need to copy a range of cells to start the data comparison for the next month. Problem is that you can’t copy cells in Excel without the cell references updating to their new location. And, guess what? That’s exactly what I need to do – copy cells, paste them into their new location without the formulas changing – at all.
One way you can do this is to copy each formula manually by double clicking the cell, copy the text in the Formula Bar, press Esc and then go to the cell to copy it to and paste it in. It works, but you can only do it one cell at a time. I have ten or more cells so that’s just a plain waste of time.
I could write a macro – and in future, I will – but there is a super neat solution that I am using for now.
First of all, press Control + ` (grave) to show formulas in cells (or choose to Show Formulas from the ribbon).
Next select and copy the cells containing the formulas.
Paste these as text into a document. I use Paste Special > Unformatted Text in Word but you can paste into any text editor.
Return to Excel and hide the formulas using Control + ` again.
Now select the text of the formulas from your document and copy it, then paste it into the appropriate cells in the worksheet. The Data Parse feature in Excel will automatically split the text containing the formulas and paste one formula into each of the cells.
It might sound complex, but you can do this really quickly, and it’s accurate and effective.