none
How to disable particular formula calculation in automatic mode? RRS feed

  • Question

  • Hi,

    I am using more than 90,000 formulas in worksheet. Now my customer want to disable particular formulas in formula automatic calculation mode because in automatic calculation mode if they make any change in particular cell then entire worksheet formulas are always recalculated.

    Some formulas are having lot of dependencies with other cells so it taking more time to refresh new value after making some changes in particular cell.

    Now my customer want to disable particular section formulas in automatic calculation mode to increase performance remaining section formula speed. They want the disabled formulas need to be executed only in button click.

    For example :

    Formula present in A90000 to Z90000. Now they want to disable formula calculation in B1:D500 in automatic Formula  calculation mode. They want  the formulas present in B1:D500 need to be calculated only in button click.

    Please guide me how to disable particular section formulas execution in automatic calculation mode. If it is not possible please provide me alternative approach to resolve this problem.

    Please help me.



    Bala


    • Edited by Balaramji Saturday, May 21, 2016 3:05 PM
    Saturday, May 21, 2016 3:02 PM

Answers

  • Hi Balaramji,

    you cant disable the formulas which are available in sheet.

    sure you can calculate some specified range.

    For work around you can try to replace "=" sign of Formula with "==".

    so it will not execute and when you want to execute again replace "==" sign with "=".

    you can perform this operation on button's Click.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 2, 2016 7:50 AM
    Moderator

All replies

  • Hi,

    I am using more than 90,000 formulas in worksheet. Now my customer want to disable particular formulas in formula automatic calculation mode because in automatic calculation mode if they make any change in particular cell then entire worksheet formulas are always recalculated.

    Some formulas are having lot of dependencies with other cells so it taking more time to refresh new value after making some changes in particular cell.

    Now my customer want to disable particular section formulas in automatic calculation mode to increase performance remaining section formula speed. They want the disabled formulas need to be executed only in button click.

    For example :

    Formula present in A90000 to Z90000. Now they want to disable formula calculation in B1:D500 in automatic Formula  calculation mode. They want  the formulas present in B1:D500 need to be calculated only in button click.

    Please guide me how to disable particular section formulas execution in automatic calculation mode. If it is not possible please provide me alternative approach to resolve this problem.

    Please help me.


    Saturday, May 21, 2016 2:58 PM
  • Hi Balaramji,

    please visit the link below. it will give you information regarding your issue.

    Optimizing Worksheets for Fastest Calculation

    Calculate only a specified range?

    Manually Recalculating a Single Cell or Range

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 23, 2016 4:16 AM
    Moderator
  • Hi Balaramji,

    you cant disable the formulas which are available in sheet.

    sure you can calculate some specified range.

    For work around you can try to replace "=" sign of Formula with "==".

    so it will not execute and when you want to execute again replace "==" sign with "=".

    you can perform this operation on button's Click.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 2, 2016 7:50 AM
    Moderator