Calculating Custom Functions in Microsoft Excel

, ,

Microsoft Excel does a great job of calculating only what is needed.  If automatic calculation is turned on (and is by default), it only calculates formulas that have changed since the last calculations.  If automatic calculation is turned off, F9 will accomplish the same thing.

CTRL F9 goes one step further and calculates formulas that have changed, as well as the formulas dependent on them.

CTRL ALT F9 calculates all formulas in the workbook, regardless of whether they changed since the last calculation.

When custom functions are used, Microsoft Excel doesn’t always know the dependencies because the function can reference cells outside those provided in the function arguments.   When this occurs, using CTRL SHIFT ALT F9 is critical to ensure that all cells are calculated correctly.  This rechecks dependent cells and calculates all formulas regardless of whether they have changed since the last calculation.  This is the only way to ensure that all data is calculated.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.