Charles Esther
Volatile functions in Microsoft Excel are helpful when you need real-time data.
They recalculate every time the workbook opens or when any change triggers a calculation.
However, you might not always want them to keep updating.
Here’s how to fix their values or temporarily pause recalculations.
What Are Volatile Functions in Excel?
Volatile functions recalculate every time a worksheet updates.
Common examples include NOW() for the current date and time, TODAY() for just the date, RAND() for a random number between 0 and 1, and RANDBETWEEN() for a random number between specified values.
These are great for dynamic tracking, but sometimes you’ll want them to stop updating.
1. Convert Volatile Functions to Static Values While Typing
A simple method to prevent updates is to fix the value when entering the formula.
For instance, typing =NOW() into a cell will return the current time.
But it will update every time the sheet recalculates.
To make it static, press F9 while the formula is still active, then press Enter.
This replaces the function with a fixed timestamp.
Alternatively, you can use keyboard shortcuts:
- Press Ctrl+; to insert the current date.
- Press Ctrl+Shift+; to insert the current time.
- On Mac, use Cmd instead of Ctrl.
You can enter both by pressing them one after the other with a space in between.
You might see a decimal number at first, but that’s Excel’s internal serial format for date and time.
To format it properly, press Ctrl+1, go to Custom in the Format Cells dialog, and define the desired display format.
When done, the cell will display the date and time exactly how you want it—and it will stay that way.
If you already entered a volatile function, reentering the cell and pressing F9 converts it, but note: the function updates one final time before becoming static.
2. Turn Existing Volatile Functions into Static Values
If you’ve already used volatile functions and want to stop them from recalculating, the best option is to copy and paste as values.
Let’s say you use =RANDBETWEEN(50,100) in cells A1 to A10 to generate random numbers.
Each time the sheet updates, those values change.
To lock them in:
- Select the cells.
- Press Ctrl+C.
- Then press Ctrl+Shift+V to paste them as values.
On Mac, use Cmd instead of Ctrl.
This removes the formula and keeps only the current values.
Now they won’t change unless you manually edit them.
3. Stop Excel from Auto-Recalculating Volatile Functions
You can also prevent recalculation by changing Excel’s settings.
Go to the Formulas tab and click Calculation Options, then select Manual.
This stops Excel from recalculating volatile functions automatically.
However, any affected cells will show a strikethrough format to warn you that the data may be outdated.
With manual calculation enabled, Excel only recalculates when you:
- Save the workbook (Ctrl+S),
- Press F9 to recalculate all open workbooks, or
- Press Shift+F9 to calculate only the active sheet.
This is useful for large workbooks where constant recalculation can slow performance.
Final Thoughts
Volatile functions like NOW() and RAND() are powerful tools, but they can affect speed and accuracy if not managed well.
Using the methods above—fixing values at entry, converting after the fact, or switching to manual calculations—helps maintain control over your data.
For better performance, try to minimize volatile functions and keep your formulas clean.
Also, consider reducing image sizes and limiting excessive formatting to keep your workbook running efficiently.
