Change in a Cell in excel refreshes whole sheet

Question

Change in a Cell in excel refreshes whole sheet

Kashif Bashir Bhatti 36

Hi,
I am working on an Excel Add-in and have created some custom functions in it. Now my user has created some functions (150+ custom functions) and all of these custom functions have parameters as references to other cells in sheet. When user updates some text cell (whether referred in some function call or not) , whole sheet is refreshed and Excel shows busy in all cells having custom functions it. I understand that if a cell is referred in some custom function and it is updated, that custom function is called to show updated data, but it should not happen when user edits a cell not referred in any of the functions.

Kashif Bashir Bhatti 36 Reputation points

2023-08-16T12:45:52.9233333+00:00

After some investigation and help from other sources, I came to know about Volatile functions which changes its values after calculation is done in workbook. In my case all functions were passed a reference to a cell which has function TODAY() in it. So this TODAY function was being re-calculated after any change in sheet and new value generated was causing change of parameter value for all functions and hence causing whole sheet being recalculated. After removing this TODAY() call, it worked as required.

So conclusion is that excel was working correctly and this was not a problem.

1 answer

Your answer

Kashif Bashir Bhatti 36 Reputation points

2023-08-16T12:45:52.9233333+00:00

After some investigation and help from other sources, I came to know about Volatile functions which changes its values after calculation is done in workbook. In my case all functions were passed a reference to a cell which has function TODAY() in it. So this TODAY function was being re-calculated after any change in sheet and new value generated was causing change of parameter value for all functions and hence causing whole sheet being recalculated. After removing this TODAY() call, it worked as required.

So conclusion is that excel was working correctly and this was not a problem.

Answer 1

lindalu-MSFT 161 Microsoft Employee Moderator

Hi @Kashif Bashir Bhatti Did you try changing the calculation mode from automatic to manual? That should prevent the entire sheet from recalculating when you update a cell.
On the Formulas ribbon, change from Automatic to Manual:

This will only calculate when you explicitly request:
Choose Formulas > Calculate Now (shortcuty key F9) to calculate all open workbooks.
Choose Formulas > Calculate Sheet (shortcut key Shift + F9) to calculate only the active sheet.

You can also set this in File > Options. In the Excel Options, choose Formulas and change the Calculation options.

Linda

Kashif Bashir Bhatti 36 Reputation points

2023-01-02T12:26:03.053+00:00

Yes, I tried it. But when I change calculation mode to automatic, it again calls all functions even only unrelated cells were changed.

Excel should only calculate changed cells or cells depending on them.

Share via

Change in a Cell in excel refreshes whole sheet

1 answer

Your answer