Volatile functions in Microsoft Excel are useful when you want to display real-time data, as they recalculate whenever a workbook is reopened, or when any other calculations are performed. However, you can fix the results they return, or temporarily prevent them from recalculating.
Examples of volatile functions in Excel include NOW (which returns the current date and time), TODAY (which returns the current date), RAND (which returns a random number between 0 and 1), and RANDBETWEEN (which returns a random number between two values you specify).
Related
Everything You Need to Know About Volatile Functions in Excel
They’ve got nothing to do with chemistry or hostility!
1
Convert a Volatile Function to a Static Value as You Type
One way to stop a volatile function recalculating is to convert it to a fixed value at the point of entry.
In this example, the NOW function is used to return the current date and time:
=NOW()
Related
13 Microsoft Excel Date and Time Functions You Should Know
Why use a calendar or a clock when Excel can track it all for you?
However, two minutes later, when I edited another cell in the worksheet, the time updated.
To make the date and time static from the outset, type the formula, and with Enter mode still activated, press F9 > Enter.
To enter the current date using a keyboard shortcut, press Ctrl+; (semicolon), then Enter. For the current time, press Ctrl+Shift+;. If you’re using a Mac, use Cmd in place of Ctrl. You can enter both the date and time into the same cell by using both keyboard shortcuts, one after the other, separated by a space.
At first, you may see what appears to be a random decimalized number, but in fact, this is a serial number representing the date and time as they were when you pressed Enter.
Related
What Are Date and Time Serial Numbers in Microsoft Excel, and Why Do They Exist?
Date and time calculations would be impossible without them.
Finally, press Ctrl+1 (or right-click the cell and click “Format Cells”) to launch the Format Cells dialog box, click “Custom,” and in the Type field, state how you want the date and time to be displayed.
When you click “OK,” the date and time will appear in the active cell, and they will remain fixed until you change them manually.
If you reenter a cell containing a volatile function and press F9 to make it static, the cell will update one more time before turning into a fixed value. If you want to retrospectively fix a cell containing a volatile function without affecting the value, see method 2 below.
In a second example, let’s say you want to generate a random number between 0 and 1. To do this, you type:
=RAND()
into cell A1, and press Enter.
However, because RAND is a volatile function, a new random number is generated each time a calculation is performed elsewhere in the workbook.
Instead, before you press Enter, press F9, and the RAND function will be replaced by a fixed value in both the cell and the formula bar.
Take care when using the F9 shortcut key in Microsoft Excel. If you’re not inputting data into or editing a cell (in other words, if Excel is in Ready mode), pressing F9 forces all volatile functions to recalculate—exactly the opposite of what you’re trying to achieve!
2
Retrospectively Convert Cells With Volatile Functions to Static Values
Another way to fix values produced by volatile functions—and probably the method I use the most—is to copy the result and paste it as a value. This is particularly handy if you want to perform this action on cells already containing volatile functions without changing their values, or if you need to fix many cells at the same time.
Imagine you’ve produced a set of random numbers between 50 and 100 using the RANDBETWEEN function:
=randbetween(50,100)
Related
How to Generate Random Numbers in Microsoft Excel
Use a simple function, or get more advanced control with a generator tool.
However, left as they are, these random numbers will continue to regenerate each time a calculation is performed elsewhere in the workbook, or if you close and reopen the file.
So, to fix these values, first select the cells, and press Ctrl+C (or Cmd+C on a Mac).
Next, with those cells still selected, press Ctrl+Shift+V (or Ctrl+Cmd+V on a Mac) to paste the cell values while forcing Excel to ignore the formula used to generate them.
Now, the previously unfixed values have been converted to static numbers.
3
Turn Off Automatic Calculations
Another way to prevent volatile functions from recalculating automatically in Microsoft Excel is to change the calculation settings. Making this alteration won’t change how the functions work, but it will affect how they are calculated and how their result is displayed on your screen.
To do this, click the “Calculation Options” button in the Formulas tab, and click “Manual.”
Changes to the calculation options in one workbook will affect all other workbooks that are already open and any that you open subsequently.
Now, when a calculation is performed in the workbook, any cells containing volatile functions that have not updated automatically due to this change (also known as a stale value) will be reformatted with strikethrough. This is to remind you that the information in those cells is not up to date.
Now, any volatile functions will only be calculated when:
- You save the workbook manually or press Ctrl+S,
- You click “Calculate Now” or press F9 in Ready mode (this affects all calculations in the entire workbook and all open workbooks), or
- You click “Calculate Sheet” or press Shift+F9 in Ready mode (this affects all calculations in the active sheet only).
Related
How to Manually Calculate Only the Active Worksheet in Excel
If you have large workbooks with a lot of formulas on the worksheets, recalculating the workbooks can take a long time.
Since volatile functions in Excel continually recalculate, using many in one workbook can significantly affect your spreadsheet’s performance. So, using the methods in this guide can help speed up your Excel file. Simplifying your formulas, compressing images, and avoiding too much formatting are other ways to ensure your workbooks run smoothly.