Volatile Formulas Detected in Excel – Keep Your Distance

Last week, I came across an excel problem in a forum. I immediately sprung into action and created a long formula that started with OFFSET().

Within a few hours, it was shot down by other excel experts as it contained volatile formulas.

I immediately recognized the cardinal sin I had committed.

So with this confession, let me share what I have learned about volatile functions in Excel. In plain simple terms, it is a function that will make your excel spreadsheet slow, as it recalculates the formula again and again. A number of actions can trigger this (described later in this post).

A very simple example of a volatile function is the NOW() function (to get the current date and time in a cell). Whenever you edit any cell in a worksheet, it gets recalculate. This is fine if you have a small data set and less number of formulas, but when you have large spreadsheets, this could significantly slow down the processing.

Here is a list of some common volatile functions, which should be avoided:

Super Volatile Formulas:

Almost Volatile Formulas:

The good news is, my favorite INDEX(), ROWS(), and COLUMNS() don’t exhibit volatility. The bad news is that Conditional Formatting is Volatile

Also, ensure that you do not have these functions inside non-volatile functions, such as IF(), LARGE(), SUMIFS(), and COUNTIFS(), as this would eventually make the entire formula volatile.

For example, suppose you have a formula =If(A1>B1, “Trump Excel”,RAND()). Now, if A1 is greater than B1, it returns Trump Excel, but if it is not, then it returns RAND(), which is a volatile function.

Triggers that recalculate Volatile Formulas
  • Entering new data (if Excel is in Automatic recalculation mode).
  • Explicitly instructing Excel to recalculate all or part of a workbook.
  • Deleting or inserting a row or column.
  • Saving a workbook while the ‘Recalculate before save’ option is set (it’s in File–> Options–> Formula).
  • Performing certain Autofilter actions.
  • Double-clicking a row or column divider (in Automatic calculation mode).
  • Adding, editing, or deleting a defined name.
  • Renaming a worksheet.
  • Changing the position of a worksheet in relation to other worksheets.
  • Hiding or unhiding rows, but not columns.

If you have a lot of formulas in your worksheet that are making it slow, I suggest you switch to Manual Calculation Mode. This stops automatic recalculation and gives you the power to tell excel when to calculate (by clicking ‘Calculate Now’ or pressing F9). This option is available in Formulas–> Calculation Options.

Volatile Formulas - Manual Calculation

Related Tutorials:
  • Dhiraj

    Nice