I have a Google sheet where I'd like to conditionally convert a formula range into its values (effectively locking them). I've searched for a function I could embed in a IF statement but don't think that's possible. I have reached my limit on what I can do. Anybody have ideas?

Below is a link to a scrubbed demo version. Please let me know any ideas you have.



You can not do what you ask for per se.
Luckily, there is an alternative.

Step 1
Select the range you want to stop changing when the values of the formula change e.g. A8-A14 (Where A8 is where your formula is and A14 is last cell of produced results)

Step 2
Copy the range and paste it on-top of the same range.

Step 3
On the little pop-up (bottom right corner) choose Paste values only.

Done. Your formula is now frozen/locked.
(Actually the formula is deleted and replaced by the results which are now static. If you want to re-use the formula or refer to it in a later time, make sure you have already copied it somewhere).

Google sheets - Paste values only

  • Please remember that as per site guidelines when an answer addresses your question, accept it so others can benefit as well. – marikamitsos Feb 25 '20 at 21:33
  • hi marikamitsos. I knew this one and use it all the time - I have some decent spreadsheet skills. Based on my search prior to coming here I suspected there wasn't a good solution. I was hoping for a hack of some existing function to nest into a IF statement to make it happen. I'm trying to automate this process so someone with slightly lesser skills than myself can handle the basic functions of this job without knowing what I know. I'll keep looking - may try a macro if I can fire it from a specific drop down selection. We'll see. Thanks – MJD Tech Feb 27 '20 at 14:26


A follow to say that I did find a solution - of sorts.

I created a "button" that triggers a macro written to "paste values" in the required ranges. The the button changes color to indicate the period has been "closed".

Worked well and thanks for helping.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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