How to copy formulas from one spreadsheet to another?

Question

If I select say 10 rows in Google Spreadsheets and use ctrl+c and then create a new spreadsheet and try to paste all the data it only moves over the values but not the formulas. Is there any way to paste all the data including the formulas? This feature works only if I paste it in the same spreadsheet. Thanks!

Answer 1

I know this is a few years after the initial question, but I found a quick(ish) workaround that requires a couple steps outside of google sheets..

I tried the 'Show formulae' method as this was a good way to copy formulae in bulk, but also ran in the same issue as above. Here's what ended up working best for me:

• I copy the formulae (I do this in one column batches) to a word processor.
• Then I use a little strategic find and replace, in my case, replacing the " ( " symbol in this chunk of formulae with " ('[sheet name here]'! " so that all the formulae in the text I copied is now amended with the proper sheet referencing. Depending on your formulae, this step could change for you, but chances are there should be some similarities here between cells to take advantage of with F&R instead of doing it manually one-by-one.
• Once I have my block of proper formulae, I copy the whole thing, select the group of cells in the column I originally copied the formulae from and paste the whole block of text from the word processor. This pastes each formula in its own cell since they're separated by line breaks, all correctly attributed!
• One thing to note is that if the cells you're pasting into are merged, this will undo that merge since google sheets generates new cells with the text data rather than pasting values into the existing cells. The formatting should remain though.

Answer 2

First you need to show formulas :

Then copy and paste values only.