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!

  • Have you tried right click > Paste special > Paste formula only
    – pjmg
    Mar 18, 2020 at 16:25
  • 1
    Yes, I have tried. This feature only works on the same page, if I open any other spreadsheet it doesn't. Mar 18, 2020 at 16:35
  • Found a way to make it works.
    – pjmg
    Mar 18, 2020 at 16:42

2 Answers 2


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.

First you need to show formulas :

enter image description here

Then copy and paste values only.

  • Wow! That's awesome, it really works! Thank you, you are a lifesaver! I have no idea how you managed to come up with this idea. Mar 18, 2020 at 19:36
  • There's only one problem. Let's say one of the cells in a row has a formula =sum(B66,B64). Normally, if I copy and paste a row that contains such a formula, it automatically takes the coordinates of a new row and B66 and B64 become B1 and B2 or whatever. If I do the way you suggested, it doesn't. Mar 18, 2020 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.