Cat On A Spreadsheet

Volatile functions in Excel are powerful—but they can be a major cause of workbook slowdown. If your spreadsheet feels sluggish or recalculates more than necessary, volatile formulas are often to blame. In this post, we’ll walk through common volatile functions, explain why they slow things down, and show you practical, non-volatile alternatives for each.

What Are Volatile Functions?

A volatile function in Excel recalculates every time any change occurs anywhere in the workbook—even if its inputs haven't changed. This triggers dependent formulas and can cause unnecessary recalculation cascades.

Here are the main volatile functions you’ll encounter:

• NOW()

• TODAY()

• RAND() / RANDBETWEEN()

• OFFSET()

• INDIRECT()

• CELL(), INFO(), AREAS() (context-dependent)

Let’s explore how to replace or restructure these functions to eliminate the volatility without losing functionality.

Replacing NOW() and TODAY()

Problem: These update every time the workbook recalculates—even if you're not using the new value.

Solution: Use VBA to insert a static timestamp only when needed.

Instead of NOW(), use this VBA snippet:

Assign this macro to a button or shortcut. Now you control when the timestamp is updated.

Replacing RAND() and RANDBETWEEN()

Problem: These recalculate constantly, even when you don’t want new values.

Solution: Generate static random numbers using VBA.

Instead of Randbetween(), use: 

For static random values, use VBA to insert them directly, or copy-paste values from volatile formulas.

Replacing OFFSET()

Problem: OFFSET() is volatile even when its inputs don’t change.

Solution: Use INDEX() instead.

Instead of:

=SUM(OFFSET(A1, 1, 0, 5, 1))

Use:

=SUM(INDEX(A:A, 2):INDEX(A:A, 6))

INDEX() is non-volatile and provides better performance in all but the most complex scenarios.

Replacing INDIRECT()

Problem: INDIRECT() is volatile and breaks if the referenced cell moves or is renamed.

Solution: Use structured references, dynamic named ranges, or CHOOSE() where possible.

Instead of:

=INDIRECT("Sheet2!A1")

Use:

=Sheet2!A1

Or, if you must reference dynamically:

=CHOOSE(MATCH(A1, {"Jan","Feb","Mar"}, 0), Sheet1!B2, Sheet2!B2, Sheet3!B2)

Or use named ranges combined with INDEX().

Replacing CELL(), INFO(), and Others

These functions are often used to detect file paths, sheet names, or formatting—but they’re volatile and sometimes unnecessary.

Instead of:

=CELL("filename", A1)

Use VBA:

For things like sheet names:

General Strategy: Push Volatility to VBA

When Excel gives you no non-volatile function alternative, move the logic to VBA. You can trigger macros with buttons, worksheet events, or change controls.

Example:

This automatically adds a date in C2 whenever B2 is changed—no volatile =TODAY() formula required.

Bonus: Avoid Volatile UDFs

User Defined Functions (UDFs) can also be volatile if they:

• Call NOW(), TODAY(), or other volatile functions internally

• Use Application.Volatile=True explicitly

• Refer to other volatile cells

Avoid writing UDFs that mimic volatile behavior unless absolutely necessary.

Final Thoughts

Rewriting volatile formulas doesn't mean eliminating all dynamic behavior—it means taking control of when and how your spreadsheet recalculates. By replacing volatile functions with static values, structured formulas, or VBA, you'll make your workbooks:

• Faster

• More stable

• Easier to debug and maintain

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet