4

I've found a way to copy formulas only in Google Sheets through Search and Replace Ctrl + H feature workaround as follows:

STEP 1:

Cells with Formulas to be copied Cells with Formulas to be copied

STEP 2:

Press Ctrl + H and Tick Also search within formulas Press Ctrl + H and Tick Also search within formulas

STEP 3:

Copy Formulas (formulas only are copied) Copy Formulas (formulas only are copied)

STEP 4:

Select a destination cell/range and paste the clipboard content Select a destination cell/range and paste the clipboard content

STEP 5:

Result: Formulas only are pasted Result: Formulas only are pasted

Please share any way you know to do it with less steps. Thanks a lot for your help much appreciated.

Lod
  • 657
  • 1
  • 9
  • 30

1 Answers1

1

Honestly, I don't understand your need to use ctrl+h to do this. You could've just used ctrl+c and ctrl+v, since you are copying the cell content, which is the formula. I know it will also copy the cell's formatting, but if it's a previously blank cell, there should be no problem.

A fast way to do it is copying the range of cells you want to copy the formulas from, and the right click - special paste - past only the formula.

Special Paste

~Sorry for the portuguese menu. The option should be in the same spot, though.~

The only workaround you could need is copying the actual values given by the formula on these cells, and for that Sheets also has a keyboard shortcut: ctrl+shift+v, which is a special paste case, which pastes only the results, not the formula.

So unless you're lacking one or more of those keys, you should be fine. If you do, a mere select range - right click - copy - paste would do the trick. No need for extra long steps.

Now, if what you're trying to accomplish is copying the formula and paste it as a string, you can use the =FORMULATEXT([CELL]).

FormulaText()

Rodrigo Biffi
  • 388
  • 4
  • 12
  • 3
    Thanks a lot @Rodrigo Biffi. You rock! The `=FORMULATEXT([CELL])` did it. I needed the formula as `input` to copy and paste in another text editor (like sublime text) to quickly edit many formulas at once and then paste them back (edited) into google sheet. The issue with the `right click > paste special > paste formula only` method is that it returns the formula `output` when pasting directly into google sheet or an external editor — so your `=FORMULATEXT([CELL])` method is the quicker way to go. Thanks again. Be well! – Lod Jun 04 '21 at 20:34
  • 1
    `Paste Special` → `Formulas only` will still not 1 to 1 copy the formula cell references. It updates to new cells. – Avatar Nov 10 '22 at 07:22